Keeping track of decommissioned equipment

I use the message center for ad hoc queries, with the Node deleted audit event. You can of course put it into a report and schedule it also. 

Ok, I figured out a way to get the 7 day report...but still am having issues figuring out how to join a different table to get the node caption on the report.  Here is what I have so far...

Personally I find it easier to work this in SWQL and also it means you aren't directly interacting with the DB and possibly could cause a mistake.

Second, I think Orion.Events is probably a better table to query and you can query on EventType = '8' then. So, something like:

SELECT EventID, EventTime, NetworkNode, EngineID, EventType, Message

FROM Orion.Events

WHERE EventType = '8'
AND EventTime >= GETDATE()-7 

This includes the node name in the Message, though it isn't just the node name. If you prefer to have just the node name, then you can utilise an IMPLICIT JOIN to Orion.Nodes and do this:

SELECT EventID, EventTime, NetworkNode, EventType, e.Nodes.Caption, Message

FROM Orion.Events AS e

WHERE EventType = '8'
AND EventTime >= GETDATE()-7 

Personally I find it easier to work this in SWQL and also it means you aren't directly interacting with the DB and possibly could cause a mistake.

Second, I think Orion.Events is probably a better table to query and you can query on EventType = '8' then. So, something like:

SELECT EventID, EventTime, NetworkNode, EngineID, EventType, Message

FROM Orion.Events

WHERE EventType = '8'
AND EventTime >= GETDATE()-7 

This includes the node name in the Message, though it isn't just the node name. If you prefer to have just the node name, then you can utilise an IMPLICIT JOIN to Orion.Nodes and do this:

SELECT EventID, EventTime, NetworkNode, EventType, e.Nodes.Caption, Message

FROM Orion.Events AS e

WHERE EventType = '8'
AND EventTime >= GETDATE()-7