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.

SAM - SQL User Experience Monitor to show when nodes are deleted

FormerMember
FormerMember

Hi All,

I am looking at a way of tracking deletions to the dbo.nodes table and have tried this by using a SQL User Experience Monitor and set the critical statistic threshold to any value less than 0, but the difference is not returning a negative integer value when a node is deleted.

Here is a screen shot of my application monitor:

SAM SQL UX Mon.PNG

The SQL query "SELECT COUNT (*) FROM dbo.Nodes" returns an integer value. I have copied the UX Monitor and configured this not to count the statistic as a difference to confirm that the value returned increase or decreases.

I poll, note the value e.g. 23 and difference = 0 then add a node and poll (values return 24 & 1). I poll again and the values return are 24 & 0 - no difference between 2 polls as expected.

I remove the node, poll again and the values return 23 & 0. This I would expect to return as -1 and then trigger the critical threshold but it doesn't.

Has anyone else tried to achieve this and if they have, is there a better way of doing this?

Many thanks

Garreth

  • I have not set this up, but perhaps an easier way would be to count the number of entries in the dbo.DeletedNodes table?   The only time there is an entry is when a node is deleted, and before the SolarWinds maintenance run, which will clean it out.  That way - any 'count' value greater than 0 triggers.

    Curious - what do you want to do with this information, once you find it?  Either method will not be historical in nature, so you will need some other way of tracking.

    Note, this will only yield you a nodeid, but I do believe if there are interfaces and volume detail data, that will remain until the clean up runs.

  • FormerMember
    0 FormerMember in reply to rstoney00

    Hi rstoney00,

    Thanks for the reply, I will amend the monitor to query the dbo.DeletedNodes table and see if this does what is required. A customer has reported an issue where nodes have "disappeared" from NPM with no audit trail and so we are looking at a way to identify when the nodes table changes and figured a UX Monitor would do the trick. It may not report which node is missing, but at least then the customer knows a node has been removed and can query node table live and from a backup. We could tie this in with a daily report listing all nodes allowing them to use a diff tool to list differences.

  • Count as difference makes negative difference zero. To workaround this limitation you can try negate statistic value returned from query in order to get positive difference and call this value "number of removed nodes". You can also use second "message" field to get also info about current absolute node count. I also recommend adding NOLOCK hint to avoid unnecessary blocking:

    SELECT -COUNT(*), 'Current node count: ' + CONVERT(varchar(20), COUNT(*)) FROM dbo.Nodes WITH (NOLOCK)

  • Out-of-the-box SAM provides audit events when nodes are added or deleted. This may be a simpler approach to tracking this information. This can be used in Alerts as well as in reports.

    Node Audit Events.png

  • Alter - OP was looking at possibility that the audit trail you show was NOT being populated out when the node was removed somehow.  Tis why we were not going that route automatically.  emoticons_grin.png

    Now...should that prove true:   A node is deleted and there is no audit trail, would start looking at 1)Other ways nodes might be removed.  aka - someone being slick with the sql tables or   2)Some reason the audit trail is not being populated correctly.  aka - time to open support ticket.