Splunk Search

How do I edit my current search to get my expected output?

mprreddy51
Explorer

Hi,

Can anyone suggest how to get the below expected output as shown? I am getting only 2 rows in the result currently, but I should get 3 rows because TYPE field is not there in one event and I used "BY" as shown in the search.

Search:

index=pqr sourcetype=abc  SERVICE_NAME=WORST MESSAGE=REQ |stats earliest(_time) as start by ACCNO | join ACCNO [ search index=pqr sourcetype=abc  SERVICE_NAME=WORST MESSAGE=RES |stats earliest(_time) as END by ACCNO,COUNTRY,TYPE|eval Proc_time=START-END|table ACCNO,COUNTRY,TYPE,proc_time

Sample Data:

20140122T100510 SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1234 
20140122T100513 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=GOLD  
20140122T100514 SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1432 
20140122T100515 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=SILVER 
20140122T100516 SERVICE=WORST MESSAGE=REQ COUNTRY=USA ACCNO=1236
20140122T100518 SERVICE=WORST MESSAGE=RES COUNTRY=USA ACCNO=1236 

Current output:

ACCNO   COUNTRY   TYPE     proc_time(sec)
1234    USA    GOLD     03
1432    USA    SILVER   01

Expecting Output:

ACCNO   COUNTRY   TYPE     proc_time(sec)
1234    USA    GOLD     03
1432    USA    SILVER   01
1236    USA       null     02

Thanks in advance.

-PR

0 Karma
1 Solution

sundareshr
Legend

Try this

 index=pqr sourcetype=abc  SERVICE_NAME=WORST MESSAGE=REQ |stats earliest(_time) as start by ACCNO | join ACCNO [ search index=pqr sourcetype=abc  SERVICE_NAME=WORST MESSAGE=RES |stats earliest(_time) as END values(COUNTRY) as COUNTRY values(TYPE) as TYPE by ACCNO]|eval Proc_time=START-END|table ACCNO, COUNTRY, TYPE, Proc_time

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

... | fillnull value="null" TYPE
| eventstats range(_time) AS proc_time_secs BY ACCNO COUNTRY
| stats first(proc_time_secs) AS proc_time_secs values(TYPE) AS TYPE BY ACCNO COUNTRY
| eval TYPE = mvindex(TYPE, 0)
0 Karma

sundareshr
Legend

Try this

 index=pqr sourcetype=abc  SERVICE_NAME=WORST MESSAGE=REQ |stats earliest(_time) as start by ACCNO | join ACCNO [ search index=pqr sourcetype=abc  SERVICE_NAME=WORST MESSAGE=RES |stats earliest(_time) as END values(COUNTRY) as COUNTRY values(TYPE) as TYPE by ACCNO]|eval Proc_time=START-END|table ACCNO, COUNTRY, TYPE, Proc_time
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 ...