Month: October 2012

SCCM 2012 Table has multiple records for Server IP

For full details see   HS view are for History data, not current data. RA views are discovery data views. vSMS are “extra” views.. I don’t generally use them.   It is generally better to limit the use of NOT LIKE statements Here is the clean up SELECT R.ResourceID AS ‘ResourceID’, R.Netbios_Name0 AS ‘Name’, […]

PC by Gateway

  select CS.Name0, CS.UserName0, NA.Description0, NAC.IPAddress0, NAC.MACAddress0, NAC.DefaultIPGateway0 From dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on NAC.ResourceID = CS.ResourceID join dbo.v_GS_NETWORK_ADAPTER NA on NA.ResourceID = CS.ResourceID and NAC.ServiceName0 = NA.ServiceName0 Where NAC.DefaultIPGateway0 = ‘’ or NAC.DefaultIPGateway0 = ‘’

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”