Report Splunks

Report Splunks

(Splunk Professional only)

The report:: modifier is a way to create SQL reports from your search results. If you add report:: to a search, Splunk doesn't deliver results in its usual format. Instead it creates a temporary SQL table, resultstable, based on the results set for the search, executes the value of report:: as a SQL statement, and outputs the results of the statement as a table instead of Splunk's usual results page.

resultstable contains one row for each event, and one column for each identifiable field that Splunk was able to extract from the results of the search. If Splunk cannot extract any fields from an event, it leaves that event out of resultstable so that the report doesn't contain hundreds of blank lines.

The example Splunk below first creates a results table of all events that match eventtype::login, then selects the pre-trained address fields _ip and _url from each. _ip contains a comma-separated list of all IP addresses for each event; _url contains URLs in the event.

eventtype::logon report::[select _ip, _url from resultstable]

The above Report Splunk would produce a table with two columns, _ip and _url. If an event did not contain a value for _ip or _url, Splunk would exclude its row from the report rather than include an empty row.

No database involved

To be clear, Splunk has no relational database to drag on its performance. Instead, whenever a Splunk search contains report:: the Splunk server creates a short-lived resultstable at run time. The report:: operator can then make a SELECT statement on resultstable.

Syntax

• All keywords must be typed in lowercase, contrary to SQL convention. A fix for this is pending.
• select is the only supported SQL statement.
• Report Splunks are built on SQLite. See SQLite syntax reference for details. For example, SELECT does not support TOP and BOTTOM.
• resultstable is the only table available to be queried. The table does not persist after the Report Splunk completes.
• select statements may be nested.

Shortcuts

Splunk includes several shortcuts to SQL's select syntax for quick splunking.

• report::[*]

Shortcut for report::[select * from resultstable]

• report::[field]

Shortcut for report::[select field from resultstable]

• report::[top field]

Shortcut for report::[select field, count(*) from resultstable group by field order by count(*) desc]

• report::[rare field]

Shortcut for report::[select field, count(*) from resultstable group by field order by count(*) asc]

Exporting reports

To save your Report Splunk as a CSV or text file, use the Export function on the Splunks menu.

How Splunk recognizes fields

There are three ways Splunk populates fields with values from events.

• It looks for segments that match the acceptable formats and values for IP addresses and URLs. It uses these to populate the fields _ip and _url.
• It looks for segments that are probably name-value pairs, such as user=jsmith or level:3. These are used to create fields such as _user and _level on the fly.

You can train Splunk to recognize patterns in your data and use them to populate fields with specific names. The training will work on events already in the index, as well as new events indexed after training.

Training Splunk to recognize fields

If Splunk does not create the fields you expect from your results, you can train it to recognize fields by running this command on the Splunk server host.

# splunk train fields

The program will prompt you for a sample data file, and then interactively prompt you to specify which parts of the events in the sample should be identified as what fields. You will then be able to search for those fields using report::. For example, if you train Splunk to recognize the field _OriginatingIP, you can then run the following Splunk:

report::[select _OriginatingIP from resultstable]

This automatically creates entries in your local configuration bundle (in $SPLUNK_HOME/etc/bundles/local.) For more control, you can manually configure these in the files props.conf and regexes.conf. See Define Search-Time Report Fields in the Admin Manual for additional information.

report:: and maxresults::

Adding report:: to a Splunk changes the default value of maxresults:: to 100 instead of 10,000. This is to prevent browsers from choking on large reports, but you can use higher values.

The number of rows in a report will usually be less than the value of maxresults::, because the report won't include rows that don't contain the fields specified by the select statement given as the value of the report:: modifier.

No Results?

If you expect results in a report:: but don't get any, it means there were no matching fields in the first maxresults:: number of results for your Splunk. You can increase the value of maxresults::, but it's more effective to sharpen your Splunk so that relevant values appear in the first 1000 results. Set a time range or specify your event type or source type to remove events that aren't necessary for the report.

Examples

• meta::all report::[select * from resultstable where _ip like '%445%']
• host::gwrk1 eventtype::?9034 report::[select _ip, count(*) from resultstable group by _ip order by count(*) desc]

• Was this topic useful?
• Post a comment