8 Replies Latest reply on Jun 20, 2017 4:50 PM by mmatec01

    SWQL Query to select only records with latest timestamp

    mmatec01

      So I am trying to write an SWQL query that shows me all nodes that are unmanaged with some extra information tagged in, like Who unmanaged the node and When.  OK, I wrote a nice little SWQL query like this:

       

      SELECT DISTINCT N.Caption AS NodeCaption, N.ChildStatus AS ChildStatus, N.Status AS NodeStatus, ToLocal(N.UnmanageFrom) AS UnManageFrom, ToLocal(N.UnmanageUntil) AS UnManageUntil, ToLocal(AE.ObservationTimestamp) AS UnManagedWhen, AE.AccountID AS Who

      FROM Orion.Nodes N

      LEFT OUTER JOIN Orion.AuditingEvents AS AE

         ON AE.AuditEventMessage LIKE CONCAT ('% unmanaged node ', N.Caption, '.')

      INNER JOIN Orion.AuditingActionTypes AS AT

         ON AE.ActionTypeID = AT.ActionTypeID

      WHERE ((N.Unmanaged = 'True' OR N.UnmanageFrom >= GetUtcDate())

      AND AT.ActionTypeDisplayName LIKE '%Node unmanaged%')

      GROUP BY N.Caption, N.ChildStatus, N.Status, N.UnmanageFrom, N.UnmanageUntil, AE.ObservationTimestamp, Who

      ORDER BY N.Caption ASC, UnManagedWhen DESC

       

       

      That works great but I am getting multiple entries for nodes which have been managed/unmanaged multiple times in past.  OK, so I rewrote the query slightly like this;

       

      SELECT DISTINCT N.Caption AS NodeCaption, N.ChildStatus AS ChildStatus, N.Status AS NodeStatus, ToLocal(N.UnmanageFrom) AS UnManageFrom, ToLocal(N.UnmanageUntil) AS UnManageUntil, MAX(ToLocal(AE.ObservationTimestamp)) AS UnManagedWhen, AE.AccountID AS Who

      FROM Orion.Nodes N

      LEFT OUTER JOIN Orion.AuditingEvents AS AE

         ON AE.AuditEventMessage LIKE CONCAT ('% unmanaged node ', N.Caption, '.')

      INNER JOIN Orion.AuditingActionTypes AS AT

         ON AE.ActionTypeID = AT.ActionTypeID

      WHERE ((N.Unmanaged = 'True' OR N.UnmanageFrom >= GetUtcDate())

      AND AT.ActionTypeDisplayName LIKE '%Node unmanaged%')

      GROUP BY N.Caption, N.ChildStatus, N.Status, N.UnmanageFrom, N.UnmanageUntil, Who

      ORDER BY N.Caption ASC, UnManagedWhen DESC

       

      Well, that's pretty close to what I want but I still see that there are multiple entries for nodes which had had different users (AccountID) doing manage/unmanage.  If the user was the same, all duplicates disappear and I get what I want.   How do I further filter out results so I get the listing of all nodes that are currently unmanaged but I only get listing of the LATEST occurrence when this unmanaging happened and the user who did that.

      Thanks.

        • Re: SWQL Query to select only records with latest timestamp
          tdanner

          How about something like this?

           

          SELECT N.Caption AS NodeCaption, N.ChildStatus, N.Status AS NodeStatus, 
               TOLOCAL(N.UnManageFrom) AS UnManageFrom, TOLOCAL(N.UnManageUntil) AS UnManageUntil, 
               TOLOCAL(AE.ObservationTimestamp) AS UnManagedWhen, AE.AccountID AS Who
          FROM Orion.Nodes N
          LEFT JOIN Orion.AuditingEvents AE ON N.NodeID = AE.NetworkNode
          WHERE AE.AuditEventID = (
              SELECT TOP 1 A.AuditEventID
              FROM Orion.AuditingEvents A
              WHERE A.ActionTypeID=28 AND A.NetworkNode=N.NodeID
              ORDER BY A.TimeLoggedUtc DESC
          )
          ORDER BY N.Caption ASC, UnManagedWhen DESC
          
          1 of 1 people found this helpful
            • Re: SWQL Query to select only records with latest timestamp
              mmatec01

              Well, the SWQL query as such works but it comes back with 0 rows.  So there is something missing... debugging...

                • Re: SWQL Query to select only records with latest timestamp
                  mmatec01

                  Debugged and this works great.   Thank you!

                   

                  SELECT N.Caption AS NodeCaption, N.NodeID, N.ChildStatus, N.Status AS NodeStatus,  

                  TOLOCAL(N.UnManageFrom) AS UnManageFrom, TOLOCAL(N.UnManageUntil) AS UnManageUntil,  

                  TOLOCAL(AE.ObservationTimestamp) AS UnManagedWhen, AE.AccountID AS Who 

                  FROM Orion.Nodes N 

                  LEFT JOIN Orion.AuditingEvents AE ON N.NodeID = AE.NetworkNode 

                  WHERE (AE.AuditEventID = (SELECT TOP 1 A.AuditEventID 

                     FROM Orion.AuditingEvents A 

                     WHERE A.ActionTypeID=27 AND A.NetworkNode=N.NodeID 

                     ORDER BY A.TimeLoggedUtc DESC)

                     AND

                     (N.Unmanaged = 'True' OR N.UnmanageFrom >= GetUtcDate()))

                  ORDER BY N.Caption ASC, UnManagedWhen DESC

                    • Re: SWQL Query to select only records with latest timestamp
                      mesverrum

                      Worth pointing out here as I have seen this a few times in the past, it looks like the actiontypeid list changes around based on the versions and order that you installed the modules in since it looks like different modules have different actions available to them and they just get added to the list when they are installed.  These days I always end up navigating to the actions table and find the ones I need by name because I was getting burned by hard coding actionid's and not having them work when I went to my next client.

                        • Re: SWQL Query to select only records with latest timestamp
                          tdanner

                          Excellent point! Here's my query changed to refer to the audit event type by name instead of id.

                           

                          SELECT N.Caption AS NodeCaption, N.ChildStatus, N.Status AS NodeStatus,   
                               TOLOCAL(N.UnManageFrom) AS UnManageFrom, TOLOCAL(N.UnManageUntil) AS UnManageUntil,   
                               TOLOCAL(AE.ObservationTimestamp) AS UnManagedWhen, AE.AccountID AS Who  
                          FROM Orion.Nodes N  
                          LEFT JOIN Orion.AuditingEvents AE ON N.NodeID = AE.NetworkNode  
                          WHERE AE.AuditEventID = (  
                              SELECT TOP 1 A.AuditEventID  
                              FROM Orion.AuditingEvents A  
                              WHERE A.AuditingActionType.ActionType='Orion.NodeUnmanaged' AND A.NetworkNode=N.NodeID  
                              ORDER BY A.TimeLoggedUtc DESC  
                          )  
                          ORDER BY N.Caption ASC, UnManagedWhen DESC  
                          
                          1 of 1 people found this helpful
                            • Re: SWQL Query to select only records with latest timestamp
                              mmatec01

                              So that works great.  The only wrinkle is the fact that in Web Report Writer I am getting "*Query is not valid" error when I try to insert this SWQL statement as a selection.  It works great in SWQL Studio but it fails in Web Report Writer.  Just playing with it further it looks like the problem is the following clause in SELECT subquery (on line 9 above):

                               

                              AND A.NetworkNode=N.NodeID

                               

                              if you remove it then Web Report Writer permits SWQL query, no problem.  Of course without this construct the report is incomplete or empty and hence not useful.  Any idea why Web Report Writer has a problem with it?

                                • Re: SWQL Query to select only records with latest timestamp
                                  tdanner

                                  The web report writer modifies the query somewhat. Normally this does not affect the results, but it is getting confused by the correlated subquery in the where clause and producing a broken query. I came up with this as a workaround. It works in both SWQL Studio and the web report writer.

                                   

                                  SELECT N.Caption AS NodeCaption, N.ChildStatus, N.Status AS NodeStatus,     
                                       TOLOCAL(N.UnManageFrom) AS UnManageFrom, TOLOCAL(N.UnManageUntil) AS UnManageUntil,     
                                       TOLOCAL(AE.ObservationTimestamp) AS UnManagedWhen, AE.AccountID AS Who    
                                  FROM Orion.Nodes N    
                                  LEFT JOIN (
                                      SELECT A.NetworkNode, MAX(A.AuditEventID) AS AuditEventID
                                      FROM Orion.AuditingEvents A
                                      WHERE A.AuditingActionType.ActionType='Orion.NodeUnmanaged'
                                      GROUP BY A.NetworkNode
                                  ) AE1 ON N.NodeID = AE1.NetworkNode
                                  LEFT JOIN Orion.AuditingEvents AE ON AE1.AuditEventID=AE.AuditEventID
                                  ORDER BY N.Caption ASC, UnManagedWhen DESC
                                  
                                  1 of 1 people found this helpful