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

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
Legend

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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...