For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=238953&mpage=1#239186 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 […]
Category: Uncategorized
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 […]
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’
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’
If you are trying to install CM12 console on a workstation, you will receive the following error: Input string was not in a correct format. The solution is to browse the DVD and smssetup\bin\i386 and execute the AdminConsole.msi.
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’, […]
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’
select CS.Name0, NAC.ServiceName0, NAC.MACAddress0 from dbo.v_GS_COMPUTER_SYSTEM CS Join dbo.v_GS_NETWORK_ADAPTER_CONFIGURATION NAC on CS.ResourceID = NAC.ResourceID
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’, […]
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’