6 Replies Latest reply on Mar 10, 2017 3:26 PM by timt

    Custom Properties - How to audit for changes

    timt

      Hi,

       

      I have a question, and I can't seem to find it.  Is there a way to be able to audit a custom property field, to be able to run a report to see who changed the property and when?


      The reason is, we have setup a Mute_Node property, and would like to audit to see who muted it and when.

       

      Thanks much!

        • Re: Custom Properties - How to audit for changes
          timt

          I am able to get this out of SWQL, however, how can I link this to get the node name, instead of it just appearing in the message?

           

          SELECT o.TimeLoggedUtc, o.AccountID, o.ActionTypeID, o.AuditEventMessage, o.NetworkNode, o.NetObjectID, o.AuditingActionType.ActionTypeDisplayName

          FROM Orion.AuditingEvents o

          WHERE AuditEventMessage like '%Mute_Node%' and networkNode is not NULL and ActionTypeID = 30

          order by TimeLoggedUtc DESC

            • Re: Custom Properties - How to audit for changes
              tdanner

              The NetworkNode property is the NodeID value, so you can join to the Orion.Nodes entity to get other node properties. Like this:

               

              SELECT o.TimeLoggedUtc, o.AccountID, o.ActionTypeID, o.AuditEventMessage, o.NetworkNode, n.DisplayName, o.NetObjectID, o.AuditingActionType.ActionTypeDisplayName
              FROM Orion.AuditingEvents o
              LEFT JOIN Orion.Nodes n ON o.NetworkNode=n.NodeID
              WHERE AuditEventMessage like '%Mute_Node%' and NetworkNode is not NULL and ActionTypeID = 30
              order by TimeLoggedUtc DESC
              
                • Re: Custom Properties - How to audit for changes
                  timt

                  Thanks TDanner, you're a genus!

                   

                  Do you know why when I link a custom property table that it shows multiple duplicates?

                   

                   

                  SELECT o.TimeLoggedUtc, o.AccountID, o.ActionTypeID, o.AuditEventMessage, o.NetworkNode, n.DisplayName, o.AuditingActionType.ActionTypeDisplayName, cp.OS_Environment 

                  FROM Orion.AuditingEvents o 

                  left JOIN Orion.Nodes n ON o.NetworkNode=n.NodeID 

                  right join orion.nodescustomproperties cp on o.networknode=n.nodeid

                  WHERE AuditEventMessage like '%Mute_Node%' and NetworkNode is not NULL and ActionTypeID = 30 

                  order by TimeLoggedUtc DESC 

                   

                    • Re: Custom Properties - How to audit for changes
                      tdanner

                      You are getting result set multiplication because of how the ON clause in your join to Orion.NodesCustomProperties is written:

                       

                      right join orion.nodescustomproperties cp on o.networknode=n.nodeid
                      

                       

                      Notice how the ON expression does not mention "cp"? This means that every row in Orion.NodesCustomProperties "matches" every row in the rest of the result set. You probably meant to say "o.networknode=cp.nodeid".

                       

                      Another note: even with the ON clause fixed, you are using a RIGHT JOIN, which may not be what you want here. RIGHT JOIN will include every row in Orion.NodesCustomProperties regardless of whether there is a corresponding audit event.

                      1 of 1 people found this helpful
                        • Re: Custom Properties - How to audit for changes
                          timt

                          Ahh I get it!  Thanks for your support, it's been very .. enlightening!

                           

                          SELECT o.TimeLoggedUtc, o.AccountID, o.AuditEventMessage, n.DisplayName, o.AuditingActionType.ActionTypeDisplayName, cp.OS_Environment, cp.os_admin, cp.site_name

                          FROM Orion.AuditingEvents o 

                          left JOIN Orion.Nodes n ON o.NetworkNode=n.NodeID 

                          left join orion.nodescustomproperties cp ON o.networknode=cp.nodeid

                          WHERE AuditEventMessage like '%Mute_Node%' and NetworkNode is not NULL and ActionTypeID = 30 

                          order by TimeLoggedUtc DESC