Solved

SCCM 2012 Report

Posted on 2016-08-09
8
130 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 22

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 22

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Remote Apps is a feature in server 2008 which allows users to run applications off Remote Desktop Servers without having to log into them to run the applications.  The user can either have a desktop shortcut installed or go through the web portal to…
This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

895 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

13 Experts available now in Live!

Get 1:1 Help Now