Splunk Search

How to get stats by month?

SS1
Path Finder

Hi,

I have my current search giving below output, I want to have stats listed by Month. Can someone help on this one

Current Search:  my search |  eval True=(total1-total2) | eval False=round(False/(True+False)*100,2) | table False

Output:        False

                        42.12

 

Desired Output: 

Month      False

August    42.12

July           xx.xx

june           xx.xx

.

.

.

Labels (6)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| bin _time span=1mon
0 Karma

SS1
Path Finder

It still doesn't give monthly stats, still shows just the value

0 Karma

bowesmana
SplunkTrust
SplunkTrust

with bin command, you would normally then use "stats xxxx by _time" to then group values according to the month and then you do your true/false calculations on that.

What is in 'my search' part of your search?

0 Karma

SS1
Path Finder

My search

 

index=action AND "True" | dedup _time| stats count as total1| appendcols [search index=action AND "[Home]" | dedup _time| stats count as total2] | appendcols [search index=action AND "False" | dedup _time| stats count as False]| eval True=(total1-total2)|eval False=round(False/(False+True)*100,2)| table False

0 Karma

bowesmana
SplunkTrust
SplunkTrust

OK, without knowing your data, I would suggest that using appendcols is not the best way to approach the problem.  I am not sure why you are deduping _time. 

You have events in index=action that can have

a) any ONE of the 3 possible (True, [Home], False)
b) one or more of (True, [Home], False)

and as you are deduping _time, it may be that you have more than one with the identical _time value

Depending on the answers to the above, this may be an option

index=action AND ("True" OR "[Home]" OR  "False")
| eval T=if(match(_raw, "(?i)True"), 1, 0)
| eval H=if(match(_raw, "(?i)\[Home\]"), 1, 0)
| eval F=if(match(_raw, "(?i)False"), 1, 0)
| bin _time span=1mon
| stats sum(T) as T sum(H) as H sum(F) as F count by _time
| eval True=(T-H) 
| eval False=round(F/(F+True)*100,2) 
| table False

A single search to collect all data. Some eval statements to determine the type of data you are looking at and then the bin of time for 1 month the stats by _time (i.e. 1 month)

Then the calculates for the True/Home/False values.

However, your reason for deduping _time is significant. If there is more than one T, H or F at _time and you want to disregard those, this is not right as it will count those twice.

You could add a 

| stats max(T) as T max(H) as H max(F) as F by _time

before the | bin command to get either a 1 or 0 for time for each value of _time before you aggregate to 1 month.

 

 

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...