Below are the basic log files (more understandable way) that are helpful in troubleshooting the client issues if there are any application instllation failure/inventory and other issues.This information has been compiled from Microsoft Knowledge Base Article 867490 A list of windows update error codes can be found here. CAS.log ."CAS" means "Content Access Service". In this Log you will find the local Package Cache status. useful in Downloaded package issues and "cache out of space" errors. Make sure package content access is completed. ccmexec.log ."CCMEXEC" means "Client Configuration Manager Executive" and it is nothing else that the SMS Service itself (ccmxec.exe) Log.…
Author: Eswar Koneti
This gives list of machines where the group policy database file not updated .Before creating the SCCM web report,software inventory has to be enabled for GPO file secedit.sdb file which will is available in %windir%\security\database. collection(WQL): select SMS_R_SYSTEM.ResourceID ,SMS_R_SYSTEM.ResourceType ,SMS_R_SYSTEM.Name ,SMS_R_SYSTEM.SMSUniqueIdentifier ,SMS_R_SYSTEM.ResourceDomainORWorkgroup ,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = “secedit.sdb” and DATEDIFF(dd,SMS_G_System_SoftwareFile.ModifiedDate,GetDate()) > 15 Now you have to figure out why it is not updating SCCM Report(SQL): select a.Name0 ,cs.UserName0 [Last loggedin],os.Caption0 [OS] , CONVERT(VARCHAR(12),b.ModifiedDate,107)As "GPO Date Last Applied" from v_R_System a join v_GS_SoftwareFile b on b.ResourceID=a.ResourceID join v_GS_OPERATING_SYSTEM OS on Os.ResourceID=a.ResourceID join v_GS_COMPUTER_SYSTEM…
This Report will help you in identifying the collection that are member of any other parent collection. SELECT COL.CollectionID, COL.Name, COL.Comment, CTSC.parentCollectionID FROM dbo.v_Collection COL, dbo.v_CollectToSubCollect CTSC WHERE CTSC.subCollectionID = COL.CollectionID The above report is to get collection with its parent collection but if you want to know the root map of particular, go with this post http://blogs.catapultsystems.com/mdowst/archive/2012/02/23/finding-sccm-sub-collections.aspx
Gives list of computers where Programs installed recently. SELECT CS.Name0, CS.UserName0, ISW.ProductName0, ISW.VersionMajor0, ISW.VersionMinor0, ISW.Publisher0, ISW.RegisteredUser0, ISW.InstallDate0, ISW.InstallSource0 FROM dbo.v_GS_COMPUTER_SYSTEM CS, dbo.v_GS_INSTALLED_SOFTWARE ISW WHERE ISW.ResourceID = CS.ResourceID ORDER BY ISW.InstallDate0 DESC, CS.Name0, CS.UserName0, ISW.ProductName0
Select Sys.Name0, Sys.SMSID0, Sys.Domain0, Sys.SystemRole0 from dbo.v_GS_SYSTEM Sys Where Sys.SMSID0 = @GUID Prompt for GUID: select SMS_Unique_Identifier0 from v_R_System
SELECT ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName, count(*) as ‘count’ FROM dbo.v_GS_Installed_Software_Categorized ISC WHERE ISC.FamilyName In (‘Unidentified’,'Uncategorized’) group by ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName order by ISC.NormalizedPublisher, ISC.NormalizedName, ISC.NormalizedVersion, ISC.CategoryName
Query based versus Direct membership: Direct membership rules should be created when you want to target a very specific user or SMS client. Direct membership rules are not automatically updatable; therefore, any change of targeted resources is a manual process. If a targeted resource(workstation) is removed from the site database, the associated direct membership rules will be deleted from all collections. For example, if the workstation ESKON00 is a resource, and ESKON00 ages out of the site database(removed) for any reason, all direct membership rules referring to ESKON00 will also be Deleted. If ESKON00 is later rediscovered or inventoried, the…
There are cases,where a computer might be added to different collection and it would be difficult to find out the computer is member of which collections.Try this report to list all the collection the computer member of. select a.CollectionId, b.Name from dbo.v_R_System r join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID join dbo.v_Collection b on b.CollectionID = a.CollectionID Where R.Name0 =@machine Prompt for machine: select Name0 from v_R_System If you want to try for user member of What collection ? below is the SQL report. select a.CollectionId, b.Name from dbo.v_R_User R join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID join dbo.v_Collection…