Solved

SCCM 2012 Report

Posted on 2016-08-09
8
102 Views
Last Modified: 2016-08-28
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?
0
Comment
Question by:craigleenz
  • 3
  • 3
  • 2
8 Comments
 
LVL 21

Expert Comment

by:yo_bee
ID: 41750592
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
 
LVL 12

Expert Comment

by:Benjamin Voglar
ID: 41759120
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
 
LVL 21

Expert Comment

by:yo_bee
ID: 41759227
Nice query, but it does not have the one value that the Asker is inquiring about.
0
 
LVL 12

Expert Comment

by:Benjamin Voglar
ID: 41759260
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
Do email signature updates give you a headache?

Do you feel like you are constantly making changes to email signatures? Are the images not formatting how you want them to? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today.

 

Author Comment

by:craigleenz
ID: 41761914
thank you Benjamin, I will test this over the weekend
0
 

Author Comment

by:craigleenz
ID: 41761918
is this run in against the SQL instance, or do I need to create a separate report or something?
0
 
LVL 12

Accepted Solution

by:
Benjamin Voglar earned 500 total points
ID: 41761999
0
 

Author Closing Comment

by:craigleenz
ID: 41774017
thanks, closet to what I;m wanting, so awarding the points,
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Know what services you can and cannot, should and should not combine on your server.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now