Month – December 2012

How to find all workstations with SNMP.

For full detail please see, Select CS.Name0 as ‘PC Name’, OS.Caption0 as ‘OS’, OS.CSDVersion0 as ‘SP’ From dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_GS_SERVICE S on CS.ResourceID = S.ResourceID join dbo.v_GS_SYSTEM SYS on CS.ResourceID = SYS.ResourceID join dbo.v_GS_OPERATING_SYSTEM OS on CS.ResourceID = OS.ResourceID Where S.DisplayName0 = ‘SNMP Service’ and SYS.SystemRole0 = ‘Workstation’

File not in a list

For full details please see:     select distinct R.ResourceID, R.User_Name0, R.Netbios_Name0 AS ‘Computer_Name’, R.Resource_Domain_OR_Workgr0 AS ‘Domain_workgroup’, S.SiteName as ‘SMSSiteName’, OS.Caption0 AS ‘OperatingSystem’, OS.CSDVersion0 AS ‘ServicePackLevel’, SEQ.SerialNumber0 AS ‘SerialNumber’, SEQ.SMBIOSAssetTag0 AS ‘AssetTag’, CS.Manufacturer0 AS ‘Manufacturer’, CS.Model0 AS ‘Model’, WS.LastHWScan AS ‘Last_Scan_Date’, RAM.TotalPhysicalMemory0 AS ‘Memory_KBytes’, Pro.NormSpeed0 AS ‘Processor_GHz’, (Select sum(LD.Size0) from v_GS_LOGICAL_DISK LD where LD.ResourceID […]

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’

Count of how often clients report HW inventory

For full detail see.     I liked this request, I might ever try to make an NICE SSRS report out of this one. But not for a few weeks, so here you go.   select CS.Name0 as ‘PC’, Count(HLD.ResourceID)+1 as ‘HW inv’ from dbo.v_GS_COMPUTER_SYSTEM CS join dbo.v_HS_LOGICAL_DISK HLD on CS.ResourceID = HLD.ResourceID Where […]