SWQL report - Details page link not available

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:

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

Does anyone have any ideas on how to solve this?

Find more posts tagged with

report help

SWQL

details page link

Accepted answers

mesverrum

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]

All comments

mesverrum

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]

csolberg

That's very helpful! Thanks mesverrum

csolberg

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?

jrouviere

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.

csolberg

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

jrouviere

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