How to find the difference of two stats count

[avi7326]

I am having two counts in the dashboard one is the total count and other is error count to get the success count I want the difference. How can we do that.

index=US_WHCRM_int   (sourcetype="bmw-crm-wh-xl-cms-int-api" severity=INFO ("*Element*: bmw-cm-wh-xl-cms-contractWithCustomers-flow/processors/2/processors/0 @ bmw-crm-wh-xl-cms-int-api:bmw-crm-wh-xl-cms-api-impl/bmw-cm-wh-xl-cms-contractWithCustomers*") OR

"*flow started put*contractWithCustomers" OR "*flow started put*customers:application*" OR "ERROR Message" OR "flow started put*contracts:application*") OR (sourcetype="bmw-crm-wh-xl-cms-int-api" severity=ERROR "Error Message")

| rex field=message "(?<json_ext>\{[\w\W]*\})"

| rex field=message "put:\\\\(?<Entity>[^:]+)"

| rename attributes{}.value.details as details

| rename properties.correlationId as correlationId

| table _time properties.* message json_ext details Entity

| spath input=json_ext

| stats count by Entity


Using
| stats count by Entity and | stats count by title  I am getting two counts how can I find the difference between the Entity and title count

[ITWhisperer]

Your requirement is unclear - if you have a count by "Entity A" for example, which total count do you want to use to find the difference, "Total X", "Total Y" or "Total Z", and vice versa

[avi7326]

From the query using stats count by entity getting A and then using stats count by title getting B then I want a difference  A-B count then what should I use.

[ITWhisperer]

Since you are using count by Entity, you will get multiple counts, one for each unique Entity. Similarly, since you are using count by title, you will get multiple counts, one for each unique title. Which Entity count do you want to compare with which title count?

[avi7326]

Is there any way to merge query A  and Query B , Both these two queries are different .
Query A 
|stats count as total 
result = 5

Query B 
|stats count as error
result=3 

Now I want a difference 5-3 =2  as success count

[PickleRick]

Again, your requirement is a bit unclear. While there is a possibility of use eventstats in general case as @ITWhisperer showed, the command might be quite resource-intensive, especially over a big data set so you might want to rethink what you really need because sometimes it's better to calculate some partial sums and creatively aggregate them to get what you need - this approach may in many cases prove to be way way more efficient.

[ITWhisperer]

| eventstats count as total
| stats count as error values(total) as total
| eval difference=total-error

[avi7326]

How should I join both two queries.

[ITWhisperer]

Depending on the size of your searches, you could try this

<search A>
| stats count as total
| append [search <search B>
         | stats count as error]
| stats values(total) as total, values(error) as error
| eval difference=total - error

[avi7326]

index=US_WHCRM_int   sourcetype="bmw-crm-wh-xl-cms-int-api" ("*Element*: bmw-cm-wh-xl-cms-contractWithCustomers-flow/processors/2/processors/0 @ bmw-crm-wh-xl-cms-int-api:bmw-crm-wh-xl-cms-api-impl/bmw-cm-wh-xl-cms-contractWithCustomers*") OR

"*flow started put*contractWithCustomers" OR "*flow started put*customers:application*" OR "ERROR Message" OR "flow started put*contracts:application*"

| rex field=message "(?<json_ext>\{[\w\W]*\})"

| rex field=message "put:\\\\(?<Entity>[^:]+)"

| rename attributes{}.value.details as details

| rename properties.correlationId as correlationId

| table _time properties.* message json_ext details Entity

| spath input=json_ext

| stats count as Entity

| append

    [ search index=US_WHCRM_int (sourcetype="bmw-crm-wh-xl-cms-int-api" severity=INFO ("*Element*: bmw-cm-wh-xl-cms-contractWithCustomers-flow/processors/2/processors/0 @ bmw-crm-wh-xl-cms-int-api:bmw-crm-wh-xl-cms-api-impl/bmw-cm-wh-xl-cms-contractWithCustomers*") OR

"*flow started put*contractWithCustomers" OR "*flow started put*customers:application*" OR "ERROR Message" OR "flow started put*contracts:application*") OR (sourcetype="bmw-crm-wh-xl-cms-int-api" severity=ERROR "Error Message")

| rex field=message "(?<json_ext>\{[\w\W]*\})"

| rex field=message "put:\\\\(?<Entity>[^:]+)"

| rename attributes{}.value.details as details

| rename properties.correlationId as correlationId

| table _time properties.* message json_ext details Entity

| spath input=json_ext

| stats count by title

| fields count]

| stats values(Entity) as Entity values(title) as title

| eval success=title-Entity

I am using this query but not getting the correct count please help me with this.
Or there is any other option to find the difference between those two counts.

[PickleRick]

Ugh. There is so much going on here that I don't know where to start.

1. Don't use wildcards at the beginning of your search term. It kills your searches performance-wise.

2. You're doing the same (very inefficient) base search twice. That's not the best idea.

3. You needlessly extract many fields but in the end only do

stats count as Entity

or

stats count by title

 4. First search gives you one number as a result, the appended search (which will most probably get silently finalized due to exceeding permitted subsearch time and will return _wrong_ results) returns several numbers - one for each title.

It seems you don't need any "merging" of two searches but need to design your search from the ground up to get the results you need. But to do so you need to know (and tell us if you want us to help):

1) What does your data look like

2) What do you want to achieve

[avi7326]

I am not using a base search but using two different queries to get the exact count what I want . So one query is this 

index=US_WHCRM_int   sourcetype="bmw-crm-wh-xl-cms-int-api" ("*Element*: bmw-cm-wh-xl-cms-contractWithCustomers-flow/processors/2/processors/0 @ bmw-crm-wh-xl-cms-int-api:bmw-crm-wh-xl-cms-api-impl/bmw-cm-wh-xl-cms-contractWithCustomers*") OR

"*flow started put*contractWithCustomers" OR "*flow started put*customers:application*" OR "ERROR Message" OR "flow started put*contracts:application*"

| rex field=message "(?<json_ext>\{[\w\W]*\})"

| rex field=message "put:\\\\(?<Entity>[^:]+)"

| rename attributes{}.value.details as details

| rename properties.correlationId as correlationId

| table _time properties.* message json_ext details Entity

| spath input=json_ext

| stats count as Entity
which is giving me entity as total fetch count.
Other query is this 

index=US_WHCRM_int (sourcetype="bmw-crm-wh-xl-cms-int-api" severity=INFO ("*Element*: bmw-cm-wh-xl-cms-contractWithCustomers-flow/processors/2/processors/0 @ bmw-crm-wh-xl-cms-int-api:bmw-crm-wh-xl-cms-api-impl/bmw-cm-wh-xl-cms-contractWithCustomers*") OR

"*flow started put*contractWithCustomers" OR "*flow started put*customers:application*" OR "ERROR Message" OR "flow started put*contracts:application*") OR (sourcetype="bmw-crm-wh-xl-cms-int-api" severity=ERROR "Error Message")

| rex field=message "(?<json_ext>\{[\w\W]*\})"

| rex field=message "put:\\\\(?<Entity>[^:]+)"

| rename attributes{}.value.details as details

| rename properties.correlationId as correlationId

| table _time properties.* message json_ext details Entity

| spath input=json_ext

| stats count by title

| fields count

which is giving me title count as error count.
Now I want a success count which can be calculated by subtracting the total fetch count - error count. So how I will get that. Please help me with that. Hope this helps you to understand.

[PickleRick]

Ok. Again. One search gives you a single number. Another search returns several numbers (depending on how many titles you have in your data). What do you want to substract from what?

And again - why extract so many fields when in the end you're just doing stats count?