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
Store

Report on node added Date

Hi guys

My boss would like to have a report with the Nodes that have been added the last couple of months.

I tried doing the report with the event node added, but that event is also triggered if you move a node from one poller to the other.

And since I have been moving a lot of nodes forth and back to load balance our pollers, that report got very long

Is there anywhere in the database recorded when a node first got added??

Regards Jens W

Find more posts tagged with

Accepted answers

shuth

I don't have a system with an additional polling engine on hand at the moment but I suggest looking at the AuditingEvents table and filter the ActionTypeID column for event ID '25' (Node Added). There is a different ID for changing polling engine (29).

Rough query like the one below. You'll need to add a date filter as the Auditing log has a 365 day retention by default. Caveat: Haven't tested this on a system with an additional poller.

SELECT TOLOCAL(ae.TimeLoggedUtc) AS [Date], ae.AccountID, ae.AuditEventMessage, n.Caption
FROM Orion.AuditingEvents ae
INNER JOIN Orion.Nodes n ON n.NodeID = ae.NetworkNode
WHERE ae.ActionTypeID = '25'
ORDER BY Date DESC

25 Orion.NodeAdded Node added

26 Orion.NodeDeleted Node deleted

27 Orion.NodeManaged Node managed

28 Orion.NodeUnmanaged Node unmanaged

29 Orion.NodePollingEngineMoved Node polling engine changed

30 Orion.NodeChanged Node edited

Update: 21/03/2019 - A slightly modified query in case your ActionTypeID's are different, you can filter the action by name.

SELECT TOLOCAL(ae.TimeLoggedUtc) AS [Date], ae.AccountID, ae.AuditEventMessage, ae.DisplayName
FROM Orion.AuditingEvents ae
WHERE ae.AuditingActionType.ActionType = 'Orion.NodeAdded'
ORDER BY Date DESC

All comments

mesverrum

Not specifically, but isn't the message different between a new node versus a node being moved to another poller? I think you could filter against the message text to exclude those moves. I've done reports for people before on Node added and didn't notice mine picking up moving them, but it's also possible I just wasn't looking for that use case.

Also something to be wary of when you are reporting on anything in the events is that by default they are only retained for 30 days, so you can't do "the last couple months" without extending that retention period.

jew

To be honest I was not sure so this morning i Added 2 devices (EDRC-ASA*) from our discovery result list, and moved 2 devices(KFA220-ASA*) from our primary poller(X0361p01 or 2) to our APE (X0361p03 or 4).

The events look identical to me, or is there anyway to see more details in the events?

Regards Jens

jew

But when you take and subtract the NodeIDs that has "Node removed" event in the same timeframe the result must be the real nodes added

Here is what i ended up with:

SELECT A.EventTime, A.NetworkNode, A.Message, B.Name, B.IPAddress, B.Department, B.endk_devicetype, B.endk_ELnetområde, B.endk_fysiskplacering

FROM

(

SELECT Added.EventTime, Added.NetworkNode, Added.EngineID, Added.Message

FROM

(

SELECT E.EventID, E.EventTime, E.NetworkNode, E.NetObjectID, E.NetObjectValue, E.EngineID, E.EventType, E.Message, E.Acknowledged, E.NetObjectType, E.TimeStamp

FROM Orion.Events E

WHERE E.EventType = 9 AND ADDMONTH(-1,GETUTCDATE())

)

AS Added

LEFT JOIN

(

SELECT E.EventID, E.EventTime, E.NetworkNode, E.NetObjectID, E.NetObjectValue, E.EngineID, E.EventType, E.Message, E.Acknowledged, E.NetObjectType, E.TimeStamp

FROM Orion.Events E

WHERE E.EventType = 8 AND ADDMONTH(-1,GETUTCDATE())

)

AS Removed

ON Added.NetworkNode = Removed.NetworkNode

WHERE Removed.NetworkNode IS NULL

ORDER BY Added.EventTime DESC

)

As A

JOIN

(

SELECT n.NodeID, n.IPAddress, n.Caption AS Name, c.Department, c.endk_devicetype, c.endk_ELnetområde, c.endk_fysiskplacering

FROM Orion.Nodes n

LEFT JOIN Orion.NodesCustomProperties c ON n.NodeID = c.NodeID

)

AS B

ON A.NetworkNode = B.NodeID

ORDER BY A.EventTime DESC

It is not easy to read but it is working

Regards Jens

shuth

Rough query like the one below. You'll need to add a date filter as the Auditing log has a 365 day retention by default. Caveat: Haven't tested this on a system with an additional poller.

SELECT TOLOCAL(ae.TimeLoggedUtc) AS [Date], ae.AccountID, ae.AuditEventMessage, n.Caption
FROM Orion.AuditingEvents ae
INNER JOIN Orion.Nodes n ON n.NodeID = ae.NetworkNode
WHERE ae.ActionTypeID = '25'
ORDER BY Date DESC

25 Orion.NodeAdded Node added

26 Orion.NodeDeleted Node deleted

27 Orion.NodeManaged Node managed

28 Orion.NodeUnmanaged Node unmanaged

29 Orion.NodePollingEngineMoved Node polling engine changed

30 Orion.NodeChanged Node edited

Update: 21/03/2019 - A slightly modified query in case your ActionTypeID's are different, you can filter the action by name.

SELECT TOLOCAL(ae.TimeLoggedUtc) AS [Date], ae.AccountID, ae.AuditEventMessage, ae.DisplayName
FROM Orion.AuditingEvents ae
WHERE ae.AuditingActionType.ActionType = 'Orion.NodeAdded'
ORDER BY Date DESC

mesverrum

I'll want to jump in and mention that those integers are specific to your environment based on the order of modules you installed and how long your system has been around, any time you want to report on them and have it work in another environment you need to join the table with the actual name strings and search against those.

jew

Hi thank you for that, seems that the Auditing events have a much better retention period than normal events, and I dont have to root out moves.

The results seems to be fine also with APE.

A retention period of 365 days is fine for me, I only need a few months of data

Regards Jens

kpmarcin

Hello Steven,

This is an amazing query!

I was trying to make something similar with only Discovery tables, but it was too difficult and messy. I did not think of checking the Audit table.

Thank you.

Kind regards,

Marcin.

robertcbrowning

Great idea. I was poking around in the nodes tables, I didn't think to check the audit tables. Perfect & the results go back as far as you've set its retention period.

kbswcon

Hi jew,

Can you provide the English schema translations for the following columns in your query?

B.endk_devicetype, B.endk_ELnetområde, B.endk_fysiskplacering

Thanks!