Splunk Search

How to combine the rows of a table?

Mrig342
Contributor

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..!!

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

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

View solution in original post

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

Mrig342
Contributor

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:

QueueConsumer_Count
APIM31
APIM21
APIM11

while the expectation was:

QueueConsumer_Count
APIM31
APIM21
APIM12

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

Thank you..!!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

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

Mrig342
Contributor

Hi @ITWhisperer ,

This modification really works for my desired output.

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...