cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 8

SWQL report - Details page link not available

Jump to solution

Hi,

I am creating a report that displays repetitive alerts related to bandwidth usage on interfaces. I wish to include a details page link on the 'Trigger object' (the interface).

Interface bandwidth report.png

I am using EntityDetailsUrl from Orion.AlertObjects. And the SWQL query looks like this:

"SELECT alertconfig.Name   

,COUNT (alerthist.message) AS [Alert Count]

,alertobj.EntityCaption AS [Trigger Object]

,alertObj.EntityDetailsUrl AS DetailsUrl

,tolocal(max(alerthist.TimeStamp)) AS [Most Recent Trigger],

node.Caption AS [Node],

......."

However, when I try to add the display settings, I am not able to choose details page link:

pastedImage_3.png

I also tried to add the display settings to the node column, but it gives me the following message:

pastedImage_4.png

Does anyone have any ideas on how to solve this?

0 Kudos
1 Solution

Accepted Solutions
Highlighted

Re: SWQL report - Details page link not available

Jump to solution

Basically those links don't reliably work when using custom SQL/SWQL datasources.  You can "kind of" get them to work through various tricks but  honestly I find that it becomes a huge time sink to try and get the format to match what it is looking for and once you do it still only lets you generate details link for a single column, which is kind of an annoying limitation.

What works better and is more universal is just to build the html for the link yourself and check the box for Allow HTML Tags in the table editor.

So for your example I'd do this

,'<a href="' + alertObj.EntityDetailsUrl + '">' + alertobj.EntityCaption + '</a>' AS [Trigger Object]

- Marc Netterfield, Github

View solution in original post

9 Replies
Highlighted

Re: SWQL report - Details page link not available

Jump to solution

Basically those links don't reliably work when using custom SQL/SWQL datasources.  You can "kind of" get them to work through various tricks but  honestly I find that it becomes a huge time sink to try and get the format to match what it is looking for and once you do it still only lets you generate details link for a single column, which is kind of an annoying limitation.

What works better and is more universal is just to build the html for the link yourself and check the box for Allow HTML Tags in the table editor.

So for your example I'd do this

,'<a href="' + alertObj.EntityDetailsUrl + '">' + alertobj.EntityCaption + '</a>' AS [Trigger Object]

- Marc Netterfield, Github

View solution in original post

Highlighted
Level 8

Re: SWQL report - Details page link not available

Jump to solution

That's very helpful! Thanks mesverrum

0 Kudos
Highlighted
Level 8

Re: SWQL report - Details page link not available

Jump to solution

I'm having some syntax problems (I'm not very familiar with HTML), and cannot get this to compile. Is there any documentation available which helps explain how to use HTML with SWQL?

0 Kudos
Highlighted
Level 14

Re: SWQL report - Details page link not available

Jump to solution

I don't think there's any official documentation, there's a lot of examples on Thwack from various customers providing their own customizations.

Do you have an example of your query that's causing you issues? We can take a look at your syntax and suggest from there.

0 Kudos
Highlighted
Level 8

Re: SWQL report - Details page link not available

Jump to solution

My original query looks like this:

"SELECT alertconfig.Name   

,COUNT (alerthist.message) AS [AlertCount]

,alertobj.EntityCaption AS [TriggerObject]

,tolocal(max(alerthist.TimeStamp)) AS [Most Recent Trigger],

node.Caption AS [Node],

node.CustomProperties.BusinessService,

node.CustomProperties.BusinessSubService,

node.CustomProperties.Environment,

alertObj.EntityDetailsUrl AS DetailsUrl

FROM Orion.AlertHistory alerthist 

LEFT JOIN Orion.AlertObjects alertobj ON alertobj.alertobjectid=alerthist.alertobjectid 

LEFT JOIN Orion.AlertConfigurations alertconfig ON alertconfig.alertid=alertobj.alertid 

LEFT JOIN Orion.Actions action ON action.actionid=alerthist.actionid 

LEFT JOIN Orion.Nodes node ON node.nodeid=RelatedNodeID 

WHERE  

DAYDIFF(alerthist.timestamp,GETUTCDATE())<7 

AND eventtype=0 

AND alertobj.EntityType LIKE 'Orion.NPM.Interfaces'

AND alertconfig.Name like '%bandwidth%'

AND node.CustomProperties.CustomerID LIKE 'A00'

GROUP BY name,  [TriggerObject], RelatedNodeCaption 

HAVING COUNT(alerthist.message) > 4

ORDER BY [AlertCount] DESC"

which runs as I expect it to. But when I try to replace the third line with the line ",'<a href="' + alertObj.EntityDetailsUrl + '">' + alertobj.EntityCaption + '</a>' AS [TriggerObject]". It states that it cannot resolve property TriggerObject. Do you have any idea why?

Like I said, I'm not too familiar with the syntax, so the query is probably not ideal, but I'm tring

Highlighted
Level 14

Re: SWQL report - Details page link not available

Jump to solution

OK, I see it now. I don't know if mseverrum has a tip for this, but sometimes I find that the group by doesn't honor a custom named field.

So in this example your issue is in the Group By line, you can verify this by removing [TriggerObject], from that line and your query will work.

You should be able to change that line to the below and get the desired results:

GROUP BY name,  alertobj.EntityCaption, RelatedNodeCaption

Highlighted
Level 8

Re: SWQL report - Details page link not available

Jump to solution

It works! Thank you jrouviere​!

Highlighted

Re: SWQL report - Details page link not available

Jump to solution

Thats due to the order of operations in SQL, despite being at the top of the code the select statement is actually one of the last things that SQL runs so that rename hasn't happened when the group by code runs.

- Marc Netterfield, Github
Highlighted
Level 14

Re: SWQL report - Details page link not available

Jump to solution

Makes some sense, but with the same query and the only difference being the HTML addition I'm not sure why it should work one way and not the other.