Solved

SCCM 2012 Report

Posted on 2016-08-09
8
188 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sccm client without collection 1 64
COPY from excel to notepad 3 68
IT Desktop Support 11 90
Problem: Word 2016 cannot import a .vsdx created with Visio 2013 6 54
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Troubleshooting common task sequence error codes
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

860 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