?
Solved

SCCM 2007 report/query

Posted on 2014-03-24
5
Medium Priority
?
610 Views
Last Modified: 2014-03-28
Please advise on a Microsoft System Center 2007 report/query.

I'd need the following data: Computer name, Operating system, Manufacturer, Model, Subnetmask,UserName, Number of days since discovery, Number of days since inventory.

Please advise howto do this.
J.
0
Comment
Question by:janhoedt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Leon Taljaard
ID: 39950933
0
 

Author Comment

by:janhoedt
ID: 39952621
Thanks but no, sorry, doesn't help me a lot.
I'm not familiar with these queries. Even a copy paste of the query resulted in errors within SCCM 2007.
0
 
LVL 8

Expert Comment

by:Leon Taljaard
ID: 39952642
Hi

This link http://technet.microsoft.com/en-us/library/dd334578.aspx I don't have SCCM 2007 but if I create this in my SCCM 2012 I get a last HW scan report for all of my machines.

Not sure if you are also familiar with report builder, you can also use this to create your own custom reports.

You can also look at a solution similar from Experts Exchange

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Windows_Server_2008/Q_27158708.html

Often the extra things you require need some more customizing.

Here is a few more

select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where ResourceId in (select SMS_R_System.ResourceID from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan,GetDate()) > 30) or ResourceId not in (select ResourceID from SMS_G_System_WORKSTATION_STATUS) 

Open in new window

But  if you are looking for the clients status based on its discovery method,here you go with report:

select a.Name0,b.AgentName,b.AgentTime from v_R_System a
 join v_AgentDiscoveries b on b.ResourceId=a.ResourceId where
 (AgentName  like 'Heartbeat Discovery' and DATEDIFF(Day,AgentTime,Getdate())>=30 ) OR
 (AgentName  like 'SMS_AD_SYSTEM_DISCOVERY_AGENT' and DATEDIFF(Day,AgentTime,Getdate())>=30 )

Open in new window


Thanks
0
 

Author Comment

by:janhoedt
ID: 39952657
Thanks.

The joins ring a bell, inner join etc also. I'd love to dive into it and I will certainly but
can you help me with creating this report?

Your first query gives an error, the second gives an output I actually don't need.

I'm looking for a report with the following fields:

Computer name
Operating system
Manufacturer
Model
Subnetmask
User name
Number of days since discovery
Number of days since inventory



J.
0
 
LVL 8

Accepted Solution

by:
Leon Taljaard earned 2000 total points
ID: 39952854
Ok no problem

Try this and see what you get, the user Name might not be 100% but it will give you a start

SELECT DISTINCT 
                      a.Name0 AS 'Computer Name', a.Operating_System_Name_and0 AS 'Operating System', b.AgentName, v_GS_COMPUTER_SYSTEM.Manufacturer0 AS 'Manufacturer', 
                      v_GS_COMPUTER_SYSTEM.Model0 AS 'Model', v_GS_NETWORK_ADAPTER_CONFIGURATION.IPSubnet0, b.AgentTime, 
                      v_GS_WORKSTATION_STATUS.LastHWScan AS 'Last Inventory Scan', v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0
FROM         v_R_System AS a INNER JOIN
                      v_AgentDiscoveries AS b ON b.ResourceId = a.ResourceID INNER JOIN
                      v_GS_COMPUTER_SYSTEM ON a.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
                      v_GS_WORKSTATION_STATUS ON a.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID INNER JOIN
                      v_GS_NETWORK_ADAPTER_CONFIGURATION ON a.ResourceID = v_GS_NETWORK_ADAPTER_CONFIGURATION.ResourceID INNER JOIN
                      v_GS_SYSTEM_CONSOLE_USAGE ON a.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE.ResourceID
WHERE     (b.AgentName LIKE 'SMS_AD_SYSTEM_DISCOVERY_AGENT') AND (v_GS_NETWORK_ADAPTER_CONFIGURATION.IPSubnet0 NOT LIKE 'NULL'

Open in new window

)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Here is the example of setting up enterprise library configuration in asp.net. One of the great enhancements that comes with the Enterprise Library is the Enterprise Library Configuration Console. This can be found on your Start menu under Progra…
Hi Everyone Now IIS AppPool issues is something we have always had because of the amazing custom development and in-house applications we have :) The issues we had were getting out of control and it was time to do something about it, just for…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

765 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