topic Re: Need solution on search query in Splunk Search

Need solution on search query

dinesh001kumar — Sat, 02 Nov 2024 13:43:35 GMT

I am having two index( index A and index B). Here I need to measure response time of topup of prepaid or postpaid number with help of transaction ID.

From index A I can filter where the transaction is prepaid or postpaid,index A contains(customer ID, Type(Prepaid or Postpaid).

In indexB we have two logs one is request log and other is response log.

With help of customer ID from Index A I need to find the transaction ID from Request log since customer ID is not available in response log. Once we get the transaction ID, we need to substract the time stamp (Response log time- Request log time).

Index A. Log pattern---> _timestamp, customerID,type

Index B----> contains request and response log.

Request log pattern---> timestamp, transactionID, customer ID

Response log pattern--->timestamp, transactionID,status.

Method to measure --> From index A we need to get customerID and then go to index B to find out the transaction ID from Request log. With help of transactionID need to subtract the timestamp between response and request log from index B

Please help us how we can proceed,in SPL query.

Re: Need solution on search query

richgalloway — Sat, 02 Nov 2024 17:38:24 GMT

This would more efficient if indexA was a lookup table, but this query should get you started. Others may bristle at the use of join, but they are welcome to submit alternatives. 🙂

index=indexb OR index=indexa | stats values(*) as * by transactionID | join customerID [search index=indexa] | table timestamp customerID transactionID status type

Re: Need solution on search query

ITWhisperer — Sun, 03 Nov 2024 13:36:36 GMT

| eval request_time=if(isnotnull(transactionID) AND isnotnull(customerID), time, null()) | eval response_time=if(isnotnull(transactionID) AND isnull(customerID), time, null()) | eventstats values(request_time) as request_time values(response_time) as response_time values(customerID) as customerID by transactionID | eventstats values(type) as type by customerID | stats values(request_time) as request_time values(response_time) as response_time values(status) as status values(type) as type by customerID transactionID