Splunk Search

table with fields and count related by another field

lemospt
Explorer

Hi,

 

i have the following case,

An operation has multiple events and every event of an operation is related by field PushId.

Below the events of one operation (underlined necessary fields)

13:14:03,838;04-08-2023 13:14:03.838;;SMS;33786543;iOS;001452c7-9f80-4215-87b5-a20b00e3c4fd;;;0;OK

13:09:31,150;04-08-2023 13:09:31.133;;SEND_PUSH_APNS;33786543;ios;001452c7-9f80-4215-87b5-a20b00e3c4fd;This is a Title;This is the Body.;17;OK;null

13:09:31,131;04-08-2023 13:09:31.102;;NON_SILENT_PUSH;33786543;;001452c7-9f80-4215-87b5-a20b00e3c4fd;This is a Title;This is the Body..;29;OK;null

01:23:52,652;04-08-2023 01:23:52.519;10.129.150.86;SEND_PUSH_REQUEST;33786543;ios;001452c7-9f80-4215-87b5-a20b00e3c4fd;This is a Title;This is the Body.;133;OK;29a6c9e8-d731-47b4-81b9-6748596c4138

I want to count by every PushID (001452c7-9f80-4215-87b5-a20b00e3c4fd in this case) the Number of requests equal to SEND_PUSH_REQUEST, ACK and SMS.

With this counts do a table including Title and Body,

For this particular case, should look like,

Title | Body | Requests (SEND_PUSH_REQUEST) | ACK | SMS

This is a Title | This is the Body | 1 | 0 | 1

 

for counts i'm ok with below queries

index=vfpt_idx_gssm_mbe PushId=001452c7-9f80-4215-87b5-a20b00e3c4fd
| stats count(eval(Request="SEND_PUSH_REQUEST")) as request
, dc(eval(Request="ACK")) as ack
, count(eval(Request="SMS")) as sms by PushId, Title, Body
| table Title Body request ack sms

the problem is isn't show the correct count for SMS because is Title and Body is not on all events

index=vfpt_idx_gssm_mbe PushId=001452c7-9f80-4215-87b5-a20b00e3c4fd
| stats count(eval(Request="SEND_PUSH_REQUEST")) as request
, dc(eval(Request="ACK")) as ack
, count(eval(Request="SMS")) as sms by PushId
| table Title Body request ack sms

With this query, counts are ok but table is not showing Title and Body.

 

I'm stuck here and i don't know how to relate the counts with fields Title and Body on a table.

 

Thank you

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
index=vfpt_idx_gssm_mbe PushId=001452c7-9f80-4215-87b5-a20b00e3c4fd
| eventstats vlaues(Title) as Title values(Body) as Body by PushId
| stats count(eval(Request="SEND_PUSH_REQUEST")) as request
, dc(eval(Request="ACK")) as ack
, count(eval(Request="SMS")) as sms by PushId Title Body
| table Title Body request ack sms

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
index=vfpt_idx_gssm_mbe PushId=001452c7-9f80-4215-87b5-a20b00e3c4fd
| eventstats vlaues(Title) as Title values(Body) as Body by PushId
| stats count(eval(Request="SEND_PUSH_REQUEST")) as request
, dc(eval(Request="ACK")) as ack
, count(eval(Request="SMS")) as sms by PushId Title Body
| table Title Body request ack sms

lemospt
Explorer

That's it @ITWhisperer, Thanks a lot for the help.

0 Karma
Get Updates on the Splunk Community!

Splunk Decoded: Service Maps vs Service Analyzer Tree View vs Flow Maps

It’s Monday morning, and your phone is buzzing with alert escalations – your customer-facing portal is running ...

What’s New in Splunk Observability – September 2025

What's NewWe are excited to announce the latest enhancements to Splunk Observability, designed to help ITOps ...

Fun with Regular Expression - multiples of nine

Fun with Regular Expression - multiples of nineThis challenge was first posted on Slack #regex channel ...