16 Replies Latest reply on Oct 14, 2019 8:39 AM by shuth

    Custom chart not showing all data from SWQL query

    shuth

      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?

       

      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
        • Re: Custom chart not showing all data from SWQL query
          adam.beedell

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

            • Re: Custom chart not showing all data from SWQL query
              shuth

              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.

                • Re: Custom chart not showing all data from SWQL query
                  adam.beedell

                  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?

                    • Re: Custom chart not showing all data from SWQL query
                      shuth

                      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

                       

                       

                      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
                • Re: Custom chart not showing all data from SWQL query
                  shuth

                  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.

                  • Re: Custom chart not showing all data from SWQL query
                    wluther

                    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