i want to find out time between two fields but am not getting results

[prashanthberam]

i have written query like this

index=** sourcetype="****" |rex field=_raw "^(?:[^ \n]* ){6}(?P[^\-]+)\-\w+:\d+\-\w+:(?P\d+)" | eval InvocationType_REQ=if(InvocationType="REQ",timestamp,"") | eval InvocationType_ACK=if(InvocationType="ACK",timestamp,"")|eval InvocationType_RSP=if(InvocationType="RSP",timestamp,"")| transaction CorrelationID |table ClaimID,InvocationType_REQ,InvocationType_ACK,InvocationType_RSP,duration

I want the duration time in sec and one more thing i don't want ClaimID's in the list those doesn't have InvocationType_ACK timing (CLAIMIDS)
SUGGEST THE QUERY HOW I DO IT...

[woodcock · Accepted solution]

Like this:

index=ccsp sourcetype="verscend"
| rex field=_raw "^(?:[^ \n]* ){6}(?P[^\-]+)\-\w+:\d+\-\w+:(?P\d+)"
| eval InvocationType_REQ=if(InvocationType="REQ",timestamp,"")
| eval InvocationType_ACK=if(InvocationType="ACK",timestamp,"")
| eval InvocationType_RSP=if(InvocationType="RSP",timestamp,"")
| stats range(_time) AS duration values(*) AS * BY CorrelationID
| search InvocationType_ACK="*"
| table ClaimID InvocationType_REQ InvocationType_ACK InvocationType_RSP duration

[niketn]

Add the following in your search before displaying the table.
| search eventcount>=1 AND InvocationType!="ACK" |

Transaction command adds eventcount and duration fields to a search. Eventcount gives a list of events that are grouped together based on the transaction id i.e. CorrelationID in your case. Similarly duration is the difference of first and last event matched by transaction in seconds. If you are getting it as 0, it implies you have only single event in the transaction.

In order to test transaction you can add CorrelationID=<Your Sample Correlation ID with multiple events> to your base search.

Transaction is an expensive command if run for long duration of time and might drop data. You should consider using startswith, endswith, keepevicted, keeporphaned, maxpause and maxspan parameters based on your use case. Infact, you should also explore stats instead of transaction which will return results faster.

Refer to the following documentation on Splunk event correlation commands to be used based on various use cases: http://docs.splunk.com/Documentation/Splunk/latest/Search/Abouteventcorrelation

[gcusello]

Hi prashanthberam,
duration time after a transaction command is showed in seconds and, if you want, you can shot it in minutes, hours ...!
about the request to not have ClaimID value when InvocationType_ACK is null you have to add the following command:

| eval ClaimID=if(isnull(InvocationType_ACK),"-",ClaimID)

I suggest to put a character (e.g."-") to test the condition and after you can remove it.
Bye.
Giuseppe