SCCM systems summarized query data

USE --Put your SCCM instance name here

SELECT distinct
CS.Name0,
case when (vcd.CNLastOnlineTime > getdate()-14 OR vcd.CNLastOfflineTime > getdate()-14) then 1 else 0 end as [active],
OS.caption0,
BIOS.SerialNumber0,
case when IPAddress0 like '%:%' then substring(IPAddress0,-6,charindex(':',IPAddress0)) else IPAddress0 end as [IPAddress0] --IP
,replace(MACAddress0, ':','') [MACAddress0]
,CAST(CS.TotalPhysicalMemory0/1024/1024.0 AS DECIMAL(15,2)) AS [Total Memory]
,cpu.*
,DSK.Total_GB
FROM [dbo].v_GS_NETWORK_ADAPTER_CONFIGUR NAC --network settings
left join [dbo].v_GS_PC_BIOS BIOS ON NAC.ResourceID = BIOS.ResourceID --BIOS settings for getting serial number
left JOIN [dbo].v_CombinedDeviceResources vcd ON NAC.ResourceID=vcd.MachineID --client activity settings
left join [dbo].v_GS_OPERATING_SYSTEM OS ON NAC.ResourceID = OS.ResourceID --OS readable=Caption
left join [dbo].v_GS_COMPUTER_SYSTEM CS ON NAC.ResourceID = CS.ResourceID --name, memory , etc
left join (SELECT DISTINCT resourceID,
           COUNT(ResourceID)  AS [Number of CPUs],
           NumberOfCores0,
           NumberOfLogicalProcessors0
        FROM [dbo].[v_GS_PROCESSOR]
        GROUP BY
          resourceID,
          NumberOfCores0,
          NumberOfLogicalProcessors0) CPU On CPU.ResourceID = NAC.ResourceID --CPU data
join (Select DISTINCT ResourceID,  sum(DSK.Size0) over (partition by resourceid)/1014 [Total_GB]
         FROM   [dbo].v_GS_LOGICAL_DISK DSK
         WHERE  DSK.Size0 > 0) DSK On DSK.ResourceID = NAC.ResourceID -- HDD data
WHERE (IPAddress0 IS NOT NULL) AND (NOT (IPAddress0 LIKE '169%')) AND (NOT (IPAddress0 LIKE '192%')) AND (NOT (IPAddress0 LIKE '0%')) AND IPAddress0 not in ('2.2.2.2','3.3.3.3','4.4.4.4') --local and specific IPs excluded
ORDER BY Name0