topic How to refine this query in Splunk Dev

How to refine this query

avii7326 — Wed, 08 May 2024 16:48:19 GMT

How should I refine this query so that I can get every fields in one table without using join or append or any other sub search.

(index=whcrm OR index=whcrm_int)sourcetype="bmw-sl-gcdm-int-api" ("Sending POST consents to *" OR "Create / Update Consents done" OR "Error in sync-consent-dataFlow:*") | stats count(eval(match(_raw, "Sending POST consents to *"))) as Total, count(eval(match(_raw, "Create / Update Consents done"))) as Success, count(eval(match(_raw, "Error in sync-consent-dataFlow:*"))) as Error | eval ErrorRate = round((Error / TotalReceived) * 100, 2) | table Total, Success, Error, ErrorRate | append [ search (index=whcrm OR index=whcrm_int) (sourcetype="bmw-sl-gcdm-int-api" ("Sending POST consents to *" OR "Create / Update Consents done" OR "Error in sync-consent-dataFlow:*")) | rex field=message ": (?<json>\{[\w\W]*\})$" | rename properties.correlationId as correlationId | rename properties.gcid as GCID | rename properties.gcid as errorcode | rename properties.entity as entity | rename properties.country as country | rename properties.targetSystem as target_system | table correlationId GCID errorcode entity country target_system ]

Re: How to refine this query

gcusello — Wed, 08 May 2024 16:09:15 GMT

Hi @avii7326,

sorry but I don't understand the purpose of this search:

you have the same search in the first part, with results aggregated using statsm so in one row you have three values Total Success and Error.

Then in the append search, using the same search, you have many events listed with the table command.

And there isn't any correlation between the two parts of the search.

What's the output that you would have?

Ciao.

Giuseppe

Re: How to refine this query

avi7326 — Fri, 10 May 2024 14:33:44 GMT

Using these two searches because I want to extract some fields using that regular expression for that only I am appending it. I want help in this only so that I don't repeat this search two times and have one query in table with fields - total ,success, error, correlationid, GCID etc.

Or If I am using wrong query you can suggest me how to proceed -
I have that logs and have to count those logs for total ,success and error and these fields will be used if there will be any error to show the details of that error this GCID correlationId will be required. Please guide how can I proceed.

Thanks in Advance

Re: How to refine this query

avi7326 — Mon, 13 May 2024 13:13:39 GMT

Hi @gcusello

Can you please help me on this as I have explained my requirement, as it is bit urgent for me.

Thanks

Re: How to refine this query

scelikok — Mon, 13 May 2024 13:45:32 GMT

Hi @avi7326,

Please try below query;

(index=whcrm OR index=whcrm_int) sourcetype="bmw-sl-gcdm-int-api" ("Sending POST consents to *" OR "Create / Update Consents done" OR "Error in sync-consent-dataFlow:*") | eval Total= if(match(_raw, "Sending POST consents to *"),1,0) | eval Success= if(match(_raw, "Create / Update Consents done"),1,0) | eval Error= if(match(_raw, "Error in sync-consent-dataFlow:*"),1,0) | rex field=message ": (?<json>\{[\w\W]*\})$" | rename properties.correlationId as correlationId | rename properties.gcid as GCID | rename properties.gcid as errorcode | rename properties.entity as entity | rename properties.country as country | rename properties.targetSystem as target_system | stats sum(Total) as Total sum(Success) as Success sum(Error) as Error by correlationId GCID errorcode entity country target_system | eval ErrorRate = round((Error / Total) * 100, 2)

Re: How to refine this query

gcusello — Mon, 13 May 2024 14:30:12 GMT

Hi @avi7326 ,

as I said, there's no sense to put in the same panel a result from a stats search and a table .

use your searches in two different panels.

Ciao.

Giuseppe