Splunk Search

table of multiple averages, each for a different time period

ltruesda
Explorer

I have an index of events where each event is associated with a user. I want to produce a table where each row represents a user. Of course the first column will be the key column of userids.

The other three columns need to be event-per-day averages for three consecutive time periods. For example, the first time period could be "before July 1st, 2013", the second could be "from July 1st, 2013 through August 31st, 2013", and the third could be "after August 31st, 2013".

The intention is to split time into three consecutive time periods and calculate event-per-day averages for each time period.

Thanks for all assistance.

Tags (3)
0 Karma
1 Solution

lguinn2
Legend

Try this

yoursearchhere
| bucket _time span=1d
| stats count by _time userId
| eval cutTime1 = strptime("7/1/2013","%m/%d/%Y")
| eval cutTime2 = strptime("8/31/2013","%m/%d/%Y")
| eval timePeriod = case(_time < cutTime1,"before July 1st, 2013",
                         _time >= cutTime1 AND _time <= cutTime2,"from July 1st, 2013 through August 31st, 2013",
                         _time > cutTime2,"after August 31st, 2013")
| chart average(count) by userId timePeriod

Look at the table output from the chart command (instead of the graphic) and I think it will be just what you asked for -- and it doesn't require subsearches, so it may be much faster...

View solution in original post

lguinn2
Legend

Try this

yoursearchhere
| bucket _time span=1d
| stats count by _time userId
| eval cutTime1 = strptime("7/1/2013","%m/%d/%Y")
| eval cutTime2 = strptime("8/31/2013","%m/%d/%Y")
| eval timePeriod = case(_time < cutTime1,"before July 1st, 2013",
                         _time >= cutTime1 AND _time <= cutTime2,"from July 1st, 2013 through August 31st, 2013",
                         _time > cutTime2,"after August 31st, 2013")
| chart average(count) by userId timePeriod

Look at the table output from the chart command (instead of the graphic) and I think it will be just what you asked for -- and it doesn't require subsearches, so it may be much faster...

HiroshiSatoh
Champion

Are you sure you want a like this?

(your search) earliest=-1d@d latest=@d |stats avg(Recordvalue) as Day1avg by UserID|join UserID [search (your search) earliest=-2d@d latest=-1@d |stats avg(Recordvalue) as Day2avg by UserID]|join UserID [search (your search) earliest=-3d@d latest=-2@d |stats avg(Recordvalue) as Day3avg by UserID]

result:
UserID Day1avg Day2avg Day3avg
A001 1 2 3
A002 4 5 6
A003 7 8 9

HiroshiSatoh
Champion

I used a sub-search because it did not know what average is, but I was glad to the average of the number of events. and advances the more you use the bucket.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...