I have a custom chart showing the number of times particular alerts have triggered over the last 30 days. The original query I've copied from a post by marcnetterfield a long time ago but lost the source.
The problem is the chart doesn't line up with the output of the SWQL query in table form and I'm missing entries on the chart. In the screenshot below, the table and chart datasource is the same SWQL query. There are 8 unique alert names and the highlighted 4 are not shown on the chart. Of the alerts shown in the chart, the values match up exactly with the table excluding the missing alerts.
Edit: Pasting an image on thwack is painful. It resizes and distorts or blurs it into an unrecognisable mess. I've tried 5 different times to paste the below image in or used the Insert Image and it ends up worse each time.
So here is an imgur link: https://imgur.com/3QRqnla
Any thoughts of what I'm missing here? Bad SWQL as a datasource?
ah.alertobjects.AlertConfigurations.Name AS [Alert Name]
,COUNT(Message) as [Alert Count]
,DATETRUNC('day',Timestamp) as [Date]
FROM Orion.AlertHistory ah
where EventType = 0
and DAYDIFF(timestamp, GetDate()) < 30
and ah.alertobjects.AlertConfigurations.Name LIKE '%Sev%'
GROUP BY ah.alertobjects.AlertConfigurations.Name, DATETRUNC('day',timestamp)
ORDER BY ah.alertobjects.AlertConfigurations.name
I loaded it up in my environment, but noticed a different issue. All of my numbers appear to be correct, though they are all off by 1 day. (between the table and the chart)
Orion Platform 2018.4 HF3, SCM 1.1.0, NCM 7.9, NPM 12.4, DPAIM 11.1.1, VMAN 8.4.0, SAM 6.8.0, NetPath 1.1.4
Yep, just tested it by changing the chart to last 7 days with hourly intervals instead of daily. The raw timestamps are all midnight, but the chart is showing them in the late evening of the previous day.
You can fix the chart by wrapping the whole date section in a toutc() function, but you wouldn't want to do that on your tables because that makes them harder to make sense of.
Interesting find. The values in my chart matched up with the table info although that may have been luck. I tried changing the date section in TOUTC but that didn't change anything - still missing some entries in the chart and they weren't misplaced in the previous/next days.
Edit: I wonder if this server is set to UTC... I have no remote access to the server itself (only the web console).
I changed the interval to hourly and you can see the chart drops the line in at 11:00am, which matches the UTC+11 for the browsers current timezone.
If I change the SWQL to include a TOUTC function, the entry still appears at 11:00am.
This particular client is running on NPM 12.2. I went through the Orion Core platform release notes and found references to custom chart fixes in Orion 2018.2. It might be related to the first entry. I don't think it is top records as I bumped it up to 100 and it didn't resolve the issue.
00022780, 00032805, 1130747, 1125894
|Custom chart resources display correctly.|
|876639, 889594, 920136, 1102219, 1358660||Custom charts show only the number of top records configured in the Show only limited number of top records.|
NPM 12.2 is a couple of years old now. I'll advise the client to upgrade.
There are no view limitations on that view. I also tried it within a report and the same objects are missing. There are no account limitations on my account either (which is also full admin). I think if it were view/account limitations, it would also hide the result from the table.
I'm guessing something filtering-related.
Got anything that's rounding data up on the time settings on the chart? - In fact what does the custom chart settings look like in general?
Agreed - something is happening when the data is loaded into the chart. The thing that is confusing is the same data source/query works fine in a custom table (and custom query) resource.
Data source: the query in my original post
Time Period: Last 30 Days
Chart type: Stacked column
Data Series: Alert Count (under More > Time column: Date )
Group chart data by: Alert name
Legend shows: Alert name
Time Periods: Once a day
This is the output of a custom table using the same query on the same view. The 4 bolded rows don't show up in the chart.
|ALERT NAME||ALERT COUNT||DATE|
|VIC - Sev 3 - Node Down||1||11/09/2019 12:00:00 AM|
|VIC - Sev 2 - Node Down||2||13/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||9||13/09/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||2||13/09/2019 12:00:00 AM|
|VIC - Sev 1 - Node Down||1||13/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||4||14/09/2019 12:00:00 AM|
|VIC - Sev 1 - Node Down||3||15/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||1||15/09/2019 12:00:00 AM|
|WA - Sev 3 - Node Down||1||16/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||10||16/09/2019 12:00:00 AM|
|VIC - Sev 1 - Node Down||2||18/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||1||21/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||9||24/09/2019 12:00:00 AM|
|VIC - RCH - Sev 2 - Node Down||1||24/09/2019 12:00:00 AM|
|VIC - Sev 2 - Node Down||1||24/09/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||1||24/09/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||4||25/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||9||25/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||1||26/09/2019 12:00:00 AM|
|SA - Sev 1 - Node Down||2||27/09/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||2||27/09/2019 12:00:00 AM|
|VIC - Sev 1 - Node Down||2||30/09/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||4||2/10/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||7||2/10/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||8||3/10/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||10||3/10/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||46||4/10/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||93||4/10/2019 12:00:00 AM|
|WA - Sev 2 - Node Down||13||9/10/2019 12:00:00 AM|
|WA - Sev 1 - Node Down||40||9/10/2019 12:00:00 AM|
|VIC - Sev 1 - Node Down||9||9/10/2019 12:00:00 AM|
|VIC - Sev 2 - Node Down||4||9/10/2019 12:00:00 AM|
Just replicated in my environment and didn't notice an obvious gap and each reported alert name was present somewhere
Do your missing alerts show up on the chart with different sample intervals on the chart options?
If you do the same thing for the WHERE filter %Sev 2% does the missing Sev2 show up or is it still missing?
Tried changing the filter to %Sev 2% - still doesn't show the missing alert. I also tried changing to different intervals - no go there either.
With a filter for Sev 3, the chart has no data.
Shows up in the data source preview.
Are there alert limitations or account limitations on this account? I'd spin up a brand new local account with 0 limitations applied and verify.
* Oh i see you already checked your account for limitations, but maybe not alert limitations?
Yeah really a strange one here.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.