SCCM 2012 Report

Team, I need to extract a report which contain's machine name, the last time that logged onto the network as well as a summary of the hardware (IE: Manufacturer/Model/Serial/Disk Space etc). In SCCM Report - Hardware 01A - Summary of computers in a specific collection give me 95% of the information, minus the last logon time, anyway way we can include that information?
Craig PaulsenSystems EngineerAsked:
Who is Participating?
 
Benjamin VoglarIT ProCommented:
0
 
yo_beeDirector of Information TechnologyCommented:
You can pull this info from AD.

Import-Module ActiveDirectory

 Get-ADComputer -filter * -Properties lastLogonDate | Select Name,LastLogonDate | export-csv -Path <FullFileName> -NoTypeInformation

Open in new window

0
 
Benjamin VoglarIT ProCommented:
Create a custum report in SCCM. I call this report (All in One)

SELECT  distinct 
 CS.name0 as 'Computer Name', 
 CS.domain0 as 'Domain', 
 CS.UserName0 as 'User', 
 BIOS.SerialNumber0 as 'Bios serial', 
 SE.SerialNumber0 as 'System Enclosure serial',
 CS.Manufacturer0 as 'Manufacturer', 
 CS.Model0 as 'model', 
 OS.Caption0 as 'OS', 
 RAA.SMS_Assigned_Sites0 as 'Site', 
 RAM.TotalPhysicalMemory0 as 'Total Memory', 
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size', 
 sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space', 
 CPU.CurrentClockSpeed0 as 'CPU Speed' 
from  
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
 right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID  
 right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID  
 right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
 right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID 
 right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID 
 right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID    
 right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID 
where 
 LDisk.DriveType0 =3
group by 
 CS.Name0, 
 CS.domain0,
 CS.Username0, 
 BIOS.SerialNumber0, 
 SE.SerialNumber0,
 CS.Manufacturer0, 
 CS.Model0, 
 OS.Caption0, 
 RAA.SMS_Assigned_Sites0,
 RAM.TotalPhysicalMemory0, 
 CPU.CurrentClockSpeed0

Open in new window

1
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
yo_beeDirector of Information TechnologyCommented:
Nice query, but it does not have the one value that the Asker is inquiring about.
0
 
Benjamin VoglarIT ProCommented:
Ups,

I paste wrong querry :)

Here is the right one:

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',
 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 Model,
 BIOS.SerialNumber0 'Serial Number', 
 CONVERT (DATE,BIOS.ReleaseDate0) AS BIOSDate, 
 BIOS.SMBIOSBIOSVersion0 AS BIOSVersion, 
 (SELECT CONVERT(DATE,SYS.Creation_Date0)) 'Managed Date', 
 SUM(ISNULL(RAM.Capacity0,0)) 'Memory (MB)', 
 COUNT(RAM.ResourceID) '# Memory Slots',
 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
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 , 
 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,
 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

Open in new window



scom.JPG
1
 
Craig PaulsenSystems EngineerAuthor Commented:
thank you Benjamin, I will test this over the weekend
0
 
Craig PaulsenSystems EngineerAuthor Commented:
is this run in against the SQL instance, or do I need to create a separate report or something?
0
 
Craig PaulsenSystems EngineerAuthor Commented:
thanks, closet to what I;m wanting, so awarding the points,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.