table with fields and count related by another field

[lemospt]

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

[ITWhisperer · Accepted solution]

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]

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