Splunk Search

How do I get row number grouped by column?

leatherface
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

somesoni2
Revered Legend

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

somesoni2
Revered Legend

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 *

somesoni2
Revered Legend

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

leatherface
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

leatherface
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
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...