HELP!!! Need help pulling a report in SCCM

[usmarine2141]

I have been assigned to pull a report today that is due for our largest client and i have no experience with using a sql query to pull the information i need. Below is what i need from SCCM this has to be only for 2 of their sites. If anyone could help me i would be so very grateful.

 

 

 

Aggregate reports on all hardware specs of pc’s and servers, including:

§ Manufacturer, model number, serial number, processor, OS, installed RAM, maximum RAM, last check in time, IP Address

§ Patch Report on all pc’s and servers

§ Audit on all A/V and Anti-Malware on all pc’s and servers

Software Licensing reports on all pc’s and servers for the following:

§ Microsoft

§ Java

§ Adobe

Software Licensing reports on all pc’s and servers for the following:

§ Microsoft

§ Java

§ Adobe

Printers/Scanners/MFPs

 

 

I started with this query but i couldnt figure out how to edit it for the information i need.

 

http://smsug.ca/blogs/garth_jones/archive/2008/02/04/basic-all-in-one.aspx

[usmarine2141]

Update:

 

I i have pulled Manufacturer, model number, serial number, processor, OS, installed RAM with the following

 

 

select distinct

R.Netbios_Name0 AS 'Computer Name',

R.Resource_Domain_OR_Workgr0 AS 'Domain/Workgroup',

S.SiteName as 'SMS Site Name',

R.AD_Site_name0 as 'AD Site',

'Top Console User' = CASE

when (SCUM.TopConsoleUser0 is NULL or SCUM.TopConsoleUser0 = '-1') then 'Unknown'

Else SCUM.TopConsoleUser0

End,

OS.Caption0 AS 'Operating System',

OS.CSDVersion0 AS 'Service Pack Level',

SEU.SerialNumber0 AS 'Serial Number',

SEU.SMBIOSAssetTag0 AS 'Asset Tag',

CS.Manufacturer0 AS 'Manufacturer',

CS.Model0 AS 'Model',

RAM.TotalPhysicalMemory0/1024 AS 'Memory (MB)',

Pro.NormSpeed0 AS 'Processor (GHz)',

LD.Size0 as 'Disk Space (MB) on C:\',

LD.FreeSpace0 as 'Free Disk Space (MB) on C:\'

from

v_R_System_Valid R

inner join v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = R.ResourceID)

left join v_GS_SYSTEM_ENCLOSURE_UNIQUE SEU on (SEU.ResourceID = R.ResourceID)

inner join v_GS_COMPUTER_SYSTEM CS on (CS.ResourceID = R.ResourceID)

inner join v_GS_X86_PC_MEMORY RAM on (RAM.ResourceID = R.ResourceID)

inner join v_GS_PROCESSOR Pro on (Pro.ResourceID = R.ResourceID)

inner join v_FullCollectionMembership FCM on (FCM.ResourceID = R.ResourceID)

left join v_Site s on (FCM.SiteCode = S.SiteCode)

inner join v_GS_LOGICAL_DISK LD on (LD.ResourceID =R.ResourceID) and LD.DeviceID0 = SUBSTRING(OS.WindowsDirectory0,1,2)

left outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on (SCUM.ResourceID = R.ResourceID)

Where

R.AD_Site_name0='VanNuys' OR R.AD_Site_name0='SaltLakeCity'

Order by

R.Netbios_Name0

[GarthMJ]

I'm sorry to say this is not a simple task and would take weeks of effort for me to create these reports and this is clearly not a single report but my guess at least 10 different reports.

 

My suggestion is a few fold, break it down into a few different reports.

Start with the high level and get the result for each item listed below.

Remove IP address from all reports. This will cause you headaches.

For items like, Audit on AV and malware, define exactly how you would do this task manually, then you can try to automated it.

[Garrett804]

Garth is correct on this. That is a big custom report which would require multiple table lookups at first manually to even find the data and then to compile it all together in the end.

 

Most of the first report for OS, RAM, etc.. though you can get off the "Summary of computers in a collection" report under Asset Intelligence.

 

This is a simple report I made for my boss just since he wanted just the Machine name, Login ID, Full Name, Last Logon Time, Make, Model, Serial of our machines. You can always expand on this though as you find the tables you need to pull the other information you are wanting.

 

Select SD.Name0 Machine_Name,
--SD.Resource_Domain_OR_Workgr0 as Resource_Domain,
SD.User_Name0 as Login_ID,
--SD.User_Domain0 as Account_Domain,
USR.Full_User_Name0 as Full_Name,
PCB.SerialNumber0 as Serial_Number,
CS.Manufacturer0 as Manufacturer,
CS.Model0 as Model,
--SAS.SMS_Assigned_Sites0 as Assigned_Site_Code
sd.last_logon_timestamp0 as Last_Logon_Time,
COL.name

From v_R_System as SD
Join v_FullCollectionMembership as FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection as COL on FCM.CollectionID = COL.CollectionID
Join v_R_User as USR on SD.User_Name0 = USR.User_Name0
Join v_GS_PC_BIOS as PCB on SD.ResourceID = PCB.ResourceID
Join v_GS_COMPUTER_SYSTEM as CS on SD.ResourceID = CS.ResourceID
--Join v_RA_System_SMSAssignedSites as SAS on SD.ResourceID = SAS.ResourceID

Where COL.name = @Collections

Order by SD.Name0