5 Replies Latest reply on Apr 8, 2016 9:42 AM by xtraspecialj

    Custom Chart "Filter results" options missing when using a SWQL/SQL Query as Data Source

    xtraspecialj

      Just like the title says, when using a SQL or SWQL query as your data source for a Custom Chart, there is no "Filter results" option (see the two comparison screenshots at the bottom of this post).  If that is intentional then I kind of get it, in that the filters should be defined within the query, however, for certain types of custom queries its not possible to do a "SELECT TOP" statement due to the Timestamp field having to be selected. 

       

      For example, I want to visualize how many Events of each type we are getting per hour.  I tried to do that by using the GUI selector in the Data Source, but there is no Event Count field to select and you can't choose to aggregate a field after selecting it like you can when doing a Custom Table (why is this missing from Custom Charts??).  So, I thought "no big deal" and I just wrote a custom SWQL query here:

       

      SELECT
      COUNT(e.EventID) AS EventCount,
      et.Name AS EventTypeName,
      e.EventTime AS Timestamp
      FROM Orion.Events e
      JOIN Orion.EventTypes et ON e.EventType = et.EventType
      WHERE e.EventTime >= ${fromtime} AND e.EventTime <= ${totime}
      GROUP BY et.Name, e.EventTime
      ORDER BY EventCount DESC
      

       

       

      Well, the problem with that is if I do a SELECT TOP 5, I'll just get 5 Events on the Chart...  Next I decided to just do a sub-select statement with the COUNT statement in it:

       

      SELECT
      TOP 5
      cnt.EventCount,
      cnt.EventTypeName,
      cnt.Timestamp
      FROM(
      SELECT
      COUNT(e.EventID) AS EventCount,
      et.Name AS EventTypeName,
      e.EventTime AS Timestamp
      FROM Orion.Events e
      JOIN Orion.EventTypes et ON e.EventType = et.EventType
      WHERE e.EventTime >= ${fromtime} AND e.EventTime <= ${totime}
      GROUP BY et.Name, e.EventTime
      ) AS cnt
      ORDER BY cnt.EventCount DESC
      

       

      The problem with that is the Timestamp field having to be in the main SELECT statement (otherwise the chart has nothing to summarize data with) makes the SELECT TOP 5 function not work appropriately (run the query in your SWQL Studio or in a Custom Chart and you'll see what I mean).  If I could just put the first query I listed above into a Data Source in the Custom Chart Resource, point the ObjectID and Caption column to the EventTypeName column, then choose to show the Top 5 Records like the way I can when using a regular Data Source, this would be simple, but the way it is now I can't figure out a way to make this work the way I expect.  Currently I'm just using the first query above and letting the chart series limitation keep it from displaying more than 10 Event Types, but I don't want 10, I want 5.

       

      Does anyone know a better way to do this that'll let me choose the Top 5 Event Types?

       

       

      When using a custom SQL/SWQL Query:

       

       

      When Using a GUI Condition Statement as my Data Source: