Category: Uncategorized

CM12 Collection prompt example

For full details see: SELECT distinct sys1.Name0, csys.Model0, csys.Manufacturer0, os.Caption0, os.CSDVersion0, BIOS.SerialNumber0, sys1.description0, REPLACE((SELECT IPAddress0 + ‘, ‘ FROM v_GS_NETWORK_ADAPTER_CONFIGUR WHERE resourceID = sys1.resourceID AND ipenabled0 = 1 FOR XML PATH(”) )+’..’,’, ..’,”) AS [IP Addresses] FROM v_r_SYSTEM sys1 join v_GS_COMPUTER_SYSTEM csys on csys.resourceid=sys1.resourceid join v_GS_OPERATING_SYSTEM os on os.resourceid=sys1.resourceid join v_GS_PC_BIOS bios on bios.resourceid=sys1.resourceid […]

Adding Ram and HD

  SELECT DISTINCT CS.Name0, CASE WHEN ES.ChassisTypes0 in (‘3′,’4′,’6′,’7′,’15’) THEN ‘Desktop’ WHEN ES.ChassisTypes0 in (‘8′,’9′,’10’,’21’) THEN ‘Laptop’ Else ‘Unknown’ END as ‘Chassis’, BIOS.SerialNumber0, CS.Manufacturer0, CS.Model0, OS.Caption0, OS.CSDVersion0, SCUM.TopConsoleUser0, R.User_Name0 AS ‘Last Logged’, BIOS.ReleaseDate0, NAC.IPAddress0, NAC.DefaultIPGateway0, RAM.TotalPhysicalMemory0, LD.DeviceID0, LD.Size0 FROM dbo.v_R_System R INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID […]

List Collection Membership

For full details, please see select C.Name from dbo.v_FullCollectionMembership FCM join dbo.v_Collection C on C.CollectionID = FCM.CollectionID Where FCM.Name = ‘Gartek-DC’

PC Serial Number by OU.

For full details, please see select distinct R.ResourceID, R.Name0, R.Client0, BIOS.SerialNumber0 from dbo.v_R_System AS R LEFT OUTER JOIN dbo.v_RA_System_SystemOUName AS OU ON R.ResourceID = OU.ResourceID INNER JOIN dbo.v_GS_PC_BIOS AS BIOS ON R.ResourceID = Bios.ResourceID where OU.System_OU_Name0 = N’GARTEK.TST/DOMAIN CONTROLLERS’

How to Add Blank Columns to a report.

For full details please see: SELECT DISTINCT CS.Name0 as ‘PC’, ” as ‘Blank column1’, BIOS.SerialNumber0 as ‘SN #’, CS.Manufacturer0 as ‘Manufacturer’, CS.Model0 as ‘Model’, ” as ‘Blank column2’, OS.Caption0 as ‘OS’, OS.CSDVersion0 as ‘Service Pack’, SCUM.TopConsoleUser0 as ‘Top User’, R.User_Name0 AS ‘Last Logged (Heartbeat)’, CS.UserName0 AS ‘Last Logged (HW Inv.)’, BIOS.ReleaseDate0 as ‘Bios Date’, […]

Last Logon User

For full details see:   Select R.Netbios_Name0, R.User_Domain0 + ‘\’ + R.User_Name0, CS.UserName0 from dbo.v_R_System R join dbo.v_GS_COMPUTER_SYSTEM CS on R.ResourceID = CS.ResourceID join dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID Where FCM.CollectionID = ‘SMS00001’

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 = ‘’