Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

Last Configuration Request in Database

Created: 07 Feb 2012 • Updated: 09 Feb 2012 | 5 comments
This issue has been solved. See solution.

I was looking at creating a report of computers that have their "Last Configuration Request" timestamp greater than 2 weeks ago. The trouble I am having is pinpointing where this is located in the database. I have found last Inventory request and other timestamps, but not this one.

StartTime in Evt_NS_Client_Config_Request does not seem to be the one I am after as the timestamps are different. Also ModifiedDate in ResourceUpdateSummary does not seem to be the one.

What I need is a timestamp of the time the client contacted the Notification Server so that I know when a notebook has gone missing.

The query I am using to match the timestamps is:

 SELECT Inv_Operating_System_Summary.Name, MAX(StartTime) as MaxRequestTime

FROM Evt_NS_Client_Config_Request 

INNER JOIN Inv_Operating_System_Summary ON (Evt_NS_Client_Config_Request.ResourceGuid = Inv_Operating_System_Summary._ResourceGuid)

GROUP BY Name

ORDER BY Name 

Comments 5 CommentsJump to latest comment

mclemson's picture

Correct table, bad query.

SELECT --Inv_Operating_System_Summary.Name, 
MAX(StartTime) as MaxRequestTime
FROM Evt_NS_Client_Config_Request 
--JOIN Inv_Operating_System_Summary ON (Evt_NS_Client_Config_Request.ResourceGuid = Inv_Operating_System_Summary._ResourceGuid)
--GROUP BY Name
--ORDER BY Name 
 
Works.  But your query does not.  It's likely because Inv_Operating_System_Summary is empty and therefore providing some NULL results.  Try the query above.
 
Or this one below:
SELECT vc.Name,
MAX(cr.StartTime) as MaxRequestTime
FROM Evt_NS_Client_Config_Request cr
JOIN vComputer vc ON vc.Guid=cr._ResourceGuid
GROUP BY vc.Name
ORDER BY vc.Name 
 
Does this help?

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

SOLUTION
StephenJoyce's picture

Thank you!

I did know about vComputer, but I had forgotten!

One modification is to use vComputer.ResourceGuid instead of _ResourceGuid.

I am not sure of what the underscored field refers to, or how to know which to use.

The correct query is thus:

SELECT vc.Name, MAX(cr.StartTime) as LastRequest
FROM Evt_NS_Client_Config_Request cr
JOIN vComputer vc ON vc.Guid=cr.ResourceGuid
GROUP BY vc.Name
ORDER BY vc.Name 
And to then find out which machines haven't reported for 14 days:
SELECT vc.Name,
MAX(cr.StartTime) as MaxRequestTime,
DATEDIFF(day,(MAX(cr.StartTime)),GETDATE()) as LastSeen
FROM Evt_NS_Client_Config_Request cr
JOIN vComputer vc ON vc.Guid=cr.ResourceGuid
GROUP BY vc.Name
HAVING DATEDIFF(day,(MAX(cr.StartTime)),GETDATE()) >= 14
ORDER BY LastSeen DESC
mclemson's picture

Built-in reports and filters use _ResourceGuid, _Location, _AssetGuid, etc.  This is the first time I've seen ResourceGuid, though Guid is used (in vComputer and vItem, for example).  I'd recommend _ResourceGuid, but I can't imagine they would be any different.

Does the information above solve your filter/report problem?

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com

StephenJoyce's picture

It actually is different. Using _ResourceGuid, I receive one result - the Notification Server. Using ResourceGuid I receive about a thousand results. I have matched the data from the latters results and it is indeed correct.

I am assuming the ResourceGuid and _ResourceGuid should be the same for each record, however I had a look at that table Evt_NS_Client_Config_Request, and _ResourceGuid is the same for each record, but the ResourceGuid is different.

Perhaps _ResourceGuid refers to the Notification Server, and not the client?

mclemson's picture

That must be it, where _ indicates a primary key, so to speak.  Good thinking!

Mike Clemson, Senior Systems Engineer, ASC
Intuitive Technology Group -- Symantec Platinum Partner
intuitivetech.com