How to write this query in Splunk?

[Anidy21]

This is application insight query which i need to write in splunk , can some one help me please

let a=traces
| where cloud_RoleInstance startswith "sams-card-account-update"
| where message contains "Received Message from CAU:"
| parse message with * "clientReqId='" clientReqId "', status=" * "resultReason='" resultReason "', message" *
| project clientReqId, timestamp, resultReason
| parse clientReqId with personId "_" paymentInstrumentId "_retry-" retry "_" epoch
| project responseDate = bin(timestamp,1d),personId, paymentInstrumentId, retry, requestDate = bin(unixtime_milliseconds_todatetime(tolong(epoch)),1d), resultReason
| where resultReason !in ("AU202","AU203","AU401","AU501","Z999")
| distinct requestDate, personId, paymentInstrumentId, responseDate, resultReason
| summarize receivedCount = count() by requestDate, responseDate;
let b=customEvents
| where cloud_RoleInstance startswith "sams-card-account-update"
| where name == "CAU_UPDATE_REQUEST"
| extend personId = tostring(customDimensions.personId)
| extend paymentInstrumentId = tostring(customDimensions.paymentInstrumentId)
| project requestDate = bin(timestamp,1d), personId, paymentInstrumentId
| distinct requestDate, personId, paymentInstrumentId
| summarize requestedCount = count() by requestDate;
a | join kind=inner b on requestDate
| project requestDate, responseDate, requestedCount, receivedCount
| project received_perc = receivedCount / todouble(requestedCount) * 100, responseDate, requestDate=substring(requestDate,0,10)
| sort by requestDate asc,responseDate asc
| render timechart

this is how the data look like 

{"kubernetes":{"labels":{"version":"v1","app":"card-account-update"},"pod_name":"card-account-update-5c4b875dc6-t7kzx","host":"stage-a6-vmss0003is","namespace_name":"n1497934467"},"time":"2023-03-25T09:05:16.023813003Z","log":{"timestamp":"2023-03-25T09:05:16.023Z","type":"trace","context":"default","thread":"org.springframework.kafka.KafkaListenerEndpointContainer#0-11-C-1","logger":"com.t.cau.integration.impl.CAUConsumer","level":"INFO","message":"Received Message from BST: BSTResponse{inquiryValue='20042341056', clientId='BSTclub.com', clientReqId='99f50c8e-4800-42af-b7ea-ccc9bf0a5349-4b40-9eea-aabfd0affec7_retry-2_1679735115331', status='ERROR', result='ERROR', resultReason='AU501', message='Unknown Error encountered', instrumentMap={xref=com.BST.payment.cau.integration.model.InstrumentInfo@6c3ad85}}"},"cluster_id":"wus-stage-a6"}

Thanks in advance

[Anidy21]

Please help me with the exact query for my result i tried after reading but not able to get the desired result .

[yuanliu]

As I and others said, the best way to get concrete help is to forget everything about SQL.  Instead, simply describe your use case.  What is it that you are trying to achieve?  Illustrate and explain data (anonymize as needed), explain and illustrate desired output, and describe, explain a logic connection between data and output.  Untangle complex SQL is unreasonable burden for volunteers here.

[woodcock]

Here is a start:

|makeresults
| eval _raw="{\"kubernetes\":{\"labels\":{\"version\":\"v1\",\"app\":\"card-account-update\"},\"pod_name\":\"card-account-update-5c4b875dc6-t7kzx\",\"host\":\"stage-a6-vmss0003is\",\"namespace_name\":\"n1497934467\"},\"time\":\"2023-03-25T09:05:16.023813003Z\",\"log\":{\"timestamp\":\"2023-03-25T09:05:16.023Z\",\"type\":\"trace\",\"context\":\"default\",\"thread\":\"org.springframework.kafka.KafkaListenerEndpointContainer#0-11-C-1\",\"logger\":\"com.t.cau.integration.impl.CAUConsumer\",\"level\":\"INFO\",\"message\":\"Received Message from BST: BSTResponse{inquiryValue='20042341056', clientId='BSTclub.com', clientReqId='99f50c8e-4800-42af-b7ea-ccc9bf0a5349-4b40-9eea-aabfd0affec7_retry-2_1679735115331', status='ERROR', result='ERROR', resultReason='AU501', message='Unknown Error encountered', instrumentMap={xref=com.BST.payment.cau.integration.model.InstrumentInfo@6c3ad85}}\"},\"cluster_id\":\"wus-stage-a6\"}"
|kv
```| search cloud_RoleInstance = "sams-card-account-update*" AND message = "*Received Message from CAU:*"```
| rex field=log.message "clientReqId='(?<clientReqId>.*?)', status=.*?resultReason='(?<resultReason>.*?)', message"

[Anidy21]

Hi @woodcock 

this is what my requirement and this is what i am getting as result of your query .please help me how will i get the desired result.Thanks in advance 🙂

Requirement

 Result from your query

 This is the index from which i need to fetch the records 

index="BTS-card-account-update"

[yuanliu]

As @woodcock said, the sample search is a start.  You may want to study this guide: Splunk SPL for SQL users.

[PickleRick]

@yuanliuNoooo! Don't point people to this dreadful document. I know that writers of that guide meant well but it's filled with bad practices and encourages users to write bad searches.

That guide should have a big red warning at the top "This is not how you should write SPL; you can use it for a one-off quickie but generally - don't write like that!"

[tscroggins]

I'll provide a friendly op-ed: Do read Splunk SQL for SQL Users if you're familiar with relational databases and SQL-like languages and need a Splunk primer. The example searches aren't optimal; however, the SELECT through ORDER BY examples provide the foundation for most general search use cases. (Use the where Splunk search command to compare two fields directly.) TRUNCATE and DELETE probably shouldn't have been included as the Splunk delete search command doesn't do the same thing. The examples using the join and append Splunk search commands are arguably anti-patterns, but do use them if they help you make sense of the problem you're trying to solve; you can optimize your searches after you have the correct outputs in hand for comparison.