System Center Configuration Manager TechCenter >
System Center Configuration Manager Forums
>
Configuration Manager Inventory
>
Old Machine Query
Old Machine Query
- I'm trying to figure out how to write two queries:
1.) Return all machines that have not been discovered in X amount of days by the SMS_AD_SYSTEM_DISCOVERY_AGENT.
2.) Return all machines in collection x and the last heartbeat received from them.
Thanks
**EDIT**
So apparently the SMS_R_System.AgentTime field contains the last heartbeat discovery time, but when I select it, I get a bunch of other times as well. How can I isolate the heartbeat time from the AD discovery time or client registration time?
Answers
- Sherry,
The right-click tools do indeed work for a query. In any event, I was finally able to resolve the problem without donating any more money into Microsoft's coffers. The problem was an invalid entry in the Internet Options -> Connections Tab -> LAN Settings -> "Advanced" manual bypass list area which caused the SCCM client to have a problem when it tried to read those settings. The problem is that everyone on this board was telling me that SCCM uses whatever proxy settings IE uses, but didn't tell me specifically that it uses the WinHTTP proxy settings defined by the proxycfg tool. What it seems that SCCM does is run a proxycfg -u command to import the settings in IE to the WinHTTP settings and use those. If there is an invalid entry or if the domain your ConfigMgr site is hosted on is not in this list, the clients will have trouble connecting. They do NOT read a proxy auto-config script as I was led to believe. They don't even look at it. As soon as I fixed the invalid entry (you can't have any drill-downs in the bypass list - everything must end at .com or .org or whatever) everything worked perfectly.- Marked As Answer by.Tim Harrison Thursday, November 05, 2009 1:29 PM
All Replies
- 1) check here: http://myitforum.com/articles/1/view.asp?id=5877 , the first query on that article, I think you could use that as a starting point, and just change the logic from less than 7 days, to more than xx days.
2) check here: http://www.myitforum.com/forums/m_204350/tm.htm#204350, for how someone else changed a default report to a group by collection. So you could take the default report of inventory date for a specific machine, and clone it & customize it to group by collection.
Standardize. Simplify. Automate. - Is there no way to isolate the heartbeat time from the other agent times in a query?
- In your query, join v_r_System with v_agentdiscoveries (on resourceid). The v_agentdiscoveries.agentname field should give you the discovery name - like 'Heartbeat'
Give that a try, and let me know if that gets you a bit closer. I will try to get to a SQL console here shortly and whip up a query.
Donnie Taylor Systems Management Engineer Dell, Inc. - Try this
http://smsug.ca/blogs/garth_jones/archive/2008/02/02/pc-s-hearthbeat-discovery-data.aspx
http://www.enhansoft.com/ - Garth,
This threw a syntax error when directly copied and pasted it into a ConfigMgr query. Do I need to edit it all to make it work for ConfigMgr as I see its for SMS?
Donnie,
It would be great if you could guide me i the right direction with some of the SQL... I am not very familiar with the database or SQL, so I'm kind of in uncharted waters. I haven't been successful in using any of the v_*_*** tables - I've only used the SMS_*_**** tables. - I don't see what's wrong with Garth's report. Looks fine to me.
I think we need to start with this....
Views (v_gs_ for example) can be used from the Reports node in your console.
WQL, (normally starting with the SMS_ identifiers), can only be used from the Query node, or when creating a collection.
I'd say 99% of the time, when you ask someone for a report, we'll give you report syntax, to be used in the Reports node, not used in the Query node.
Try taking Garth's post, and creating a custom report, and running that.
Standardize. Simplify. Automate. - OK, so Garth's post does indeed work in report format. Since the data is clearly in there, I'm sure it can be pulled in a Query as well. I want to be able to use my handy-dandy right-click tools on the results of the query, so a Report is not as useful to me.
Thanks - Anyone?
- I'm on holidays so I will not be able to look at this until late next week. But you should be able to find the data using WQL query.
http://www.enhansoft.com/ - Bump - I'd really like to be able to run this query...
Thanks again - Since as you can tell, neither Garth nor I have ever had a need for this information in a collection, perhaps we are asking the wrong question. Once you have this as a collection query, you indicated you wanted to use a right-click tool against the results.
I find that extremely interesting, because a right-click tool is traditionally (as far as I know) for invoking actions against currently powered -on devices... which considering your request is to find boxes that haven't sent in a heartbeat, that implies they simply don't exist to be touched with a right-click tool.
So, what problem, really, are you trying to solve? I'm sure you have one. And we've probably solved in via a different route than the one you are trying to attempt.
Standardize. Simplify. Automate. - You are correct, Sherry, most of the right-click tools are for machines that are powered on. I would indeed like to use this for these machines. If I have a machine on my LAN that hasn't sent in a heartbeat in a while, it would be nice to be able to just right-click and ping the workstation or connect to the CCM directory to check out the logs. Maybe it would be nice if it hasn't sent in any heartbeats in the last few weeks, and therefore no hardware or software scans, i can right-click and see who is currently logged onto the machine to see if I can take a look at it. I could also right-click on it and immediately open a Remote Desktop or Remote Assistance connection to troubleshoot. If there is nobody logged on right now, maybe I could use the right-click reboot tools and reboot it to see if that helps. Also, its much more convenient for me to use it in a query context for ease of changing the information I want to select along with last heartbeat and how quickly I can re-run the query. Its also easier to search for a string in the query interface.
While the report format gives me the information I want, if the data is able to be selected using a query, I would much rather do it that way. I am more comfortable with queries for information that doesn't need to be put into a pretty report interface. I find that in order to not be prompted for credentials every time I try to run a report, I have to first open the reporting interface in IE and then navigate to my report. It seems Firefox will not pass my Windows credentials automatically to SCCM's reporting interface.
The other thing I'm trying to resolve is the issue I've reported HERE and HERE but nobody seems to have an answer for other than to tell me to contact support. I have been told I need to completely exhaust my resources before we can call support, so thats what I'm trying to do. I would like to be able to run a query that selects the last heartbeat time, last hardware inventory and computer name of all machines in a particular workgroup. Our company often sends out a field laptop to an individual that does not make it through training, does not use their laptop at all and then sends the laptop back to us where it may sit on a shelf for several weeks. Obviously those machines are not going to send in a hardware inventory, but they also will not send back any heartbeats. I want to be able to weed those out of my "not working" list as machines that have pretty much never been turned on. I only want to look at machines that have sent back recent heartbeats meaning they're being turned on and being used and communicating, at least in some way, with the MP, but have not sent back a hardware inventory. I see no better way of determining this than to compare last heartbeat with last inventory date/time.
Thanks. - Bump
- I believe you can tell the powers that be in your organization that you have officially "exhausted all external (free) resources".
fyi, even if you were to get a Query to work, you could use it as a collection query, but you wouldn't see the custom columns. For various reasons (mostly to do with preventing you from inadvertently melting your server), the columns you see in a collection are the columns you get.
You could see them in the Query section of your console, but as far as I know, all of the currently available right-click tools only interface with Collections. Not queries. If you cared to try, you might be able to modify the .xml to attach to a Query node as well, but I don't believe anyone else has tried that yet. But then again... I'm not a right-click console tools person. Haven't used them in years.
Standardize. Simplify. Automate. - Sherry,
The right-click tools do indeed work for a query. In any event, I was finally able to resolve the problem without donating any more money into Microsoft's coffers. The problem was an invalid entry in the Internet Options -> Connections Tab -> LAN Settings -> "Advanced" manual bypass list area which caused the SCCM client to have a problem when it tried to read those settings. The problem is that everyone on this board was telling me that SCCM uses whatever proxy settings IE uses, but didn't tell me specifically that it uses the WinHTTP proxy settings defined by the proxycfg tool. What it seems that SCCM does is run a proxycfg -u command to import the settings in IE to the WinHTTP settings and use those. If there is an invalid entry or if the domain your ConfigMgr site is hosted on is not in this list, the clients will have trouble connecting. They do NOT read a proxy auto-config script as I was led to believe. They don't even look at it. As soon as I fixed the invalid entry (you can't have any drill-downs in the bypass list - everything must end at .com or .org or whatever) everything worked perfectly.- Marked As Answer by.Tim Harrison Thursday, November 05, 2009 1:29 PM
