topic Re: How to write this query in Splunk? in Splunk Search

How to write this query in Splunk?

Anidy21 — Thu, 06 Apr 2023 17:23:51 GMT

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

Re: How to write this query in Splunk?

woodcock — Fri, 07 Apr 2023 01:06:19 GMT

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"

Re: How to write this query in Splunk?

Anidy21 — Fri, 07 Apr 2023 03:33:28 GMT

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"

Re: How to write this query in Splunk?

yuanliu — Sat, 08 Apr 2023 06:52:18 GMT

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

Re: How to write this query in Splunk?

PickleRick — Mon, 10 Apr 2023 17:20:38 GMT

@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!"

Re: How to write this query in Splunk?

tscroggins — Mon, 10 Apr 2023 19:37:15 GMT

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.

Re: How to write this query in Splunk?

Anidy21 — Thu, 20 Apr 2023 04:59:58 GMT

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

Re: How to write this query in Splunk?

yuanliu — Thu, 20 Apr 2023 05:19:16 GMT

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.