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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...