How do I get my search to produce my expected output?

[mprreddy51]

Hi,

I have a requirement. Below are the sample events:

20140122T100510 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1234 
 20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=ABC COUNT=0 STATUS=P REASON=FAILED
 20140122T100514 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1432 
 20140122T100515 EMP MESSAGE=RES COUNTRY=USA ACCNO=1432 TYPE=ABC COUNT=1 STATUS=A
 20140122T100516 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1236
 20140122T100517 EMP MESSAGE=RES COUNTRY=USA ACCNO=1236 TYPE=ABC COUNT=0 STATUS=P REASON=FAILED
 20140122T100518 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1436 
 20140122T100519 EMP MESSAGE=RES COUNTRY=USA ACCNO=1436 TYPE=ABC COUNT=1 STATUS=A
 20140122T100520 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1437 
 20140122T100521 EMP MESSAGE=RES COUNTRY=USA ACCNO=1437 TYPE=ABC COUNT=1 STATUS=C

Search that is giving correct output:

index= a  sourctype=b MESSAGE=REQ| eventstats earliest(_time) as start by ACCNO | join ACCNO [ search index= a sourcetype=b  MESSAGE=RES|eventstats earliest(_time) as end by ACCNO,TYPE,COUNT,STATUS,REASON| eval processing_time = end - start| search STATUS=P|fields - start,end| table ACCNO,TYPE,COUNT,STATUS,REASON,processing_time

Output:
accno type count status reason processing_time
1234 ABC 0 P FAILED 03
1236 ABC 0 P FAILED 01

QUERY: When STATUS= A OR C, it is giving the WRONG OUTPUT. It should display the processing_time column as shown in my expected output:

index= a  sourctype=b MESSAGE=REQ| eventstats earliest(_time) as start by ACCNO | join ACCNO [ search index= a sourcetype=b  MESSAGE=RES|eventstats earliest(_time) as end by ACCNO,TYPE,COUNT,STATUS,REASON| eval processing_time = end - start| search STATUS=A|fields - start,end| table ACCNO,TYPE,COUNT,STATUS,REASON,processing_time

Current output:
accno type count status reason processing_time
1432 ABC 1 A NULL(EMPTY) NULL(EMPTY)
1436 ABC 1 A NULL(EMPTY) NULL(EMPTY)

MY EXPECTED OUTPUT:
accno type count status reason processing_time
1432 ABC 1 A NULL(EMPTY) 01
1436 ABC 1 A NULL(EMPTY) 01

Thanks in advance. -PR

[somesoni2]

Give this a try

index= a sourctype=b MESSAGE=REQ OR MESSAGE=RES | fields _time ACCNO TYPE COUNT STATUS REASON MESSAGE| stats values(_time) as times values(*) as * by ACCNO | where mvcount(MESSAGE)=2
| eval processing_time=mvindex(times,-1)-mvindex(times,0) | table ACCNO,TYPE,COUNT,STATUS,REASON,processing_time

[richgalloway]

I'm not sure why you're not getting the desired results, but I think you'll have better performance using the transaction command.

index= a sourctype=b MESSAGE=REQ| transaction ACCNO | eval processing_time=duration | where STATUS=A | table ACCNO,TYPE,COUNT,STATUS,REASON,processing_time

[mprreddy51]

In this case there can be any number of REQ for same acctno. I am using "earliest". Here that is not the issue of using TRANSACTION.

[richgalloway]

Try inserting fillnull value="SUCCESS" REASON to your original query.

[mprreddy51]

The query which i posted is a small piece of my actual query. Still i have cases,lookups...etc
I am expecting the result as my Expected o/p, I am getting correct for STATUS=P because there is a field REASON. As comes to STATUS=A or C i am not because there is no field REASON.i think it is restricting because of BY.

Any idea expert!!!!!!!