Splunk Search
Highlighted

How do I edit my search to find the first Value1 and first Value2 from my sample event, then calculate the difference?

Explorer

Hi,

I need to get the first Message REQ and first Message RES from the below event and should show my below expected output.

Sample data:

20140122T100512 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1234 
20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=ABC COUNT=0 STATUS=P
20140122T100514 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1234 
20140122T100515 EMP MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=ABC COUNT=1 STATUS=A

Search:

Index=abc sourcetype=PQR EMP |transaction ACCNO|eval Login=_time | eval Logout=_time + duration|eval diff=tostring(Logout-Login, "duration")|search TYPE=ABC |table ACCNO, DIFF, STATUS, COUNTRY,  TYPE,COUNT|search STATUS=P 

I also used |search STATUS!=A, but still 2 values are appearing in count and status columns

My current output:

ACCNO   DIFF        STATUS                   COUNTRY   TYPE   COUNT
1234    (GARBAGE)   P,A(A in differentrow)   USA       ABC    0,1(1 in differentrow) 

Expected output:

ACCNO   DIFF   STATUS   COUNTRY   TYPE   COUNT
1234    01     P        USA    ABC    0

Thanks,
PR

0 Karma
Highlighted

Re: How do I edit my search to find the first Value1 and first Value2 from my sample event, then calculate the difference?

Legend

Since status is a multi value field, you will have to use a mv functions. Try like this

.... | eval status=mvfilter(match(status, "P"))
0 Karma
Highlighted

Re: How do I edit my search to find the first Value1 and first Value2 from my sample event, then calculate the difference?

Explorer

@sundareshr

Here i need to pick only first two events first req and first res for each accno.Only few accno have multiple req and multiple res,rest all accno have one req and one res.

Here count(0,1) and status(A,P) are having 2 values ,ONLY for this type of case where you get multiple req and res for same accno.rest will have one value only which is fine. I this type of case it is picking 2 values as shown in my output above. I am looking for desired output.

20140122T100512 EMP MESSAGE=REQ COUNTRY=USA ACCNO=1234
20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=ABC COUNT=0 STATUS=P

Thanks!!!

0 Karma
Highlighted

Re: How do I edit my search to find the first Value1 and first Value2 from my sample event, then calculate the difference?

Legend

In that case, what if you did something like this

 Index=abc sourcetype=PQR EMP (MESSAGE=REQ) OR (MESSAGE=RES AND STATUS=P)
0 Karma
Highlighted

Re: How do I edit my search to find the first Value1 and first Value2 from my sample event, then calculate the difference?

Explorer

If i use OR condition as u said then i will not get time difference. Because i am using transaction command.It will show 00:00:00 and i am passing there status values as a parameter in drilldowns.

0 Karma
Highlighted

Re: How do I edit my search to find the first Value1 and first Value2 from my sample event, then calculate the difference?

Legend

Not sure I understand your comment about time difference. Did you try this search? Also, you can use tokens to pass the status values, should work just fine.

0 Karma