How to refine this query

[avii7326]

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

    ]

[scelikok]

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)

[gcusello]

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

[avi7326]

Hi @gcusello 

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


Thanks

[gcusello]

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

[avi7326]

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