table with fields and count related by another field

lemospt — Fri, 04 Aug 2023 17:28:59 GMT

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

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

Re: table with fields and count related by another field

ITWhisperer — Fri, 04 Aug 2023 13:38:47 GMT

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

Re: table with fields and count related by another field

lemospt — Fri, 04 Aug 2023 17:11:14 GMT

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

topic table with fields and count related by another field in Splunk Search

table with fields and count related by another field

Re: table with fields and count related by another field

Re: table with fields and count related by another field