SQL to generate Asset Information – Configuration Manager SCCM 2012

SELECT   DISTINCT  s.Netbios_Name0 AS ComputerName,  
            s.Operating_System_Name_and0 AS OSName,  
            pr.Name0 AS ProcessorTypeSpeed,  
            pr.Manufacturer0 Manufacturer, 
            pr.NumberOfCores0 Cores, 
            pr.NumberOfLogicalProcessors0 LgicalProcessorCount, 
            case when pr.DataWidth0=64 then '64 bit' else '32 bit' end DataWidth, 
            m.TotalPhysicalMemory0/1024.00 AS MemoryMB,  
            GS1.TotalVirtualMemorySize0 VirtualMemory, 
            GS1.TotalVisibleMemorySize0 VisibleMemory, 
            ip.IPAddress0,  
            T1.COL AS TotalDriveSize, 
            LastBootUpTime0, 
            DATEDIFF(Day,GS1.LastBootUpTime0, GETDATE()) AS [Days since last boot]           
FROM v_R_System_Valid s  
       INNER JOIN v_GS_PROCESSOR pr ON s.ResourceID = pr.ResourceID 
       INNER JOIN v_GS_COMPUTER_SYSTEM gs ON s.ResourceID = gs.ResourceID  
       INNER JOIN v_GS_NETWORK_ADAPTER ON s.ResourceID = v_GS_NETWORK_ADAPTER.ResourceID  
       INNER JOIN v_GS_X86_PC_MEMORY m ON s.ResourceID = m.ResourceID 
       INNER JOIN v_GS_NETWORK_ADAPTER_CONFIGURATION ip ON s.ResourceID = ip.ResourceID 
      -- INNER JOIN v_GS_LOGICAL_DISK AS ld ON s.ResourceID = ld.ResourceID  
       INNER JOIN  
       ( SELECT RESOURCENAME,  
       col 
FROM  
(  
        SELECT DISTINCT TAB.Netbios_Name0 RESOURCENAME,  
            (  
            SELECT COL.deviceid0 +' '+ cast(COL.Size0/1024.00 AS varchar(20))+' ' 
            FROM v_GS_LOGICAL_DISK COL   
            WHERE   
                COL.ResourceID = TAB.ResourceID AND COL.DriveType0=3 
             FOR XML PATH ('')  
            ) COL  
FROM v_R_System_Valid TAB  
 )T  
 where T.COL is NOT NULL  
 ) T1 on T1.RESOURCENAME=s.Netbios_Name0 
       INNER JOIN V_GS_OPERATING_SYSTEM GS1 on GS1.ResourceID=s.ResourceID 
WHERE  
            s.Operating_System_Name_and0 LIKE '%Windows NT Server%' 
     AND  
       ip.IPAddress0 IS NOT NULL AND ip.DefaultIPGateway0 IS NOT NULL        
Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SCCM, SQL, T-SQL and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s