Report: IT the Splunk way

If you’ve just downloaded 1.2+ you may not have discovered a cool new feature yet – you can now turn easy Splunk searches into structured statistical reports.

Why would you want to do this? Maybe you want to know how many 404s per URL. Maybe you want to know how many failed logins per user.

Here’s how:

1. Get the records you want to report on by running a search. On http://prodemo.splunk.com (login guest/guest) try searching for all of the DB2 log events by running this search:

sourcetype::db2_diag

OK, you’ve got normal Splunk search results now – individual events that match your search criteria. Let’s move on and get these events summarized.

2. Add the report:: operator to your search to run a structured search on the results, like this:

sourcetype::db2_diag report::[*]

What Splunk’s actually doing here is taking the results of the first search, making a temporary “table” called “resultstable” and running a query “SELECT * FROM resultstable.” So you get to do SQL on-the-fly if you need to, without the nasty overhead of actually storing all of your logs in a sloooooow, inflexible and bloated SQL relational database.

The results you get back show all of the “fields” that Splunk has automatically extracted from each record in the search results – these aren’t physically stored fields in our index in any way – they were just pulled out at search runtime. You can see that Splunk has figured out that parts of the events were name/value pairs like _probe and it’s recognized the patterns of IP addresses and populated a field called _ip. We can now do other report:: splunks using those fields.

(You can also train Splunk to learn new fields in any log, AFTER the logs have already gone into the index, but that’s a topic for another post. Suffice to say we do that the easy Splunk way too and you won’t be writing any tedious regexes to do it.)

3. Now use those fields in a more specific report:: Splunk, like this:

sourcetype::db2_diag report::[select _probe, count(*) from resultstable 
group by _probe order by count(*) desc limit 5]

Note we are making the SELECT and FROM explicit this time.

If you’re not a SQL-head and need a translation, we’ve asked Splunk to give us a count of our results by each _probe field value, in descending order of frequency, and only show us the first and most common 5.

We now get back a nice structured set of results:

_probe : 3

count(*) : 38


_probe : 4

count(*) : 8


_probe : 1

count(*) : 2


_probe : 10
 
count(*) : 2


_probe : 100

count(*) : 2

Kudos to David for this neat new feature. Read more about report:: and other cool features you may not otherwise discover in our concise User’s Guide.

Look for us to take the idea of search runtime extracted fields like this in more directions in future releases – we might make UI widgets that can show you the name of fields in your results and let you do things with special fields like IP addresses such as traceroutes. We’re also going to do more with the output and formatting of the report:: results.

This is new and just the beginning, so be sure to post a comment if you have any ideas for how we can push this further.

Try it out!