All Apps and Add-ons

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

prashanthberam
Explorer

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...