Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Find When Node Was Added By Date

Is there a way to search the database and find what nodes were added in March (for example). Maybe a first discovered date or a first polled date, something like that?

Find more posts tagged with

Accepted answers

All comments

sum_giais

There's nothing concrete to do this natively. Here's how I get half the way there by using a SWQL query joining in the audit events data with nodes.

SELECT
     n.Caption
    ,CASE WHEN d.NetObjectID IS NOT NULL THEN TOSTRING(d.TimeAddedLocal)
          ELSE '1+ Year ago'
     END AS [Date Added to SolarWinds]
FROM Orion.Nodes AS n
LEFT JOIN ( SELECT
     TOLOCAL(ae.TimeLoggedUtc) AS [TimeAddedLocal]
    ,ae.NetObjectID
FROM Orion.AuditingEvents AS ae
WHERE ae.ActionTypeID = 25 AND ae.NetObjectType = 'N'
ORDER BY ae.TimeLoggedUtc DESC ) AS d ON d.NetObjectID = n.NodeID

This has an inherent problem of the audit event data rolling over after X amount of time (query assumes 365 day audit log retention).

If I were to really want a good record for this, I'd use a version of the query above to drop that data into a custom property via an alert with a set custom property value action assigned for permanent retention so it could be always referenced.

KMNRuser

Sum_giais, Thank you so much for the response! I am attempting to run with what you have provided. Would the next step then be to build out the alert that uses the action of "set custom Property value"? I am in the "Manage Alerts" section currently, trying to create the alert, but where it indicates "I want to alert on", I am not sure what to pick here between "Application, Component, Group, Interface, Node, Volume..below that section, there is a section entitled " Other Objects". I am just not sure based on your response what value to put here..

sum_giais

Hi there @KMNRuser ,

For Nodes here would be the rough process -- you'll have to branch out and figure out for other entities like interfaces, volumes, applications etc.

Note this is NOT tested explicitly as below, but I have used this method to populate custom data into custom properties via alerts.

The custom property would likely need to be a text custom property (unless you incorporate this as a script action somehow), not datetime because I don't think you can populate a datetime custom property with a SWQL variable in the change custom property action unless I missed something.

Pre-requisite: Create a text Custom Property for Nodes called "MyCustomPropertyName" or whatever you prefer but update everything below accordingly.

The below assumes a 24 hour update frequency, running twice daily during a single 11 minute window where the alert is enabled, adjust as needed...

1) Create new alert
2) On the Properties tab, set evaluation frequency to something like 5 minutes
3) On the Trigger Condition tab, set "I want to alert on:" to "Custom SWQL Alert (Advanced)", then set "Set up your SWQL condition" to "Node"
4) On the Reset Condition tab, set it to "Reset this alert automatically after" to 15 minutes

NOTE: This guarantees that the alert will only trigger once

5) Set the query to pull all data in for that period of time, adjusting the query for your custom property name

INNER JOIN ( SELECT
ae.TimeLoggedUtc
,ae.NetObjectID
FROM Orion.AuditingEvents AS ae
WHERE ae.ActionTypeID = 25 AND ae.NetObjectType = 'N'
ORDER BY ae.TimeLoggedUtc DESC ) AS d ON d.NetObjectID = Nodes.NodeID
WHERE ( Nodes.CustomProperties.MyCustomPropertyName IS NULL OR Nodes.CustomProperties.MyCustomPropertyName = '' )
AND HOURDIFF(d.TimeLoggedUtc, GETUTCDATE()) <= 24 )</pre>6) Time of Day, set "Specify time of day schedule for this alert", add a schedule to "Enable alert during following time period" for "Daily" frequency, on Business Days, set the time period below to desired timeframe (this is where the 15 minutes above is important). Set it to a 11 minute window, e.g. 5:00pm to 5:11pm.NOTE: This guarantees that the alert will evaluate twice daily (in case of errors first time)7) On the Trigger Actions tab, add a "Change Custom Property" action, change the "Custom Property Name" to your custom property "MyCustomPropertyName", and place this SWQL variable into the Custom Property Value
${N=SWQL;M=SELECT TOP 1 d.TimeLoggedUtc FROM Orion.Nodes AS Nodes FROM Orion.Nodes AS Nodes INNER JOIN ( SELECT ae.TimeLoggedUtc ,ae.NetObjectID FROM Orion.AuditingEvents AS ae WHERE ae.ActionTypeID = 25 AND ae.NetObjectType = 'N' ORDER BY ae.TimeLoggedUtc DESC ) AS d ON d.NetObjectID = Nodes.NodeID WHERE ( Nodes.CustomProperties.MyCustomPropertyName IS NULL OR Nodes.CustomProperties.MyCustomPropertyName = '' ) AND HOURDIFF(d.TimeLoggedUtc, GETUTCDATE()) <= 24 ) WHERE Nodes.NodeID = ${N=SwisEntity;M=NodeID} }</pre>Good luck...