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
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
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)
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