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 http://www.windows-noob.com/forums/index.php?/topic/7308-collection-membership/#entry27677 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 http://www.myitforum.com/forums/Active-Directory-Discovery-additional-Attributes-and-broken-reports-and-queries-m238462.aspx 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: 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’

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