Splunk Search

How do I get row number grouped by column?

Explorer

I can add an absolute row number to my search results with

 streamstats count as row 

However, I would like the row count to group by other columns. So in the example below, the row number groups by COL_A - each time the value of COL_A changes, the row number (ROW column) resets to 1 again

ROW | COL_A | COL_B

---------------------
1   | ABC   | 123    
2   | ABC   | 456
3   | ABC   | 789
1   | DEF   | 123
2   | DEF   | 456
1   | GHI   | 789

Any ideas?
Thanks in advance

Tags (3)
1 Solution

SplunkTrust
SplunkTrust

Try this

index="the_index" earliest=-7month@month latest=@month | bucket _time span=1month | stats count by custName _time | table custName _time count | streamstats count as row by custName| fields row *

View solution in original post

SplunkTrust
SplunkTrust

Try this

index="the_index" earliest=-7month@month latest=@month | bucket _time span=1month | stats count by custName _time | table custName _time count | streamstats count as row by custName| fields row *

View solution in original post

SplunkTrust
SplunkTrust

YOu are almost there. Just add a "by COL_A" in you streamstats command.

Explorer

Actually, worked it out. the "by" clause goes after the "as" clause, which is not like usual Splunk syntax. I would have expected an error rather than an empty column. Anyway, thanks for the help. For clarity, the correct search is:

index="the_index" earliest=-7month@month latest=@month  |  bucket _time span=1month | stats count by custName _time |  table  custName _time count | streamstats  as row count by custName | fields row *

Not sure how to mark your answer as correct and give you points if you only gave it in a comment rather than as an answer, but thanks again!

0 Karma

Explorer

Streamstats works for me if I don't have a "by" clause, but as soon as I add the "by", the column has no values. My search is trying to create a set of row numbers per custName, with the count column being the number of records for that customer in the month:

index="the_index" earliest=-7month@month latest=@month  |  bucket _time span=1month | stats count by custName _time |  table  custName _time count | streamstats count by custName  as row | fields row *

What stupid mistake am I making?

0 Karma