Skip to main content
Sumo Logic

Part 3: Chart your data

In this tutorial, we'll build out your query using aggregation statements to count the results, and visualize the data by creating a chart.

Next, let’s build out your query using aggregation statements to count the results, and visualize the data by creating a chart.

Suppose you’re wondering about your 404 errors, and want to learn more. Are they happening at once? We can find out by adding aggregation statements to your query to help group and order the data, so the results are much easier to read.

 Aggregate your search results

First, let's count the results for each status code.

  1. In the search query box enter a soft return to add a new line. Then add the count statement:
    | count by status_code
    So now the query looks like this:

    _sourceCategory=Apache/Access and GET
    | parse "\"GET * HTTP/1.1\" * * \"*\" " as url,status_code,size,referrer
    | count by status_code

  2. Click Start.

Next to the Messages tab, notice the new tab called Aggregates. Here you can see status_code and the _count broken out into a table.

Now let’s find out when those status codes happened. We can use the timeslice operator to find out and count the status codes over time in one-minute increments.

  1. Above the count operator, add a new line:
    | timeslice 1m
  2. Edit the count operator to read:
    | count by _timeslice, status_code
    Now the query is:
    _sourceCategory=Apache/Access and GET
    | parse "\"GET * HTTP/1.1\" * * \"*\" " as url,status_code,size,referrer
    | timeslice 1m

    | count by _timeslice, status_code

  3. Click Start to execute the search.
    Now the Aggregates tab shows each status code and the number of times it occurred in each one-minute interval. You can click any of the column headers in the table to sort by that column.

Transpose results

There’s good information here, but we can make it even easier to read. Let’s do a transpose (similar to Excel) by using the transpose operator.

  1. Under the count statement, add:
    | transpose row _timeslice column status_code
    Now the query reads:
    _sourceCategory=Apache/Access and GET
    | parse "\"GET * HTTP/1.1\" * * \"*\" " as url,status_code,size,referrer
    | timeslice 1m
    | count by _timeslice, status_code
    | transpose row _timeslice column status_code
  2. Click Start.
    Now in the Aggregates table, each status code is broken out as the column, and the count of each status code in its timeslice is the row.

Much better. But, from here, Sumo can create something even easier to read. Let’s visualize the data by making a chart.

Create a chart

There are many chart options that would work for our results, as shown by the available chart buttons displayed in the top right of the Aggregates tab. From here we can make a bar, column, line, area, or a pie chart.

Let’s create a column chart.

  1. Click the Column Chart icon.

    You can now see a bar chart with a different bar for each status code.
    This is good, but the chart is dominated by 200 status codes, which aren’t of interest right now.
  2. We can remove them by clicking the 200 item in the legend to the right. Let’s remove the 304 events as well.
    Now the 200 and 304 events are grayed out in the legend.

What if you wanted to get rid of the 200s and 304s permanently from the query? No problem. Just add a where clause to the query, right above the timeslice line. It’s a best practice to filter your data before you aggregate it -- that’s why we add the where statement before the count operation. The exclamation point is used as a NOT. So this statement means, "where the status code is NOT 200 or 304".

  1. Add to your query:
    | where !(status_code=200 or status_code=304)
    Now the query reads:
    _sourceCategory=Apache/Access and GET
    | parse "\"GET * HTTP/1.1\" * * \"*\" " as url,status_code,size,referrer
    | where !(status_code=200 or status_code=304)
    | timeslice 1m
    | count by _timeslice, status_code
    | transpose row _timeslice column status_code
  2. Click Start.
    Now you can see that the 200 and 304 status codes no longer appear in the chart or legend.

Wow! You’re making excellent progress. You’ve completed these tasks in Part 3 of the Quick Start user tutorial:

  1. Aggregated your search results.

  2. Used the transpose operator to make the results easier to read.

  3. Created a chart of the results and adjusted the chart legend to show only the data of most interest.

Now let’s go to Part 4 and add your results to a dashboard.