topic Re: How to build the query for the raw data. in Splunk Search

How to build the query for the raw data.

Vamshi1904 — Fri, 24 May 2024 23:01:00 GMT

Hi Team,

Good day!

I need to build query in such way that need to get only success payload that are related to particular service name. where that service name is used by different application such like (EDS, CDS).

we need to pull the data from request payload to Response payload success based on correlation ID which is present in request payload and each event contain unique Correlation ID. and we are using below query to pull the data for request payload.

index="os" host="abcd*" source="/opt/os/*/logs/*" "implementation:abc-field-flow" "TargetID":"abc" "Sender":"SenderID":"abc"

By using above query, we are getting below raw data:

INFO 2024-05-23 06:05:30,275 [[OS].uber.11789: [services-workorders-procapi].implementation:abc-field-flow.CPU_LITE @7d275f1b] [event: 2-753d5970-18ca-11ef-8980-0672a96fbe16] com.wing.esb: PROCESS :: implementation:abc-field-flow :: STARTED :-: CORRELATION ID :: 2-753d5970-18ca-11ef-8980-0672a96fbe16 :-: REQUEST PAYLOAD :: {"Header":{"Target":{"TargetID":"abc"},"Sender":{"SenderID":"abc"}},"DataArea":{"workOrder":"42141","unitNumber":"145","timestamp":"05/23/2024 00:53:57","nbSearches":"0","modelSeries":"123","manufacturer":"FLY","id":"00903855","faultCode":"6766,1117,3497,3498,3867,6255,Blank","faliurePoint":"120074","faliureMeasure":"MI","eventType":"DBR","event":[{"verificationStatus":"Y","timestamp":"05/23/2024 01:32:30","solutionSeq":"1","solutionId":"S00000563","searchNumber":"0","searchCompleted":"True","repairStatus":"N","informationType":"","componentID":""},{"verificationStatus":"Y","timestamp":"05/23/2024 01:32:30","solutionSeq":"2","solutionId":"S00000443","searchNumber":"0","searchCompleted":"True","repairStatus":"N","informationType":"","componentID":""},{"verificationStatus":"Y","timestamp":"05/23/2024 02:03:25","solutionSeq":"3","solutionId":"S00000933","searchNumber":"0","searchCompleted":"True","repairStatus":"Y","informationType":"","componentID":""}],"esn":"12345678","dsStatus":"Open","dsID":"00903855","dsClosureType":null,"customerName":"Tar Wars","createDate":"05/23/2024 00:53:49","application":"130","accessSRTID":""}}

And we are using below query for response payload:

index="OS" host="abcd*" source="/opt/os/*/logs/*" "implementation:abc-field-flow" "status": "SUCCESS"

By using above query, we are getting below raw data:

5/23/24 11:35:33.618 AM INFO 2024-05-23 06:05:33,618 [[OS].uber.11800: [services-workorders-procapi].implementation:abc-field-flow.CPU_INTENSIVE @4366240b] [event: 2-753d5970-18ca-11ef-8980-0672a96fbe16] com.wing.esb: PROCESS :: implementation::mainFlow :: COMPLETED :-: CORRELATION ID :: 2-753d5970-18ca-11ef-8980-0672a96fbe16 :-: RESPONSE PAYLOAD :: { "MessageIdentifier": "2-753d5970-18ca-11ef-8980-0672a96fbe16", "ReturnCode": 0, "ReturnCodeDescription": "", "status": "SUCCESS", "Message": "Message Received" }

The above two quires raw data in the request payload correlation id should match to the response payload correlation id. So based on that I want to search query to pull only data from request payload to response payload based on the Correlation ID.

How to build the query by using two search quires I want only response payload data from two quires.

Thanks in advance for your help!

Regards,
Vamshi Krishna M.

Re: How to build the query for the raw data.

yuanliu — Fri, 24 May 2024 21:56:37 GMT

Normally I would not propose to ignore built-in structured data. But in this case, you can probably take a shortcut if you are not interested in data fields inside that JSON blob at all.

index="os" host="abcd*" source="/opt/os/*/logs/*" "implementation:abc-field-flow" (("TargetID":"abc" "Sender":"SenderID":"abc") OR ("status": "SUCCESS")) | rex "CORRELATION ID :: (?<correlation_id>\S+)" | eval success_id = if(searchmatch("COMPLETED"), correlation_id) | eventstats values(success_id) as success_id by correlation_id | where correlation_id = success_id

Here, I observe that status SUCCESS is a subset of COMPLETED. If that's not the case, you can also use searchmatch("\"status\": \"SUCCESS\"").

But if you want to utilize data fields inside JSON, it could be better to use MessageIdentifier instead, depending on the ratio between success and failure.

Re: How to build the query for the raw data.

Vamshi1904 — Mon, 03 Jun 2024 11:02:33 GMT

Hi yuanliu ,

Thank you for your reply..

I have tried the search index shared by you, but it doesn't work.

Here we have two different search indexes:

1) request payload:

index="os" host="abcd*"  source="/opt/os/*/logs/*"  "implementation:abc-field-flow" "TargetID":"abc" "Sender":"SenderID":"abc"

2) success payload :

index="OS" host="abcd*" source="/opt/os/*/logs/*" "implementation:abc-field-flow" "status": "SUCCESS"

I need to query the search index (only for the success payload) in such way that correlation id present in the success payload need to match with Correlation id present in the Request payload.

Could you please help me out.

NOTE: Different payload has different Correlation ID.

Re: How to build the query for the raw data.

ITWhisperer — Mon, 03 Jun 2024 11:46:40 GMT

Try it like this

index="os" host="abcd*" source="/opt/os/*/logs/*" "implementation:abc-field-flow" (("TargetID":"abc" "Sender":"SenderID":"abc") OR ("status": "SUCCESS")) | rex "CORRELATION ID :: (?<correlation_id>\S+)" | eval success_id = if(searchmatch("COMPLETED"), correlation_id,null()) | eventstats values(success_id) as success_id by correlation_id | where correlation_id = success_id