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

Discussion Filed Under:

Endpoint Management, Symantec Management Platform (Notification Server) - NS 7.x, Symantec Management Platform (Notification Server), Education, Mobile & Wireless, SQL, Windows 7, Reporting

Comments RSS Feed

Comments 5 Comments • Jump to latest comment

mclemson Trusted Advisor Partner Accredited

Last Configuration Request in Database - Comment:07 Feb 2012 : Link

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

Last Configuration Request in Database - Comment:08 Feb 2012 : Link

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 Trusted Advisor Partner Accredited

Last Configuration Request in Database - Comment:08 Feb 2012 : Link

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

Last Configuration Request in Database - Comment:08 Feb 2012 : Link

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 Trusted Advisor Partner Accredited

Last Configuration Request in Database - Comment:08 Feb 2012 : Link

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

Last Configuration Request in Database

Comments 5 Comments • Jump to latest comment

Would you like to reply?

Links