Splunk Search

How to find the difference of two stats count

avi7326
Path Finder

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

Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

avi7326
Path Finder

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

avi7326
Path Finder

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

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eventstats count as total
| stats count as error values(total) as total
| eval difference=total-error
0 Karma

avi7326
Path Finder

How should I join both two queries.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

avi7326
Path Finder

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.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

avi7326
Path Finder

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.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

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?

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...