JOINs again - can't see where to JOIN.

Question

Because I still haven't got my head around JOINs I'm back here for some advice/help.

We have configured an 'alert' that essentially looks for and finds failed backup jobs. Ultimately this could just be a report but initial thoughts were to get it to alert on specific nodes failing to backup on schedule. In turn this would raise an incident. Either way, the logic we have so far is:

And this works quite nicely. The SWQL for this is:

SELECT E0.[Uri], E0.[DisplayName]
FROM Cirrus.Audit AS E0 
WHERE ( ( E0.[Action] = 'Download Config requested by job' ) 
AND ( E0.[DateTime] > '20210322 01:00:00' ) 
AND ( E0.[Type] = 'Failed' ) )

The problem is, we only get a swis:// as the result which looks like:  swis://SOL_Server/Orion/Cirrus.Audit/ID=A1DEA4FD-EFE4-4FFC-9268-0119A9305799

So I know (guess?) I need to do a JOIN to some other table to pull in the name. As I can't see any implicit joins from cirrus.audit, only I can't work out what to join ON.

Also, when looking at the Trigger Actions, I can't find/insert a variable for the Caption - presumably this is also because there is no direct correlation to the name of the node. Instead the eMail we get is just a URL that shows the NodeID.

So somewhere the system is translating A1DEA4FD-EFE4-4FFC-9268-0119A9305799 to a NodeID.

So a couple of questions.

1. What table is it dong that from

2.and how would I write out the JOIN to fit the above SWQL please?

stuartd · Answer

Ahh yes, you are right. But I was sure I found a way to get the name of the device extracted. I'll have to relook at this unless you know of a way that I can get the report as previously laid out?

micheal100 · Answer

Hi Stuart,

the AAT:126346 is the ID for an alert, not a node. Is it actually bringing you to a node view, or to the alert details view?

The detailsurl variable isn't expanding, so when you look at the alert details view does it have the node linked ?

As it stands there isn't a way to show the node caption, but the details field contains text including the IP of the device

Cannot Log into Device : bad password. Device IP: 10.199.6.31

But the ID field in the cirrus audit table is unique and there is nothing to join on that will show the caption.

stuartd · Answer

Michael,   hmmm... I understand the words but..!

My current resultant eMail looks like this:

So clearly there is a link somewhere to the NodeID (in this example 126346) and if I click the URL it takes me to the actual node. What I'm needing to understand is how to extract that into a report and I can't see how the backend is making that connection between the integer and the NodeId

Are you also saying that I can't JOIN another table in to get what I need; or that even if I do a JOIN it won't help me?

The end result we are after is that we'd like a report we can run that simply lists:

Node Name and optionally its IP - failed to do its startup/running backup.

Is this possible and if so, how? Please.