Version 2

    < 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