Category: Uncategorized

List of PC by Excel versions

For full detail see forum post. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/66b71887-acc7-40ac-8634-536784fa5adf SELECT DISTINCT R.Name0 AS ‘Machine Name’, left(SF.FileVersion,2) FROM dbo.v_R_System R INNER JOIN dbo.v_GS_SoftwareFile SF ON R.ResourceID = SF.ResourceID Join dbo.v_GS_SYSTEM S ON R.ResourceID = S.ResourceID WHERE S.SystemRole0 = ‘Workstation’ AND SF.FileName = ‘excel.exe’ AND SF.FilePath LIKE ‘C:\Program Files%’ ORDER BY R.Name0 .csharpcode, .csharpcode pre { font-size: small; color: […]

IE7 Collection

For full detail please see forum post   select * from SMS_R_System left join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId left join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = “ie7” or SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = “ie7”

Four reports to answer questions within the reporting forums

So I have been running with my head cut of these days but I always like to make sure that the Reporting forum questions are answer… http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/threads   For more detail about each of the post see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/377b785d-254b-48b9-adfa-b93869a6b599 http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/2dc7d374-fb7b-461d-b123-8b59d68667dc http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/f1b05190-d69b-4f2f-a7c9-166b966892ba http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/d2d4fe26-2dad-4ee6-8fb2-ccb7cb6acc23     Anyways, since I have been behind on things, I thought that I […]

Count of Manufacturers

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/4e4019b0-a28e-4637-ae8c-4ff523d11c1a select top 3 Manufacturer0, Count(*) from dbo.v_GS_COMPUTER_SYSTEM GROUP BY Manufacturer0 Order by 2 desc

How to find an application installed in the last 7 days

For full details see http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/38f96191-6e3e-4150-8f3a-18b1c4db8d7d SELECT R.Netbios_name0, R.AD_Site_Name0, ARP.DisplayName0, ARP.InstallDate0, DATEDIFF(Day, CONVERT(Datetime,ARP.InstallDate0), GETDATE()) AS [days since installed] FROM dbo.v_R_System R join dbo.v_FullCollectionMembership FCM on FCM.ResourceID = R.ResourceID join dbo.v_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceID = R.ResourceID WHERE (ISDATE(ARP.InstallDate0) =1) AND ARP.DisplayName0 = ‘Enhanced Web Reporting’ AND FCM.CollectionID = ‘SMS00001’ and Datediff(dd,ARP.InstallDate0,getdate()) < 7

Finding Adobe X for both x68 and x64

For full details see http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/66c80e66-9760-49b3-b429-08bf2ca8f572 http://social.technet.microsoft.com/Forums/en-US/configmanagerapps/thread/b68ce42c-98f5-4500-bced-0e8e901cb211/ select distinct 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 where SMS_R_SYSTEM.ResourceID not in (select SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “Adobe Reader X%” ) and SMS_R_SYSTEM.ResourceID not in (select SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID from SMS_G_System_ADD_REMOVE_PROGRAMS_64 where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like “Adobe Reader X%”)

Query on user history

For full details please see http://www.myitforum.com/Forums/tm.aspx?m=230539&high= SELECT DISTINCT R.Netbios_Name0, tu.SystemConsoleUser0, tu.logontime FROM dbo.v_R_System R join ( SELECT husr.ResourceID, Husr.SystemConsoleUser0, max(Husr.LastConsoleUse0) as logontime FROM dbo.v_HS_SYSTEM_CONSOLE_USER Husr WHERE Husr.SystemConsoleUser0 LIKE ‘Gartek\’ + @variable Group by husr.ResourceID, Husr.SystemConsoleUser0 ) tu on tu.resourceID = R.resourceID WHERE tu.SystemConsoleUser0 LIKE ‘Gartek\’ + @variable ORDER BY R.Netbios_Name0

Query for computers with single partition

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/4d84e5f9-fd48-4fcb-b490-d0a64052aa08 select SMS_R_System.Name from SMS_R_System where SMS_R_System.ResourceId not in ( select SMS_G_System_PARTITION.ResourceId from SMS_G_System_PARTITION where SMS_G_System_PARTITION.DeviceID = “Disk #0, Partition #1” )

List of PCs with Project, Visio or Visual

For full details, please see this forum post. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/50b2b524-3c11-41c5-a2d5-b8ae979ee3c2/ Select sys.Netbios_Name0, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0, ARP.Version0 FROM v_R_System sys JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID WHERE Displayname0 like ‘%visio%’ or displayname0 like ‘%project%’ or displayname0 like ‘%visual%’

Find Programs Removed from a PC (updated)

For full details see: http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/db96da44-a9e0-4996-bca2-286b6e2c473a SELECT DISTINCT CS.Name0, HARP.Publisher0 AS ‘Publisher’, HARP.ProdID0 AS ‘Product’, HARP.DisplayName0 AS ‘Name’ FROM dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceID = CS.ResourceID join dbo.v_HS_ADD_REMOVE_PROGRAMS HARP on HARP.ResourceID = ARP.ResourceID WHERE ARP.DisplayName0<>HARP.DisplayName0 AND HARP.Publisher0 Is Not Null union SELECT DISTINCT CS.Name0, HARP.Publisher0 AS ‘Publisher’, HARP.ProdID0 AS ‘Product’, HARP.DisplayName0 AS ‘Name’ FROM […]