Resource to display unmanaged information on the node page including user info

< BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST

End Result:

Capture.PNG

How To:

To achieve this just use the below SQL script to create Report in Report Writer and then use this report as a resource on node's page (if you run directly in Report Writer or SQL Management Studio - it will give you an error. When used as a resource on node page, Node ID variable will be supplied dynamically on line 82).

You can also download report file directly (attached). Just copy this file into your default folder with reports and report will automatically appear in your list. Then add it as a resource on your node page

Remember: Update SQL script with the correct database name as per your installation

Note: This will work with NPM 11.5+. If you want this to work with earlier versions you will need to update SQL script, the part where user ID is being extracted and change ID 28 to ID 27 (appear two times)

/* ============================================

Author: Alex Soul

Date: 13/05/2015

Description:

  * Information about unmnaged nodes.

  * Suitable for NPM 11.5. Earleir versions of NPM will require slight adjustemnt as Audit Event IDs are not the same. You will need to change ID from 28 to 27 for it to pick up user

  * To be used as an on-page report for "Report from Orion Writer" resource

Version:

  Number | Comments | Date | Author

  1.1 | Adopt for THWACK | 15/07/2015 | AS

--============================================ */

SELECT

      n.NodeID --hidden

     ,n.Caption --hidden

  ,CASE

      WHEN DATEDIFF(YEAR, n.UnManageFrom, getdate()) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(YEAR, n.UnManageFrom, getdate())) + ' years ago'

      ELSE CASE

   WHEN DATEDIFF(MONTH, n.UnManageFrom, getdate()) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(MONTH, n.UnManageFrom, getdate())) + ' month ago'

      ELSE CASE

      WHEN DATEDIFF(DAY, n.UnManageFrom, getdate()) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, n.UnManageFrom, getdate())) + ' days ago'

      ELSE CASE

      WHEN DATEDIFF(HOUR, n.UnManageFrom, getdate()) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, n.UnManageFrom, getdate())) + ' hours ago'

      ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, n.UnManageFrom, getdate())) + ' min ago'

      END

      END

      END

     END AS 'WHEN'

  ,CASE

      WHEN DATEDIFF(YEAR, n.UnManageFrom, n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(YEAR, n.UnManageFrom, n.UnManageUntil)) + ' years'

      ELSE CASE

   WHEN DATEDIFF(MONTH, n.UnManageFrom, n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(MONTH, n.UnManageFrom, n.UnManageUntil)) + ' month'

      ELSE CASE

      WHEN DATEDIFF(DAY, n.UnManageFrom, n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, n.UnManageFrom, n.UnManageUntil)) + ' days'

      ELSE CASE

      WHEN DATEDIFF(HOUR, n.UnManageFrom, n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, n.UnManageFrom, n.UnManageUntil)) + ' hours'

      ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, n.UnManageFrom, n.UnManageUntil)) + ' min'

      END

      END

      END

     END AS 'FOR'

  ,CASE

      WHEN DATEDIFF(YEAR, getdate(), n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(YEAR, getdate(), n.UnManageUntil)) + ' years'

      ELSE CASE

   WHEN DATEDIFF(MONTH, getdate(), n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(MONTH, getdate(), n.UnManageUntil)) + ' month'

      ELSE CASE

      WHEN DATEDIFF(DAY, getdate(), n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, getdate(), n.UnManageUntil)) + ' days'

      ELSE CASE

      WHEN DATEDIFF(HOUR, getdate(), n.UnManageUntil) > 3

      THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, getdate(), n.UnManageUntil)) + ' hours'

      ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, getdate(), n.UnManageUntil)) + ' min'

      END

      END

      END

     END AS 'REMAINS'

    ,n.UnManageFrom AS 'FROM'

  ,n.UnManageUntil AS 'UNTIL'

    ,r1.AccountID AS 'BY'

FROM Solarwinds.dbo.Nodes n WITH(NOLOCK)

--below two joints are joining last recorded "Node unmanaged" audit event (Event ID 27) for this node to be able to display who has unmanaged it

LEFT JOIN (

  SELECT NetObjectID, MAX([TimeLoggedUtc]) AS max_datetime

  FROM Solarwinds.dbo.AuditingEvents WITH(NOLOCK)

  WHERE ActionTypeID = '28'

  GROUP BY NetObjectID) MaxDates ON MaxDates.NetObjectID = n.NodeID

