Long back i posted report http://eskonr.com/2009/12/sccm-report-for-computers-asset-information-including-sn-number-and-model-name/ to get computer information like computer name,Serial number,Processor,Physical memory,service Pack,computer model etc
In this post ,i have modified the report little bit from previous post to add more fields that gives you lot more information about the computer . It is very simple and straight forward report that just use Where class
what columns do you get after running this report ? Name,Serialnumber, Manufacturer,Model,Processor,Memory (KBytes),Size (MBytes),MAC Adress,IP Adress,AD Site,Last user logged in,Operating System,Service Pack,Creationdate in SMS,Last Hardware Scan
SELECT
A.Name0,
MAX (B.SerialNumber0) AS 'Serialnumber',
A.Manufacturer0,
A.Model0, C.Name0 AS 'Processor',
D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
MAX ( E.Size0 ) AS 'Size (MBytes)',
MAX (F.MACAddress0) AS 'MAC Adress',
MAX (F.IPAddress0) AS 'IP Adress',
G.AD_Site_Name0 AS 'AD Site',
MAX (A.UserName0) AS 'Last user logged in',
H.Caption0 AS 'Operating System',
H.CSDVersion0 AS 'Service Pack',
G.Creation_Date0 AS 'Creationdate in SMS',
I.LastHWScanFROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS IWHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceIDGROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan
You can also other custom values from SQL Views ,please take a look Creating Custom Reports By Using Configuration Manager 2007 SQL Views http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=22052
I would like to get an Hardware inventory report with the details of all the users like:
user id, machine serial number,model number,monitor serial number,keyboard,mouse serial numbers and models via SCCM.
That is going to be complete custom solution .All the information which you asked is already available in SCCM except monitor info and mouse serial etc for which you may have to use custom solution or invest time to get it done.
For the rest ,you can use SQL code given in the blog post.
Regards,
Eswar
In few entries there is null value of SeiralNumber column. How we will get those machines serial number.
You may have login and check the clients that have no SN what is missing in those. What model are those missing SN ?
Regards,
Eswar
Thank you for taking the time to generate the report. Can you include internet explorer version in the report? Basically what i like to have is computers that have last user log on and IE version.
Thank you
software scan to retrive the information for .exe files in CM12 is very slow.Garth has blogged post on this but would recomend to go through sherry method to retrive the internet explorer info via hardware inventory.
Will this report give me the following information for all my computers in SCCM 2012?
yes,it will.what is not working for you ?
Hello,
Very helpful !
What if I want to prompt for a Collection ? because if I run it this it works, but listing all the systems.
Regards.
here you go :
SELECT
A.Name0,
MAX (B.SerialNumber0) AS 'Serialnumber',
A.Manufacturer0,
A.Model0, C.Name0 AS 'Processor',
D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
MAX ( E.Size0 ) AS 'Size (MBytes)',
MAX (F.MACAddress0) AS 'MAC Adress',
MAX (F.IPAddress0) AS 'IP Adress',
G.AD_Site_Name0 AS 'AD Site',
MAX (A.UserName0) AS 'Last user logged in',
H.Caption0 AS 'Operating System',
H.CSDVersion0 AS 'Service Pack',
G.Creation_Date0 AS 'Creationdate in SMS',
I.LastHWScan
FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I,
v_FullCollectionMembership fcm
WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID and
fcm.ResourceID=A.ResourceID and
fcm.CollectionID='CEN00F49'
GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan
Is it right if I create report from SCCM console title Reporting/Reports/NEW--> Report ?
yes you need to do as you said....
Last time I created a new report. Supposed to build it in two layers. If I try to build a report from the example (single layer), we obtain the error. Specifically references the need to properly create?
Ok. How many layers of the reports are to be produced? 3!?
what do you mean by how many layers ?
Where this query to enter?
In SCCM Reports...
SQL report is available in the report itself ? is it invisible ?