Displaying the count of events over varying time spans

[bruceclarke]

All,

I want to create a search that will return the count of events over the last 5 minutes, 30 minutes, hour, 6 hours, and day. I was able to develop a search that nearly gets me there, but the rows and columns are reversed.

Unfortunately, the "transpose" command doesn't quite work, since it messes up the column names and I can't easily replace them.

The search I have so far is below. Does anyone know how I can achieve this? Maybe my search needs to be changed completely. Not sure.

<mySearch>
| eval span1=if(_time>relative_time(now(),"-5m"),1,0)
| eval span2=if(_time>relative_time(now(),"-30m"),1,0)
| eval span3=if(_time>relative_time(now(),"-60m"),1,0)
| eval span4=if(_time>relative_time(now(),"-360m"),1,0)
| eval span5=if(_time>relative_time(now(),"-1d@d"),1,0)
| chart sum(span1) as Last5Mins, sum(span2) as Last30Mins, sum(span3) as LastHr, sum(span4) as Last6Hrs, sum(span5) as Today by instance
| transpose 50

So, I want the columns to be all the values for instance and the rows to be Last5Mins, Last30Mins, etc.

[somesoni2 · Accepted solution]

Give this a try

<mySearch>
| eval span1=if(_time>relative_time(now(),"-5m"),1,0)
| eval span2=if(_time>relative_time(now(),"-30m"),1,0)
| eval span3=if(_time>relative_time(now(),"-60m"),1,0)
| eval span4=if(_time>relative_time(now(),"-360m"),1,0)
| eval span5=if(_time>relative_time(now(),"-1d@d"),1,0)
| chart sum(span1) as Last5Mins, sum(span2) as Last30Mins, sum(span3) as LastHr, sum(span4) as Last6Hrs, sum(span5) as Today by instance
| untable instance metrics count | chart max(count) as count over metrics by instance

[somesoni2]

Just learned this command 2 days back 😉
Another benefit of splunk answers, continuous learning.

[bruceclarke]

This is exactly what I wanted. I didn't know of the "untable" command. Thanks!

[alterdego]

Have you looked at appendcols?

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Appendcols