Month – October 2012

SCCM 2012 Table has multiple records for Server IP

For full details see http://social.technet.microsoft.com/Forums/en-US/configmanagergeneral/thread/43686ca9-3064-4533-8f94-f28c889b4511/   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 = ‘192.168.1.1’ or NAC.DefaultIPGateway0 = ‘192.168.5.1’

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. 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”