Software by OU

SELECT ARP.DisplayName0, Count(*) AS ‘Count’, ARP.Publisher0, ARP.Version0 FROM dbo.v_Add_Remove_Programs ARP join dbo.v_RA_System_SystemOUName OU on OU.ResourceID = ARP.ResourceID WHERE OU.System_OU_Name0 = @OU GROUP BY ARP.DisplayName0, ARP.Publisher0, ARP.Version0 ORDER BY ARP.Publisher0, ARP.Version0

List of PC by Excel versions

For full detail see forum post. 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…   For more detail about each of the post see:     Anyways, since I have been behind on things, I thought that I […]

Count of Manufacturers

For full details see: 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 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 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 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: 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. 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%’