Blog Post

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        

2015-12-11

658 reads

Blogs

Flyway Tips: More Object History

By

I wrote about Flyway Desktop Object History in a previous tip. This feature has...

Why Ai Agents need an eval framework

By

AgentDBA (the database agent I have been building) has more than 1000 automated tests,...

A New Word: Semaphorism

By

semaphorism – n. a conversational hint that you have something personal to say on...

Read the latest Blogs

Forums

Kantor Resmi BCA KCU GALAXY Telp:0821°7555°651

By layanan_Bca88

Telp/WA 628217555651 Jl. Dr. Ir. H. Soekarno No.37-39, Klampis Ngasem, Kec. Sukolilo, Surabaya, Jawa...

Kantor Resmi BCA KCU KEDIRI Telp:0817°82°5533

By m4rt1n4

Telp/WA 62817825533 Jl. Brawijaya No.8, Pocanan, Kec. Kota, Kota Kediri, Jawa Timur 64123

Server-level Roles

By Steve Jones - SSC Editor

Comments posted to this topic are about the item Server-level Roles

Visit the forum

Question of the Day

Server-level Roles

In SQL Server 2025, can I alter a fixed server role?

See possible answers