topic Re: Splunk conditional count/aggregation in Splunk Search

Splunk conditional count/aggregation

bomran — Mon, 19 Mar 2018 15:55:20 GMT

I have some CSV data about files imported in to Splunk. The data looks like this:

"\\domain\path\to\file\","<filename>","<fsize>","<ext>","<Last_access>","<last_write>","<creation_time>","<attributes>","<owner>"

I have converted all the date strings to epoch using:

| eval epoch_LastAccessTime=strptime(LastAccessTime, "%d/%m/%Y %H:%M:%S")
...
...

I want to get:

A percentage of files last accessed between 6 months and 3 years ago
A percentage of files last accessed 3 years or more ago.

This is the search query that I have tried before getting stuck:

index="<my_index>" sourcetype="<my_sourcetype>" 
| rex field=DirectoryName "\\\domain\.org\\\teams\\\(?<Team>[^\\\]*)" 
offset_field=_extracted_fields_bounds  
| eval epoch_LastAccessTime=strptime(LastAccessTime, "%d/%m/%Y 
%H:%M:%S") 
| eval _time=epoch_LastAccessTime
| timechart span=6mon count

I've tried using commands along the lines of:

| where epoch_LastAccessTime>=three_year_ago_from_now AND 
epoch_LastAccessTime<=six_months_ago_from_now

However, this excludes everything else (3y+)

I want the result to look something like:

TimeRange  Perc
6m-3y      60%
3y+        40%

Re: Splunk conditional count/aggregation

Lowell — Mon, 19 Mar 2018 16:07:19 GMT

Your nearly on the right track. Instead of using a where to limit your results, use an eval to build a new categorical field. At that point you'll no longer need the timechart.

Something like:

| eval TimeRange=case(epoch_LastAccessTime>=three_year_ago_from_now, "3y+",
                      epoch_LastAccessTime>=six_months_ago_from_now, "6m-3y",
                      0=0, "less than 6m")
| stats count by TimeRange
| eventstats sum(count) as total_count
| eval pct=100*count/total_count

Note "0=0" is used to provide a "default" option, since it's always true.

Re: Splunk conditional count/aggregation

bomran — Mon, 19 Mar 2018 16:57:43 GMT

Exactly what I was looking for, thanks for your help.