Time series charts in QRadar Pulse

At first glance, creating a time series chart from relational data in QRadar® Pulse can be challenging. The amount of data that is returned by an AQL query can be unwieldy, but with some background knowledge and careful planning, you can produce relevant and meaningful time series charts.

Note: After you read about time series charts, create a dynamic time series chart by following the procedure in Tracking the top five most active devices in the last ten minutes. In QRadar Pulse V2.1.4 or later, the time series chart has a dynamic series option that is useful when you don't know which devices you want to track, or find it difficult to make time series charts work properly. It automatically detects series and displays them as separate lines on the time series chart.
Ordering a metric by starttime does yield a time series, like the following AQL query, but the amount of returned data can be unwieldy to work with and understand.
select starttime as 'Start Time',
SUM(eventcount) as 'Event Count (Sum)'
from events
where eventcount <> NULL
GROUP BY starttime
order by starttime
LAST 60 minutes
The amount of returned data can result in a noisy chart that doesn't provide much information. The event start times might not occur at regular intervals, which can create gaps in the data set.
Figure 1. Too much data results in noisy results
Noisy chart with large volumes of data

Creating intervals in a time series query

The first step is to decide the type of interval to use for your data analysis. Do you want to look at data from a narrow interval, such as every second? Or in larger intervals of every minute or every hour? This decision is important because data points must be grouped in intervals so that a metric is calculated.

Using the original query, you can group the data by using one of the following techniques:
  • Using the GROUP BY clause to create an interval: GROUP BY starttime/60000. Because starttime is in displayed in milliseconds, if you divide by 60,000 (60 seconds x 1000 milliseconds), you create groups or intervals of 60 seconds (1 minute).
  • Changing the ORDER BY clause to use the aggregate sTime.
The query changes to the following code:
select starttime as 'sTime',
SUM(eventcount) as 'Event Count (Sum)'
from events
where eventcount > 0
GROUP BY starttime/60000
order by "sTime"
LAST 60 minutes
The data is now more visually consumable and ensures that one data point occurs every minute. Now that the intervals are properly defined, you can use a few strategies to format the data into a more friendly time series format.
Figure 2. More consumable data results with properly defined intervals
Data is consumable but needs refinement

Pivoting rows to columns to create a series

One method for creating a time series chart involves pivoting the rows of data into columns that directly represent a series in Pulse. For example, if your use case is to count events for a specific device for each 1-minute interval, you must create a separate conditional aggregate for each series that you want to plot on the graph. 
SUM(IF LOGSOURCETYPENAME(devicetype) = 'System Notification' THEN 1.0 ELSE 0.0) 
as system_notification
SUM(IF LOGSOURCETYPENAME(devicetype) = 'SIM Audit' THEN 1.0 ELSE 0.0) as sim_audit
The full query looks like the following example:
SELECT starttime/(1000*60) as 'minute',
(minute * (1000*60)) as 'stime',
SUM(IF LOGSOURCETYPENAME(devicetype) = 'System Notification' THEN 1.0 ELSE 0.0) 
as system_notification,
SUM(IF LOGSOURCETYPENAME(devicetype) = 'SIM Audit' THEN 1.0 ELSE 0.0) as sim_audit
FROM events
WHERE devicetype <> NULL
GROUP BY minute
ORDER BY stime asc
LAST 10 minutes
The query takes the row data for both System Notification and SIM Audit devices and pivots them into separate columns that correspond to a series on the chart:
Figure 3. Returned results for separate columns
Results for pivoting data rows into columns
The following image shows the view configuration and chart display for pivoting the rows.
Figure 4. Configuring the view and chart display
Configuring data rows into columns

While this method is fairly efficient to transform relational data into time series data, you must know ahead of time what data you're looking for.

Creating a dynamic time series

What if you don't know ahead of time what devices you're looking for, or you want to graph the top five most active devices in the last hour? You can create a dynamic time series in QRadar Pulse V2.1.4. Rather than pivoting rows of data into columns, the second strategy uses a secondary GROUP BY clause (called "device" in the examples) to create a dynamic time series.
SELECT starttime/(1000*60) as 'minute', 
MIN(starttime) as 'stime', 
eventcount as 'eventCount', devicetype as 'deviceType',
LOGSOURCETYPENAME(devicetype) as 'device', 
count(*) as 'total'
FROM events
WHERE deviceType IN (
    SELECT deviceType FROM (
        SELECT devicetype as 'deviceType', 
        count(*) as 'total'
        FROM events
        GROUP BY deviceType
        ORDER BY total DESC
        LIMIT 8
        LAST {Time_Span}
    )
) and devicetype not in (18,105,147,368)
GROUP BY minute, device
ORDER BY minute asc
LAST {Time_Span}
The returned results for the query look like the following image:
Figure 5. Returned results for device
Data results for dynamic time series

The difference from the previous method is that the data is not pivoted into columns and contains repetitions in the time column that is caused by the secondary GROUP BY clause. By using the dynamic time series option, QRadar Pulse splits the data into a proper time series format. Select the column that contains the time (stime for the x-axis), the column that contains the data (total for the y-axis), and the column that contains the GROUP BY clause (to extract the different series by device).

Figure 6. Splitting the data by device
Splitting the data by device in dynamic time series

Although the chart looks the same as the one that was created by the previous method, the underlying AQL query is much more dynamic. If the log sources change over time, the chart automatically updates because the values are no longer hardcoded into the query.

However, you need to be aware of some caveats. Because the data rows don't pivot into columns, the size of the data is much larger and grows proportionately with the number of devices. QRadar Pulse processes the data, so the size of the data might negatively impact the overall responsiveness of the browser. To prevent performance degradation, QRadar Pulse renders the first 20 data series that it detects, and ignores subsequent series. Limit the size of the data by using the WHERE clause to LIMIT and ORDER your data sets for use in a dynamic time series. For example, if the use case is "count the top three most active devices in the last 10 minutes, excluding 'Health Metrics'," create the following query:
SELECT starttime/(1000*60) as 'minute', (minute * (1000*60)) as 'stime', LOGSOURCETYPENAME(devicetype) as 'device', count(*) as 'total'
FROM events
WHERE device IN (
    SELECT deviceList FROM (
        SELECT LOGSOURCETYPENAME(devicetype) as deviceList, count(*) as topDevices
        FROM events
        WHERE deviceList <> 'Health Metrics'
        GROUP BY deviceList
        ORDER BY topDevices DESC
        LIMIT 3
        LAST 10 minutes
    )
)
GROUP BY minute, device
ORDER BY stime asc
LAST 10 minutes
The query is more efficient, and ensures that the three rendered series correspond to the top three active devices in the selected time span.