This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report for Recently Edited Nodes

FormerMember
FormerMember

I'm trying to create a report that will send me a email each time a auditing event has occurred for all events EXCEPT when users are logging in.  I have a similar report already that I'm trying to tweak but I can't get it to work right.  I would need the report to generate a note each time a change to a node has been performed.  Including node adding / removal, interface adding / removal, managing / unmanaging nodes, and the user ID that performed the change.  Any ideas?

  • I suspect that "auditing event has occurred for all events EXCEPT when users are logging in" never happens because nobody is editing then.

  • FormerMember
    0 FormerMember in reply to JaroslawLadyga

    By not recording the user logins, I'm just saying on the report I don't need to see everytime a user logs in.  Report would need node added / removed, interface added / removed, managing / unmanaging nodes and the user that made the change.

  • Could you post the query or a snapshot of the report you have already? What exactly is not working quite right?

  • FormerMember
    0 FormerMember in reply to joe.tran

    So this report will generate but I can't select a time frame, need the last 24 hours, unless I write a report in Report Writer on the server, but I can't get the report to generate anything.  This report will give me what I need, I just need it to be for the past 24 hours only then I can schedule it to notify once a day:

    2015-06-05_15-52-40.png


  • I see what you mean. Looks like you'll need to do this with an Advanced Database Query. Here is a sample basic SQL query:

    SELECT AuditEventID

          ,TimeLoggedUtc

          ,AccountID

          ,ActionTypeID

          ,AuditEventMessage

          ,NetworkNode

          ,NetObjectID

          ,NetObjectType

      FROM AuditingEvents

      WHERE TimeLoggedUtc > DATEADD(d,-1,GETUTCDATE())

      AND ActionTypeID IN (39,40,42,27,24,25)

      AND AuditEventMessage IS NOT NULL

    The ActionTypeIDs should match up with the ones you have defined in your query builder.

  • FormerMember
    0 FormerMember in reply to joe.tran

    That looks like what I need! Thanks a bunch, just a FYI for myself cause my SQL is fuzzy and shaking the cobwebs out, in the DATEADD(d, -1 GETUTCDATE()) is the d, -1 the code showing the previous 24 hours changes?

  • It should. It subtracts one day from the current UTC datetime value.

    For example, if current UTC datetime is 2015-06-08 05:00:00.000, the DATEADD(d, -1, GETUTCDATE()) value will be 2015-06-07 05:00:00.000. Then the rest of the WHERE clause is just grabbing all audit events that happen between that datetime until the datetime the report is run/emailed.

  • FormerMember
    0 FormerMember in reply to joe.tran

    Great information joe,tran.  I really appreciate your help.  I've got the report schedule to kick off tonight for the first time and anticipate it should give me what I need based on tests I've already made.  Really appreciate it!  emoticons_happy.png