cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
MVP
MVP

Custom chart not showing all data from SWQL query

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

custom_chart_table.jpg

Any thoughts of what I'm missing here? Bad SWQL as a datasource?

SELECT
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
Labels (1)
0 Kudos
16 Replies

shuth
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 

pastedImage_0.png

0 Kudos

I wonder if the charting tool assumes the dates are in UTC and is applying a time zone shift when displaying them in the browser that pushes them to the previous day?

- Marc Netterfield, Github

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.

pastedImage_0.png

- Marc Netterfield, Github

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.

- Marc Netterfield, Github

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.

pastedImage_3.png

If I change the SWQL to include a TOUTC function, the entry still appears at 11:00am.

pastedImage_1.png

0 Kudos

good sleuthing!

0 Kudos
MVP
MVP

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, 1358660Custom 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.

0 Kudos

Well worth a shot - It's a weird one!

0 Kudos
Level 12

Is there any filter present on the view you're displaying the chart on?

0 Kudos

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.

0 Kudos

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?

0 Kudos

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.

Chart Config

Data source: the query in my original post

Time Period: Last 30 Days

Left Y-Axis

Units: Empty

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

pastedImage_5.png

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 NAMEALERT COUNTDATE
VIC - Sev 3 - Node Down111/09/2019 12:00:00 AM
VIC - Sev 2 - Node Down213/09/2019 12:00:00 AM
WA - Sev 1 - Node Down913/09/2019 12:00:00 AM
WA - Sev 2 - Node Down213/09/2019 12:00:00 AM
VIC - Sev 1 - Node Down113/09/2019 12:00:00 AM
WA - Sev 1 - Node Down414/09/2019 12:00:00 AM
VIC - Sev 1 - Node Down315/09/2019 12:00:00 AM
WA - Sev 1 - Node Down115/09/2019 12:00:00 AM
WA - Sev 3 - Node Down116/09/2019 12:00:00 AM
WA - Sev 1 - Node Down1016/09/2019 12:00:00 AM
VIC - Sev 1 - Node Down218/09/2019 12:00:00 AM
WA - Sev 1 - Node Down121/09/2019 12:00:00 AM
WA - Sev 1 - Node Down924/09/2019 12:00:00 AM
VIC - RCH - Sev 2 - Node Down124/09/2019 12:00:00 AM
VIC - Sev 2 - Node Down124/09/2019 12:00:00 AM
WA - Sev 2 - Node Down124/09/2019 12:00:00 AM
WA - Sev 2 - Node Down425/09/2019 12:00:00 AM
WA - Sev 1 - Node Down925/09/2019 12:00:00 AM
WA - Sev 1 - Node Down126/09/2019 12:00:00 AM
SA - Sev 1 - Node Down227/09/2019 12:00:00 AM
WA - Sev 1 - Node Down227/09/2019 12:00:00 AM
VIC - Sev 1 - Node Down230/09/2019 12:00:00 AM
WA - Sev 2 - Node Down42/10/2019 12:00:00 AM
WA - Sev 1 - Node Down72/10/2019 12:00:00 AM
WA - Sev 2 - Node Down83/10/2019 12:00:00 AM
WA - Sev 1 - Node Down103/10/2019 12:00:00 AM
WA - Sev 2 - Node Down464/10/2019 12:00:00 AM
WA - Sev 1 - Node Down934/10/2019 12:00:00 AM
WA - Sev 2 - Node Down139/10/2019 12:00:00 AM
WA - Sev 1 - Node Down409/10/2019 12:00:00 AM
VIC - Sev 1 - Node Down99/10/2019 12:00:00 AM
VIC - Sev 2 - Node Down49/10/2019 12:00:00 AM
0 Kudos

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?

0 Kudos

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.

pastedImage_0.png

With a filter for Sev 3, the chart has no data.

pastedImage_1.png

Shows up in the data source preview.

pastedImage_2.png

0 Kudos

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.

- Marc Netterfield, Github
0 Kudos

That is very weird! - I'm out of convenient ideas, but in a pinch you could send the data off to googlecharts API or something to regraph it that way?

0 Kudos