Month – July 2012

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