How to get response time from this search?

[karthi2809]

How to get response time from my search?

APIName is from my inputlookup

|inputlookup SolutionCenter.csv | append [search index=gee_sit  |eval responseTime=TransactionSentEndtime - TransactionReceivedStartTime|eval responseTime=round((responseTime/1000),3)|stats avg(responseTime) by TargetBasePath ]|stats avg(responseTime) by TargetBasePath APIName

[nickhills · Accepted solution]

I think kamlesh has nailed this for you, with one minor tweak.

 index=gee_sit 
 | eval responseTime=TransactionSentEndtime - TransactionReceivedStartTime 
 | eval responseTime=round((responseTime/1000),3) 
 | lookup SolutionCenter.csv TargetBasePath OUTPUTNEW APIName 
 | stats avg(responseTime) by APIName

[karthi2809]

Thanks a lot its working

[starcher]

One minor tweak. This helps reduce the event count getting to the eval and stats to be ones only with the field from your lookup. I would avoid the inputlookup with an append of a search as a pattern. Especially in large volume environments.

  index=gee_sit 
  | lookup SolutionCenter.csv TargetBasePath OUTPUTNEW APIName 
  | where isnotnull(APIName)
  | eval responseTime=TransactionSentEndtime - TransactionReceivedStartTime 
  | eval responseTime=round((responseTime/1000),3)  
  | stats avg(responseTime) by APIName

[kamlesh_vaghela]

Hi @karthi2809,

what is the relationship OR mapping between lookup and search data?? Is that any field in lookup file which can be mapped with TargetBasePath to fetch APIName?.

[karthi2809]

yes i mapped with TargetBasePath to fetch APIName

[kamlesh_vaghela]

Then can you please try this?

index=gee_sit 
| eval responseTime=TransactionSentEndtime - TransactionReceivedStartTime 
| eval responseTime=round((responseTime/1000),3) 
| stats avg(responseTime) by TargetBasePath | lookup SolutionCenter.csv TargetBasePath OUTPUT APIName 

https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Lookup

[karthi2809]

great thanks you

[niketn]

@nickhills, @starcher, If stats can be performed on TargetBasePath and then enriched with lookup command, the search will perform better. I think that is the point @kamlesh_vaghela has made in his query. Following is the Splunk Docs reference for the same: https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Lookup#Optimizing_your_lookup_se...

[nickhills]

Can you share some event data?

[karthi2809]

Tue Dec 19 05:30:29 EST 2017Info: Trace: MessageID=66e0fb4b7a00 ; TransactionID=va10p40027-30801-14958502-24 ; URI=/v1/carealerts/message ; Environment=prod ; Proxy=CareAlerts-CORE-v1 ; TransactionReceivedStartTime=1513679429101 ; TransactionReceivedEndtime=1513679429102 ; RequestSentStartTime=1513679429109; RequestSentEndTime=1513679429109 ; ResponseReceivedStartTime=1513679429589 ; ResponseReceivedEndTime=1513679429590 ; TransationSentStartTime=1513679429600 ; TransactionSentEndtime=1513679429602 ; TargetHost=prods.com ; TargetBasePath=/CareManagement/1.0/CareAlertMessageRHI ; TargetCopySuffix=false ; TargetCopyQueryParams=true ; IsError=false ; Status=200 ; ErrorMsg=\x00