SCCM hardware & software inventory Report
-
Thursday, December 18, 2008 7:20 AMDear All,
How to get the following report from SCCM using SQL Query .
Computer Type
Computer Name
IP Address
Make
Model
Part No
Serial No
HDD Size
Free Disk
RAM
Processor
OS
Employee
Employee ID
Company (of Employee)
Regards,
Gopinath.D
Team Leader
All Replies
-
Thursday, December 18, 2008 8:09 AMHi,
Take a look at this blog, here you will find a step-by-step describtion on how to create you own reports. I am pretty sure, that you will be able to create your own report after running through the 3 post in the blog http://blog.coretech.dk/category/confmgr07/config-mgr-inventory-and-reporting/
Kent Agerlund -
Friday, December 19, 2008 1:14 PM
Save this code as a .mof and import it into reports.
// *********************************************************************************
//
// Created by SMS Export object wizard
//
// Friday, December 19, 2008 created
//
// File Name: all-in-one.mof
//
// Comments :
//
//
// *********************************************************************************
// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
Category = "Department Reports";
Comment = "";
GraphCaption = "";
GraphXCol = 1;
GraphYCol = 2;
MachineDetail = FALSE;
MachineSource = FALSE;
Name = "All-In-One Machine Details";
NumPrompts = 1;
RefreshInterval = 0;
ReportGUID = "{435A5365-5EE2-4C85-BC2F-6A894E7A682B}";
ReportParams = {
instance of SMS_ReportParameter
{
AllowEmpty = FALSE;
DefaultValue = "";
PromptText = "Enter Machine Name";
SampleValueSQL = "select Name0 from v_R_System order by Name0";
VariableName = "MachineName";
}};
SecurityKey = "";
SQLQuery = "select v_R_System.Name0, v_R_System.Resource_Domain_OR_Workgr0, v_R_System.User_Name0 as Username, v_R_User.Full_User_Name0, v_R_System.AD_Site_Name0 from v_R_System, v_R_User where v_R_System.Name0 like @MachineName and v_R_System.User_Name0 = v_R_User.User_Name0 and v_R_System.User_Domain0 = v_R_User.Windows_NT_Domain0
\n
\nselect v_GS_COMPUTER_SYSTEM.Manufacturer0, v_GS_COMPUTER_SYSTEM.Model0, v_GS_SYSTEM_ENCLOSURE.SerialNumber0 from v_R_System, v_GS_SYSTEM_ENCLOSURE, v_GS_COMPUTER_SYSTEM where v_R_System.Name0 like @MachineName and v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID and v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
\n
\nselect v_R_System.Operating_System_Name_and0 as OperatingSystem, v_GS_OPERATING_SYSTEM.CSDVersion0 as ServicePack, v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 as Memory from v_R_System, v_GS_X86_PC_MEMORY, v_GS_OPERATING_SYSTEM where v_R_System.Name0 like @MachineName and v_R_System.ResourceID = v_GS_X86_PC_MEMORY.ResourceID and v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
\n
\nselect v_GS_LOGICAL_DISK.Name0 as Drive, v_GS_LOGICAL_DISK.Size0 as Size, v_GS_LOGICAL_DISK.FreeSpace0 as FreeSpace from v_R_System, v_GS_LOGICAL_DISK where v_R_System.Name0 like @MachineName and v_GS_LOGICAL_DISK.DriveType0 = 3 and v_R_System.ResourceID = v_GS_LOGICAL_DISK.ResourceID order by v_GS_LOGICAL_DISK.Name0
\n
\nSelect v_GS_PROCESSOR.MaxClockSpeed0, v_GS_PROCESSOR.Name0,v_GS_PROCESSOR.Manufacturer0 from v_r_system, V_gs_processor where v_R_System.Name0 like @MachineName and v_R_System.ResourceID = v_GS_processor.ResourceID
\n
\nselect v_GS_ADD_REMOVE_PROGRAMS.Publisher0, v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS.Version0 from v_R_System, v_GS_ADD_REMOVE_PROGRAMS where v_R_System.Name0 like @MachineName and v_R_System.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID order by v_GS_ADD_REMOVE_PROGRAMS.DisplayName0";
StatusMessageDetailSource = FALSE;
UnicodeData = FALSE;
XColLabel = "";
YColLabel = "";
};
// ***** End *****- Marked As Answer by Gopinath.D Wednesday, January 28, 2009 12:26 PM
-
Friday, December 19, 2008 2:08 PM
hi John Marcum,
Thanks for support .
I tried to copy the code to notepad and save as the report.mof .then import the repots but i not able to get any object in the SCCM Reports.i request you to provide me correct *.mof
Regards,
Gopinath.D
Team Leader -
Friday, December 19, 2008 2:10 PMhi Kent Agerlund,
its very usfull linke.
Regards,
Gopinath.D
Team Leader -
Thursday, April 09, 2009 6:40 PMJohn, I just sent you an email. Anyway I can get the mof file as well. Thanks.
-
Thursday, April 09, 2009 6:45 PMyou should have it in your inbox.
John Marcum, Systems Management Architect - www.TrueSec.com -
Friday, October 02, 2009 1:37 PM Hi Guys,
We currently run this report to get info on
Site
Machine name
Model
Manufacturer
Serial Number
SELECT TOP 100 PERCENT
dbo.v_R_System.AD_Site_Name0 AS [AD Site],
dbo.v_R_System.Name0 AS [Machine Name],
[Manufacturer] = CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, '')) WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 END,
[Model] = CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Model0, '')) WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Model0 END,
[Serial Number] = CASE LEN(ISNULL(dbo.v_GS_PC_BIOS.SerialNumber0, ''))
WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_PC_BIOS.SerialNumber0 END
FROM dbo.v_R_System LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_LOGICAL_DISK ON dbo.v_R_System.ResourceID = dbo.v_GS_LOGICAL_DISK.ResourceID LEFT OUTER JOIN
dbo.v_GS_PROCESSOR ON dbo.v_R_System.ResourceID = dbo.v_GS_PROCESSOR.ResourceID LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID LEFT OUTER JOIN
dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID LEFT OUTER JOIN
dbo.v_GS_DISK ON dbo.v_R_System.ResourceID = v_GS_DISK.ResourceID
WHERE (dbo.v_R_System.Client0 = 1)AND (ISNULL(dbo.v_GS_PROCESSOR.DeviceID0, 'CPU0') = 'CPU0') AND (ISNULL(dbo.v_GS_LOGICAL_DISK.DeviceID0, 'C:') = 'C:') AND (dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 3 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 4 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 6 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 15 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 8 OR dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 = 10) AND (dbo.v_GS_DISK.InterfaceType0 = 'IDE')
ORDER BY dbo.v_R_System.AD_Site_Name0 DESC, dbo.v_R_System.User_Name0
My boss wants me to add the IP info also (maintenance reasons and for our helpdesk).
I am currently downloading report builder to play around a familiarize myself. Anyone maybe have a quick fix for me?
Regards
Carlo- Proposed As Answer by Rajesh_Ramanathan Sunday, February 13, 2011 7:47 AM
-
Monday, May 03, 2010 11:51 AM
John,
Appriciate, If you can send the all-in-one.mof to my mail id ----momeen dot pasha at hotmail.com
Also is it possible to pull the report based on the site CODE?
MOMEEN -
Sunday, February 13, 2011 7:48 AM
Hi,
SQL query to include IP Addres
SELECT TOP (100) PERCENT dbo.v_GS_COMPUTER_SYSTEM.Domain0 AS Domain, dbo.v_GS_COMPUTER_SYSTEM.UserName0 AS [Last Logged on User],
dbo.v_R_System.AD_Site_Name0 AS [AD Site], dbo.v_R_System.Name0 AS [Machine Name],
CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, ''))
WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 END AS Manufacturer,
CASE LEN(ISNULL(dbo.v_GS_COMPUTER_SYSTEM.Model0, ''))
WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_COMPUTER_SYSTEM.Model0 END AS Model, CASE LEN(ISNULL(dbo.v_GS_PC_BIOS.SerialNumber0, ''))
WHEN 0 THEN 'No_Data' ELSE dbo.v_GS_PC_BIOS.SerialNumber0 END AS [Serial Number],
dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack], REPLACE
((SELECT IPAddress0 + ', '
FROM v_GS_NETWORK_ADAPTER_CONFIGUR
WHERE resourceID = v_R_System.resourceID AND IPAddress0 NOT LIKE '169%' AND IPAddress0 NOT LIKE '0.%' AND
IPAddress0 NOT LIKE '%::%' AND IPAddress0 NOT LIKE '192.%' FOR XML PATH('')) + '..', ', ..', '') AS [IP Addresses]
FROM dbo.v_R_System LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
WHERE (dbo.v_R_System.Client0 = 1)
ORDER BY [AD Site] DESC, dbo.v_R_System.User_Name0 -
Thursday, March 31, 2011 12:11 PM
There's not way to do attachements in this forum. I could email you the mof if you send me your email address. Just send me an email and I will reply with the mof. My address is my first name last Name without spaces or anything at gmail dot com.
I also copy & past code to notepad and save it as .mof but SCCM import wizard doesnt seems to like this file. Is it about encoding of this file? It has to be UNICODE or what? I rather understand why, than getting it thru email from you...
thanks for explanation
-
Monday, April 11, 2011 2:20 PMcan you please send me a mof to can i wanted to get a full inventory of the my boxes that looks like the software invetory without all the drill own stuff just a simple sheet with h/w inventory please sen to my e-mail please
-
Friday, May 13, 2011 7:50 PM
I just want to find out the PC model types in a collection -
Thanks -Jim
jandrews@consolidated.net
-
Friday, May 13, 2011 8:03 PMModerator
jimbonano, I suggest that you start your own thread as your request does not seem to fit this thread.
http://www.enhansoft.com/ -
Monday, May 16, 2011 1:22 PM
I just want to find out the PC model types in a collection -
Thanks -Jim
jandrews@consolidated.net
Simple enough....
SELECT dbo.v_R_System_Valid.Netbios_Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0
FROM dbo.v_R_System_Valid INNER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System_Valid.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
where resourceID in Select resourceID from (select ResourceID From dbo.v_FullCollectionMembership
where CollectionID =@CollID)
then for the @CollID prompt use this:begin
if (@__filterwildcard = '')
SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
else
SELECT DISTINCT CollectionID, Name FROM v_Collection
WHERE Name like @__filterwildcard
ORDER BY Name
end
John Marcum | http://myitforum.com/cs2/blogs/jmarcum/| -
Tuesday, May 24, 2011 9:19 PM
I am new to all of this and SQL illiterate (but learning). I greatly appreciate any help.
How would I add OS and last hardware scan to the above?
Regards,
Tammie
-
Friday, September 16, 2011 2:09 PM
Hi John,
Could I also get a copy of this all-in-one.mof on a .zip email? I know your busy, please when you have time.
Email is: Chris.Massara@genon.com
Thanks
-
Friday, September 16, 2011 4:20 PMModerator
Could I also get a copy of this all-in-one.mof on a .zip email? I know your busy, please when you have time.
But you can do it yourself.
-
Copy the text listed above into a text file
-
Save it as allinone.mof
-
Use the import function.
http://www.enhansoft.com/ -
-
Friday, September 16, 2011 5:01 PM
yea I did, didn't work.
John sent it to me, I am greatful.
Thanks Garth and John as always
-
Friday, September 16, 2011 6:10 PMModerator
Really all you need is the query. All the rest is fluff.
Remember that \n in the query is line feed.
http://www.enhansoft.com/ -
Friday, September 16, 2011 9:05 PM
Well I think the problem is between the keyboard and the chair. "smile"
-
Friday, September 16, 2011 9:08 PM
its so weird it posted wambofilo instead of my username of massara
I am going home its Friday!
-
Wednesday, September 28, 2011 1:30 PM
Hello,
i'm also having issues copying the code. Could someone send me the file?
Many Thanks,
Rob
robnapiza@hotmail.com
-
Wednesday, September 28, 2011 10:08 PMModeratorHi Rob, did you try copying just the query listed above? That is all you need. If you are having a issue, what is the error?
http://www.enhansoft.com/ -
Thursday, December 08, 2011 3:33 PMHello, I am trying to use your report and I was able to import it with out any problems, but when I run the report no data is returned? Am I missing something?
-
Thursday, December 08, 2011 3:48 PMModeratorThere are four+ queries above, which one are you having problem with?
http://www.enhansoft.com/ -
Thursday, December 08, 2011 7:14 PM
specifically Component Status History (last 30 days) but any of the reports I have tried comes back with "No matching records found"
-
Thursday, December 08, 2011 7:58 PMModerator
specifically Component Status History (last 30 days) but any of the reports I have tried comes back with "No matching records found"
That is not one fo the queries above. It sound like you don't have your site setup correctly. I suggest that you start your own thread and check you site to see if you are getting any data from any clietns first.
http://www.enhansoft.com/ -
Wednesday, May 23, 2012 5:15 AM
Error while importing the mof file. please help
Thanks!
-
Wednesday, May 23, 2012 10:21 AMModerator
We need more details as to what you did? Why not just used the query listed above?
http://www.enhansoft.com/
-
Wednesday, May 23, 2012 12:07 PMI copied the entries given by John on dec 19th 2008 to a notepad and saved the file as .mof. While importing in the reports it gave the above error message..
- Proposed As Answer by KB Pradeep Wednesday, May 23, 2012 12:32 PM
- Unproposed As Answer by KB Pradeep Wednesday, May 23, 2012 12:32 PM
-
Wednesday, May 23, 2012 12:32 PMModerator
With the copying and pasting you are likely running into a formatting, word wrapping or font issue.. So just grab the query, itself. Heck here is a clean version of this query http://smsug.ca/blogs/garth_jones/archive/2008/02/04/basic-all-in-one.aspx
http://www.enhansoft.com/
- Proposed As Answer by KB Pradeep Wednesday, May 23, 2012 12:56 PM
-
Wednesday, May 23, 2012 12:56 PMThanks Garth, I'm able to get the reports now.
-
Monday, June 11, 2012 11:53 AM
Hello Garth,
Can you let me know how to exclude updates while checking add/Remove programs. When I run the report Iam getting the updates which are installed on the computers which is increasing the size of my report. Please let me know if this is possible or not.
Thanks in advance
Vamshi
-
Monday, June 11, 2012 5:30 PMModerator
Hello Garth,
Can you let me know how to exclude updates while checking add/Remove programs. When I run the report Iam getting the updates which are installed on the computers which is increasing the size of my report. Please let me know if this is possible or not.
There is no simple way to exclude Software Updates for ARP data.
http://www.enhansoft.com/
-
Thursday, June 28, 2012 10:26 AM
Hi Jones,
Can you help to create report with IP deatails.
Thanks
Jijo
-
Thursday, June 28, 2012 11:00 AMModerator
Can you help to create report with IP deatails.
There are several built-in report that provide IP details un the Network Category. Have you looked at them?
http://www.enhansoft.com/
-
Thursday, June 28, 2012 11:33 AM
Thanks for prompt reply
Yes, I want all in one report with IP
Jijo
-
Thursday, June 28, 2012 11:46 AMModerator
Adding IP address will cause duplicate rows and as such I avoid using it, plus IMO it adds little value to have the IP address.
http://www.enhansoft.com/
-
Thursday, June 28, 2012 12:28 PM
Thanks Garth
-
Tuesday, July 24, 2012 6:43 PMHi this SQL query works great !! Can you include the servers total disk space in MB and free space as well.
-
Tuesday, July 24, 2012 11:16 PMModerator
Hi this SQL query works great !! Can you include the servers total disk space in MB and free space as well.
Yes, you can do that, take a look at the built-in report category called “Hardware – Disk” it will help you get started with this request.
http://www.enhansoft.com/
-
Thursday, October 18, 2012 2:36 AMHi Garth,
With the copying and pasting you are likely running into a formatting, word wrapping or font issue.. So just grab the query, itself. Heck here is a clean version of this query http://smsug.ca/blogs/garth_jones/archive/2008/02/04/basic-all-in-one.aspx
http://www.enhansoft.com/
May I know the report still there or not? i can't get it. If possible pls email to me, many thanks.
-
Thursday, October 18, 2012 2:41 AMModeratorHi Garth,
With the copying and pasting you are likely running into a formatting, word wrapping or font issue.. So just grab the query, itself. Heck here is a clean version of this query http://smsug.ca/blogs/garth_jones/archive/2008/02/04/basic-all-in-one.aspx
http://www.enhansoft.com/
May I know the report still there or not? i can't get it. If possible pls email to me, many thanks.
The report(query) is still there.. Just tried the link myself.
http://www.enhansoft.com/
-
Thursday, October 18, 2012 2:47 AM
I tried copy and paste and save to *.mof but failed to import to sccm report, may I know how to get it. many thanks
-
Thursday, October 18, 2012 2:50 AMModerator
I tried copy and paste and save to *.mof but failed to import to sccm report, may I know how to get it. many thanks
It is not a MOF file. You need to create the report and use the query to populate the report.
http://www.enhansoft.com/
-
Thursday, October 18, 2012 2:54 AMGreat, i got it.....many thanks
.png)
