Monthly Archives: December 2015

Learn How to Insert Data From Stored Procedure Into Table?

Stored Procedures are considered most efficient way for calling and retrieving data and hence users always look forward to encapsulate logic in it so that it can be called when needed. While encapsulating data in Stored Procedure, it is also … Continue reading

Posted in SQL | Tagged , , , , , | Leave a comment

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   … Continue reading

Posted in SCCM, SQL, T-SQL | Tagged , | Leave a comment