Splunk Search

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

mprreddy51
Explorer

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

mprreddy51
Explorer

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma

mprreddy51
Explorer

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

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