Symantec Management Platform (Notification Server)

 View Only

  • 1.  Last Configuration Request in Database

    Posted Feb 07, 2012 08:05 PM

    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


  • 2.  RE: Last Configuration Request in Database
    Best Answer

    Posted Feb 08, 2012 01:31 AM

    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?


  • 3.  RE: Last Configuration Request in Database

    Posted Feb 08, 2012 04:15 PM

    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
    

    

                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    • 
                
                    
  • 
			                                
                                
                                
    
                                    
    
                                        
    
                                            
                                            4.  
                                            RE: Last Configuration Request in Database
                                            
                                            
                                            
                                        
    
                                    
    
                                    
    
                                        
    
				
                                                
    
                                                    
    
					
        
        
        
        
        
        
        
            
        
    
				
    

                                                
    
                                            
			
    
                                    
    
                                
    
                                
                                

                                
    
                                    
    
                                        
                                    
    

                                    
    
				

                                        
    
                                            
    
                                                
    
                                                    
    
                                                        Posted Feb 08, 2012 05:56 PM
                                                        
                                                    
    
                                                
    
                                            
    
                                            
    
                                                

                                                
                                            
    
                                        
    

                                        
    
                                            
    
                                                
    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?
    

                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    • 
                
                    
  • 
			                                
                                
                                
    
                                    
    
                                        
    
                                            
                                            5.  
                                            RE: Last Configuration Request in Database
                                            
                                            
                                            
                                        
    
                                    
    
                                    
    
                                        
    
				
                                                
    
                                                    
    
					
        
        
        
        
        
        
        
            
        
    
				
    

                                                
    
                                            
			
    
                                    
    
                                
    
                                
                                

                                
    
                                    
    
                                        
                                    
    

                                    
    
				

                                        
    
                                            
    
                                                
    
                                                    
    
                                                        Posted Feb 08, 2012 06:33 PM
                                                        
                                                    
    
                                                
    
                                            
    
                                            
    
                                                

                                                
                                            
    
                                        
    

                                        
    
                                            
    
                                                
    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?
    

                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    • 
                
                    
  • 
			                                
                                
                                
    
                                    
    
                                        
    
                                            
                                            6.  
                                            RE: Last Configuration Request in Database
                                            
                                            
                                            
                                        
    
                                    
    
                                    
    
                                        
    
				
                                                
    
                                                    
    
					
        
        
        
        
        
        
        
            
        
    
				
    

                                                
    
                                            
			
    
                                    
    
                                
    
                                
                                

                                
    
                                    
    
                                        
                                    
    

                                    
    
				

                                        
    
                                            
    
                                                
    
                                                    
    
                                                        Posted Feb 09, 2012 12:47 AM
                                                        
                                                    
    
                                                
    
                                            
    
                                            
    
                                                

                                                
                                            
    
                                        
    

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

                                                
    
					
                                                    

        
                                                
				
    
                                                
    
                                                    
                                                
    
                                                
                                            
    
                                        
    

                                        
                                        

                                    
			
    

                                    
                                
    

                                
                                
    
                                
    
                            
		
    •