LEFT JOIN Solarwinds.dbo.AuditingEvents r1 WITH(NOLOCK) ON (r1.TimeLoggedUtc = MaxDates.max_datetime AND r1.NetObjectID = MaxDates.NetObjectID AND r1.ActionTypeID = '28')

WHERE

  n.NodeID = '${NodeID}' AND

  n.UnManaged = '1'

Have fun,

Alex Soul

www.pixace.com

attachments.zip
  • Which version of NPM you have installed?

  • Hmmm. Double-check that you have ID=28 set in lines 76 and 76. Is it possible someone is logged on as you?

    Would you do a test - un-manage node from different user account and check this report - what do you see? Still your user ID?

  • changed from ActionTypeID = '28' to '27' and all is well.

  • SQL schema has changed in the latest version. Glad you have worked it out the way it works for you. ... SQL scripting probably not best way in a long run as schema changes are beyond our control. SWQL is better, but SQL so much more user friendly emoticons_happy.png

  • Vey nice. It's a very useful piece of information to display. Thanks!

  • Sounds great, but we aren't getting results that make sense, as shown below...

    This Audit Action:

    User Test changed node TestNode property UnManageFrom from 11/2/2015 8:00:00 PM to 11/4/2015 9:49:00 AM

    User Test changed node TestNode property UnManageUntil from 11/3/2015 2:00:00 PM to 11/4/2015 2:00:00 PM

    Displays as, in the report:

    Caption          When               For               Remains          From                              Until                                   Account

    TestNode       -398 min ago     5 hours         11 hours          11/4/2015 5:49:00 PM     11/4/2015 10:00:00 PM         Test

  • Pretty nifty report!   Lots of work in that.

    I'm not a big fan of having "Report Writer" stuff on my pages, esp. Node pages where it gets referenced a lot though.   Not to mention I'm becoming a big fan of SWQL, so...  Here are a couple lighterweight versions of this report and resource that shouldn't tax your server quite as much.  Doesn't look nearly as neat, but should be quicker I would think.

    For just a report on all nodes that are currently unmanaged, when they went unmanaged and when they are scheduled to come off.   Who did the unmanaging, and how many days are left on it.   I figured just having days should be "good enough" for me, although how the report above adapts to times is pretty sweet...

    SELECT N.Caption, N.IP_Address, N.UnManageFrom AS [From], N.UnManageUntil AS [Until], AE.AccountID AS [By], DayDiff(GetUtcDate(), N.UnmanageUntil) AS [Days Left]

    FROM Orion.Nodes N JOIN Orion.AuditingEvents AE ON (AE.NetObjectID = N.NodeID)

    WHERE N.UnManaged = 1 AND (AE.AuditEventID  = (SELECT TOP 1 AE2.AuditEventID FROM Orion.AuditingEvents AE2 WHERE AE2.NetObjectID = N.NodeID ORDER BY AE2.TimeLoggedUtc DESC))

    ORDER BY N.Caption

    If you want it on an actual node page as a resource, that's pretty simple too, just have to stick in the ${NodeID} variable somewhere, and thanks to someone for letting me know that things like this might not work without explicitly setting the "ORDER BY" clause.  Take it out and you get an error!!   So, take this query and go to your "Node Details" page, click on "Customize", then hit the green "+" to add a resource to whatever column you want, make it "Custom Query", paste this in and there you go!

    SELECT N.UnManageFrom AS [From], N.UnManageUntil AS [Until], AE.AccountID AS [By], DayDiff(GetUtcDate(), N.UnmanageUntil) AS [Days Left]

    FROM Orion.Nodes N JOIN Orion.AuditingEvents AE ON (AE.NetObjectID = N.NodeID)

    WHERE (N.NodeID = ${NodeID}) AND (N.UnManaged = 1) AND (AE.AuditEventID  = (SELECT TOP 1 AE2.AuditEventID FROM Orion.AuditingEvents AE2 WHERE AE2.NetObjectID = N.NodeID ORDER BY AE2.TimeLoggedUtc DESC))

    ORDER BY AE.AccountID

    One thing I would love, maybe we can get Solarwinds to add this as a feature.  I'd love it if you could auto-hide this resource if the query returns 0 rows.  Then, rather than being on every node page, it would only be on those that were unmanaged.   cvachovecj

  • Another way to do it is to show the events on the node (if they arent already) set an event when its unmanaged and you will see it in the node.

    Im sure (without digging deeper that you should be able to do it in custom table as well?)

    pastedImage_0.png