Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @Today AS DATE
- SET @Today = GETDATE()
- DECLARE @BackInTime AS DATE
- SET @BackInTime = DATEADD(DAY, -30, @Today )
- SELECT DISTINCT
- SYS.ResourceID,
- SYS.Name0 'Name',
- SYS.AD_Site_Name0 'ADSite',
- CS.UserName0 'User Name',
- ud.[telephoneNumber0],
- ud.[mobile0] ,
- ud.[title0] ,
- ud.[department0] ,
- ud.[manager0],
- ud.[company0],
- CASE
- WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
- ELSE U.TopConsoleUser0
- END AS TopUser,
- REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win') OS,
- REPLACE (OS.CSDVersion0,'Service Pack','SP') 'Service Pack',
- CS.Manufacturer0 'Manufacturer',
- CS.Model0,
- BIOS.SerialNumber0 'Serial Number',
- CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate,
- BIOS.SMBIOSBIOSVersion0 AS BIOSVersion,
- (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date',
- RAM.Capacity0 'Memory GB',
- DeviceLocator0 'MemorySlot',
- REPLACE (cs.SystemType0,'-based PC','') 'Type',
- SUM(D.Size0) / 1024 AS 'Disk Size GB',
- CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) AS 'Last Reboot Date/Time',
- CONVERT(VARCHAR(26), OS.InstallDate0, 101) AS 'Install Date',
- CONVERT(VARCHAR(26), WS.LastHWScan, 101) AS 'Last Hardware Inventory',
- CONVERT(VARCHAR(26), CH.LastOnline, 101) AS 'Last Seen Online',
- SYS.Client_Version0 as 'SCCM Agent Version',
- CPU.Manufacturer AS 'CPU Man.',
- CPU.[Number of CPUs] AS '# of CPUs',
- CPU.[Number of Cores per CPU] AS '# of Cores per CPU',
- CPU.[Logical CPU Count] AS 'Logical CPU Count',
- US.ScanTime AS ' Windows Updates Scan Time' ,
- US.LastErrorCode AS ' Windows Updates Last Error Code' ,
- US.LastScanPackageLocation AS ' Windows Updates Last Package Location' ,
- CASE SE.ChassisTypes0
- WHEN '1' THEN 'Other'
- WHEN '2' THEN 'Unknown'
- WHEN '3' THEN 'Desktop'
- WHEN '4' THEN 'Low Profile Desktop'
- WHEN '5' THEN 'Pizza Box'
- WHEN '6' THEN 'Mini Tower'
- WHEN '7' THEN 'Tower'
- WHEN '8' THEN 'Portable'
- WHEN '9' THEN 'Laptop'
- WHEN '10' THEN 'Notebook'
- WHEN '11' THEN 'Hand Held'
- WHEN '12' THEN 'Docking Station'
- WHEN '13' THEN 'All in One'
- WHEN '14' THEN 'Sub Notebook'
- WHEN '15' THEN 'Space-Saving'
- WHEN '16' THEN 'Lunch Box'
- WHEN '17' THEN 'Main System Chassis'
- WHEN '18' THEN 'Expansion Chassis'
- WHEN '19' THEN 'SubChassis'
- WHEN '20' THEN 'Bus Expansion Chassis'
- WHEN '21' THEN 'Peripheral Chassis'
- WHEN '22' THEN 'Storage Chassis'
- WHEN '23' THEN 'Rack Mount Chassis'
- WHEN '24' THEN 'Sealed-Case PC'
- ELSE 'Undefinded'
- END AS 'PC Type'
- FROM
- v_R_System SYS
- INNER JOIN (
- SELECT
- Name0,
- MAX(Creation_Date0) AS Creation_Date
- FROM
- dbo.v_R_System
- GROUP BY
- Name0
- ) AS CleanSystem
- ON SYS.Name0 = CleanSystem.Name0 AND SYS.Creation_Date0 = CleanSystem.Creation_Date
- LEFT JOIN v_GS_COMPUTER_SYSTEM CS
- ON SYS.ResourceID=cs.ResourceID
- LEFT JOIN v_GS_PC_BIOS BIOS
- ON SYS.ResourceID=bios.ResourceID
- LEFT JOIN (
- SELECT
- A.ResourceID,
- MAX(A.[InstallDate0]) AS [InstallDate0]
- FROM
- v_GS_OPERATING_SYSTEM A
- GROUP BY
- A.ResourceID
- ) AS X
- ON SYS.ResourceID = X.ResourceID
- INNER JOIN v_GS_OPERATING_SYSTEM OS
- ON X.ResourceID=OS.ResourceID AND X.InstallDate0 = OS.InstallDate0
- LEFT JOIN v_GS_PHYSICAL_MEMORY RAM
- ON SYS.ResourceID=ram.ResourceID
- LEFT OUTER JOIN dbo.v_GS_LOGICAL_DISK D
- ON SYS.ResourceID = D.ResourceID AND D.DriveType0 = 3
- LEFT OUTER JOIN v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U
- ON SYS.ResourceID = U.ResourceID
- LEFT JOIN dbo.v_GS_SYSTEM_ENCLOSURE SE ON SYS.ResourceID = SE.ResourceID
- LEFT JOIN dbo.v_GS_ENCRYPTABLE_VOLUME En ON SYS.ResourceID = En.ResourceID
- LEFT JOIN dbo.v_GS_WORKSTATION_STATUS WS ON SYS.ResourceID = WS.ResourceID
- LEFT JOIN v_CH_ClientSummary CH
- ON SYS.ResourceID = CH.ResourceID
- LEFT JOIN (
- SELECT
- DISTINCT(CPU.SystemName0) AS [System Name],
- CPU.Manufacturer0 AS Manufacturer,
- CPU.ResourceID,
- CPU.Name0 AS Name,
- COUNT(CPU.ResourceID) AS [Number of CPUs],
- CPU.NumberOfCores0 AS [Number of Cores per CPU],
- CPU.NumberOfLogicalProcessors0 AS [Logical CPU Count]
- FROM [dbo].[v_GS_PROCESSOR] CPU
- GROUP BY
- CPU.SystemName0,
- CPU.Manufacturer0,
- CPU.Name0,
- CPU.NumberOfCores0,
- CPU.NumberOfLogicalProcessors0,
- CPU.ResourceID
- ) CPU
- ON CPU.ResourceID = SYS.ResourceID
- LEFT JOIN v_UpdateScanStatus US
- ON US.ResourceID = SYS.ResourceID
- LEFT OUTER JOIN [dbo].[User_DISC] ud
- ON ud.Unique_User_Name0 = CS.UserName0
- WHERE SYS.obsolete0=0 AND SYS.client0=1 AND SYS.obsolete0=0 AND SYS.active0=1 AND
- CH.LastOnline BETWEEN @BackInTime AND GETDATE()
- GROUP BY
- SYS.Creation_Date0 ,
- SYS.Name0 ,
- ud.[telephoneNumber0],
- ud.[mobile0],
- ud.[title0],
- ud.[department0],
- ud.[manager0],
- ud.[company0],
- SYS.ResourceID ,
- SYS.AD_Site_Name0 ,
- CS.UserName0 ,
- REPLACE((REPLACE((REPLACE((REPLACE((REPLACE((REPLACE (OS.Caption0, 'Microsoft Windows','Win')),'Enterprise','EE') ),'Standard','ST')),'Microsoft®','')),'Server','SRV')),'Windows','Win'),
- REPLACE (OS.CSDVersion0,'Service Pack','SP'),
- CS.Manufacturer0 ,
- CS.Model0 ,
- BIOS.SerialNumber0 ,
- REPLACE (cs.SystemType0,'-based PC','') ,
- CONVERT(VARCHAR(26), OS.LastBootUpTime0, 100) ,
- CONVERT(VARCHAR(26), OS.InstallDate0, 101) ,
- CONVERT(VARCHAR(26), WS.LastHWScan, 101),
- CASE
- WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0 IS NULL THEN 'N/A'
- ELSE U.TopConsoleUser0
- END,
- RAM.Capacity0 ,
- DeviceLocator0 ,
- CPU.Manufacturer,
- CPU.[Number of CPUs] ,
- CPU.[Number of Cores per CPU],
- CPU.[Logical CPU Count],
- US.ScanTime ,
- US.LastErrorCode ,
- US.LastScanPackageLocation ,
- CASE SE.ChassisTypes0
- WHEN '1' THEN 'Other'
- WHEN '2' THEN 'Unknown'
- WHEN '3' THEN 'Desktop'
- WHEN '4' THEN 'Low Profile Desktop'
- WHEN '5' THEN 'Pizza Box'
- WHEN '6' THEN 'Mini Tower'
- WHEN '7' THEN 'Tower'
- WHEN '8' THEN 'Portable'
- WHEN '9' THEN 'Laptop'
- WHEN '10' THEN 'Notebook'
- WHEN '11' THEN 'Hand Held'
- WHEN '12' THEN 'Docking Station'
- WHEN '13' THEN 'All in One'
- WHEN '14' THEN 'Sub Notebook'
- WHEN '15' THEN 'Space-Saving'
- WHEN '16' THEN 'Lunch Box'
- WHEN '17' THEN 'Main System Chassis'
- WHEN '18' THEN 'Expansion Chassis'
- WHEN '19' THEN 'SubChassis'
- WHEN '20' THEN 'Bus Expansion Chassis'
- WHEN '21' THEN 'Peripheral Chassis'
- WHEN '22' THEN 'Storage Chassis'
- WHEN '23' THEN 'Rack Mount Chassis'
- WHEN '24' THEN 'Sealed-Case PC'
- ELSE 'Undefinded'
- END ,
- CONVERT (DATE,BIOS.ReleaseDate0) ,
- BIOS.SMBIOSBIOSVersion0 ,
- SYS.Client_Version0 ,
- CONVERT(VARCHAR(26) ,CH.LastOnline, 101)
- ORDER BY SYS.Name0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement