2020.2.5 breaks some modern dashboard queries

Hi there!

We're using a swql query to list all active volume alerts and some of the relevant details of those alerts. This query still works in swql studio, as well as in the old dashboard (custom table), but the table widget in our modern dashboard is now empty (query broken after the update to 2020.2.5). 

Here's the query in question:

SELECT o.AlertConfigurations.Name AS [ALERT NAME],
    '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME],
    CASE WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'
        WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'
        WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'
        WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'
        WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'
    END AS [_IconFor_ALERT NAME],
    o.EntityCaption AS [ALERT OBJECT] ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT],
    CASE WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'
        WHEN o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption 
    END AS [RELATED NODE],
    o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE],
    ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME],
    '/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE],
    CASE WHEN minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440
            THEN (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days') 
        WHEN minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 
            THEN (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours') 
        ELSE (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes') 
    END AS [Time Active] ,aa.AcknowledgedBy,
    ah.Message AS [Note],
    round(v.VolumePercentUsed,2) AS [PERCENT USED],
    round(v.VolumeSpaceAvailable/1024/1024/1024,2) AS [GB FREE]
    FROM Orion.AlertActive aa join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    LEFT JOIN Orion.Nodes p on p.nodeid=relatednodeid 
    LEFT JOIN Orion.Volumes v ON v.VolumeID=Substring(EntityNetObjectId, 3, 20)
    LEFT JOIN orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3) 
    WHERE o.EntityNetObjectId LIKE 'V:%'

It seems that o.AlertConfigurations.Name no longer works, when using modern dashboard. To me, this feels like a bug.

Does this work for anyone else? 

Kind regards

Ariano

  • Update: I started a case to get this issue reviewed. 

    Here is a shorter version of the query, just to reproduce the issue:

    SELECT o.AlertConfigurations.Name
    FROM Orion.AlertActive aa join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    WHERE o.EntityNetObjectId LIKE 'V:%'

    This works in 2020.2.4 (old and new dashboard/custom table widget) and in the old dashboard in 2020.2.5.

    It also works in both versions using swql studio.

    Using 2020.2.5 and a modern dashboard leads to empty widgets and error messages ("exceptionMessage": "RunQuery failed, check fault information.\nno viable alternative at input 'NAME' in Select clause",).

  • I can't get that query to work in 2020.2.4 either. BUT, doing the joins manually works. 

    SELECT
      --o.AlertConfigurations.Name,
      AC.Name
    FROM Orion.AlertActive aa
    INNER join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    INNER JOIN Orion.AlertConfigurations AS AC ON O.Alertid=AC.Alertid
    WHERE o.EntityNetObjectId LIKE 'V:%'
  • Interesting. For me, both versions work just fine in swql studio and the old dashboard, but the modern dashboard still breaks:

    "message": "An error has occurred.",
    "exceptionMessage": "RunQuery failed, check fault information.\nno viable alternative at input 'NAME' in Select clause", ...

    I do have a second installation, still running 2020.2.4 and i can confirm the original query works there in the old and the new dashboard.

  • Had to test in another 2020.2.4 - did not work. Validate query shows green but actually running the query results in error.

    The the manual join and it will work.

  • Thank you for trying! To clarify: when I said "both versions" in my response to you, I meant the manual join as you mentioned. This works fine in 2020.2.4 (as does the o.AlertConfigurations.Name - for me anyways), but in 2020.2.5 it also breaks the modern widget (same error message as before). I'm curious what the official response will be...

  • So this:

    SELECT
    --o.AlertConfigurations.Name,
    AC.Name
    FROM Orion.AlertActive aa
    INNER join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
    INNER JOIN Orion.AlertConfigurations AS AC ON O.Alertid=AC.Alertid
    WHERE o.EntityNetObjectId LIKE 'V:%'

    does not work for you in 2020.2.5? It does work for me.

  • I made the mistake to test with the modified complete query, which did not work in the modern dashboard, while it does in swql studio and the old dashboard. 

    The short version on its own works, as it should. Good! But now for the strange part: I slightly reduced the query to this:

    SELECT ac.Name AS [ALERT NAME],
        o.EntityCaption AS [ALERT OBJECT] ,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT],
        CASE WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self' 
            WHEN o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption 
        END AS [RELATED NODE],
        o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE],
        ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME],
        CASE WHEN minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 
                THEN (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days') 
            WHEN minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 
                THEN (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours') 
            ELSE (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')
        END AS [Time Active],
        aa.AcknowledgedBy, 
        ah.Message AS [Note],
        round(v.VolumeSize/1024/1024/1024,2) AS [VolSize],
        round(v.VolumePercentUsed,2) AS [PERCENT USED],
        round(v.VolumeSpaceAvailable/1024/1024/1024,2) AS [GB FREE]
    FROM Orion.AlertActive aa join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid
        LEFT JOIN Orion.Nodes p on p.nodeid=relatednodeid 
        LEFT JOIN Orion.Volumes v ON v.VolumeID=Substring(EntityNetObjectId, 3, 20)
        LEFT JOIN orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3) 
        INNER JOIN Orion.AlertConfigurations AS ac ON O.Alertid=AC.Alertid
    WHERE o.EntityNetObjectId LIKE 'V:%'

    Now it not only validates correctly, but also shows results when testing the query within the new dashboard.

    I can see the correct results and I can save the query and setup the widget, but saving the widget settings still results in the same error message and the widget remains empty. Disappointed

    Edit: now I removed everything related to AlertConfigurations (inluding the join) to see what happens and it still breaks in the modern dashboard, while it still works fine in swql studio.

    Edit 2: I also tried adding a new widget to a new dashboard and using the modified query there. Same result, sadly.

    I think i will leave it at that, for the time being. I found some other broken table widgets on a different dashboard. Those are related to SRM Objects (File Shares, NAS Volumes). I don't think this is related to the queries themselves, since they all work in 2020.2.4 and still do in 2020.2.5 when I check them using swql studio. 

  • Hi Xaturbatu!

    Thanks for trying to figure it out! Sadly, this doesn't work either. Don't get me wrong: the query looks correct and it works fine in swql studio (as expected). It just does not work in a table widget, on a modern dashboard running on 2020.2.5. At least for me, it doesn't...

    I can create the widget, validate and preview results, but upon saving, it still throws errors and does not show results, so this is the same result as with the previous tests above.

    Once the Solarwinds support has responded to the case, I will update this thread to keep you informed.

  • Now I realized that it only works when configuring the widget and pressing "Show Records", but not when saving it. Looks like the table has an issue with spaces in the column name like [ALERT NAME]. Remove all spaces from column names and change the column label with the space after saving the query. 

    That was my first (or second?) try, but it didn't work either.

    I also tried creating a new dashboard, adding new widget and auto-populate the field names when creating the widget. Same result. This was when I started removing parts of the query to see if simpler queries would work. I then found other widgets on different dashboards (different queries, related to other objects), that also failed the same way. 

  • Meanwhile I tried the reduced query (without icons and aa.AlertObjects.AlertConfigurations.Name instead of o.AlertConfigurations.Name), combined with altered names (replaced spaces with _).

    This seems to work, but it now shows two new and different problems. First I thought that it's no longer possible to reorder columns in the widget setup, but that turned out to be a visual bug. Watching the preview shows the reordering to work, while the names in the right hand column revert to the previous setting. Strange.

    More problematic: I can no longer sort by aa.TriggeredDateTime. I originally had the query sort the output (ORDER BY aa.TriggeredDateTime DESC). The 2020.2.5 update introduced sorting as widget configuration, so i tried that, but the issue get more complicated when I do that, because I had the query modify the timestamp to a more readable format and sorting that field does not work correctly. I then added aa.TriggeredDateTime as a separate value and sorted on that field, but there is no option to hide that row from the widget...

    I dont think I like this update so far and while one can rebuild the query to fit the new widgets requirements, I really think that should not be necessary. There's to much weird behaviour and everything works unmodified in swql studio as well as in the old dashboards custom tables/report datasources.

    At this point, I'm waiting on the official Solarwinds response (the case ist still in the "not started" state) and I think I will stop debugging now.

    Thanks so far! I'll keep the thread updated, once I get a response from Solarwinds.