topic Re: How do I get row number grouped by column? in Splunk Search

How do I get row number grouped by column?

leatherface — Mon, 28 Sep 2020 17:42:39 GMT

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

Re: How do I get row number grouped by column?

somesoni2 — Fri, 26 Sep 2014 13:46:11 GMT

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

Re: How do I get row number grouped by column?

leatherface — Fri, 26 Sep 2014 16:49:52 GMT

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?

Re: How do I get row number grouped by column?

leatherface — Fri, 26 Sep 2014 17:13:13 GMT

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!

Re: How do I get row number grouped by column?

somesoni2 — Fri, 26 Sep 2014 17:13:39 GMT

Try this