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
Parents Reply Children
No Data