How to combine the rows of a table?

[Mrig342]

Hi All,

I have logs like below in splunk.

log1: "count":1,

log2: gcg.gom.esb_159515.rg.APIMediation.Disp1.3.Rs.APIM3

log3: "count":1,

log4: gcg.gom.esb_159515.rg.APIMediation.Disp1.3.Rs.APIM2

log5: "count":1,

log6: gcg.gom.esb_159515.rg.APIMediation.Disp1.3.Rs.APIM1

I used the below query to create a table showing the "Queue" and the "Consumer count":

***** | rex field=_raw "Rs\.(?P<Queue>\w+)" | rex field=_raw "count\"\:(?P<Consumer_Count>\d+)\," | table Queue,Consumer_Count

But this query gives the table in the below manner:

Queue	Consumer_Count
	1
APIM3
	1
APIM2
	1
APIM1

I want the rows to be combined in the below manner:

Queue	Consumer_Count
APIM3	1
APIM2	1
APIM1	1

Please help to modify the query to get the desired output.

Thank you..!!

[ITWhisperer]

| streamstats latest(Consumer_Count) as Consumer_Count
| where isnotnull(Queue)

[Mrig342]

Hi @ITWhisperer 

Thank you for the query modification. However, when the Consumer_Count changes to 2 or more, it still shows 1 in the table.

For eg:

log1: "count":1,

log2: gcg.gom.esb_159515.rg.APIMediation.Disp1.3.Rs.APIM3

log3: "count":1,

log4: gcg.gom.esb_159515.rg.APIMediation.Disp1.3.Rs.APIM2

log5: "count":2,

log6: gcg.gom.esb_159515.rg.APIMediation.Disp1.3.Rs.APIM1

And query used is ***** | rex field=_raw "Rs\.(?P<Queue>\w+)" | rex field=_raw "count\"\:(?P<Consumer_Count>\d+)\," | streamstats latest(Consumer_Count) as Consumer_Count | where isnotnull(Queue) | table Queue,Consumer_Count

It still gives the table as below:

Queue	Consumer_Count
APIM3	1
APIM2	1
APIM1	1

while the expectation was:

Queue	Consumer_Count
APIM3	1
APIM2	1
APIM1	2

Please help to modify the query to get the table in the required manner.

Thank you..!!

[ITWhisperer · Accepted solution]

It depends on the order of the events - try this way instead

| streamstats last(Consumer_Count) as Consumer_Count
| where isnotnull(Queue)

[Mrig342]

Hi @ITWhisperer ,

This modification really works for my desired output.

Thank you so much for helping me always through your quick solutions.