Month – December 2012

How to find all workstations with SNMP.

For full detail please see, http://social.technet.microsoft.com/Forums/en-US/configmgrinventory/thread/2eb10f29-0927-4b43-a4a4-0c712793673f 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: http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/107d690a-4bbd-49c4-87bf-389278525b63     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: http://www.myitforum.com/forums/SCCM-Report-question-m238232.aspx 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: http://www.windows-noob.com/forums/index.php?/topic/7022-last-logged-user/   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. http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/32c4db2f-ae84-4fed-93c1-c3f35f8a6fff     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 […]