Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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