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

[mprreddy51]

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

[woodcock]

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)

[sundareshr · Accepted solution]

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