Splunk Search

How do I edit my search to find the difference between two fields?

sunnyparmar
Communicator

Hi,

I have a search given below. All is working fine, but in last I want to sort out difference between total-acknowledged which I am not getting, so please suggest here.

(host="e2e-onp-front" response="Message acknowledged successfully*")    OR      (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*|rex "Successfully (?<response>created) notification"|eval notifications=if(match(response,"created"),"      Total", "Acknowledged")| stats  count by notifications | eval Diff = Total - Acknowledged

Thanks in advance

Tags (3)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

Try a bit different approach. Join the eval in the stats, something like below. You might need to adjust for your final requirements

 (host="e2e-onp-front" response="Message acknowledged successfully*")      OR        (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*|rex "Successfully (?<response>created) notification"|stats count(eval(like(response,"%created%"))) AS Total, count(eval(NOT like(response,"%created%"))) as Acknowledge| eval Diff = Total - Acknowledged
Happy Splunking!

View solution in original post

rakeshh123
Path Finder

Hello sunnyparmer,
i just changed order of the queries and changed stats to eventstats in the query ........I actually worked on my data with a similar query it is working fine..this is just due to the fact stats cannot pass data to another stats command in chain you have to use eventstats for that....i am sending screenshot of my query and data....
alt text

(host="e2e-onp-front" response="Message acknowledged successfully*") OR (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=|rex "Successfully (?created) notification"|eventstats count by notifications*|stats count(eval(like(response,"%created%"))) AS Total, count(eval(NOT like(response,"%created%"))) as Acknowledge| eval Diff = Total - Acknowledged...
Let me know if it works

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Try a bit different approach. Join the eval in the stats, something like below. You might need to adjust for your final requirements

 (host="e2e-onp-front" response="Message acknowledged successfully*")      OR        (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*|rex "Successfully (?<response>created) notification"|stats count(eval(like(response,"%created%"))) AS Total, count(eval(NOT like(response,"%created%"))) as Acknowledge| eval Diff = Total - Acknowledged
Happy Splunking!

sunnyparmar
Communicator

Thanks.. You are close enough but displaying result is not exact.. Could you please rearrange these?

  1. | stats count by notifications | delta count as diff p=1

notification count diff
Acknowledged 38
Total 2 36

  1. your search| stats count by notification|transpose 2|rename "row 1" as Acknowledged,"row 2" as Total|eval Diff=Total-Acknowledged|search column=count|fields - column

Acknowledged Total Diff
2 38 36

  1. your search |stats count by notification|streamstats last(count) as newcount current=f|eval Diff=newcount-count|eventstats last(Diff) as Diff|fields - newcount

notifications count Diff
Acknowledged 2 -36
Total 38 -36

0 Karma

sunnyparmar
Communicator

second one is more appropriate but the issue is notification column is vanished from it.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

In the second one, the notification is transposed as Acknowledged and Total and the correspoding counts are shown under each column. Where do you wantto display notification now? How should be your final result looks like?

Happy Splunking!
0 Karma

sunnyparmar
Communicator

final result will be look like -

notification Counts
Total 38
Acknowledged 2
Difference 36

Thanks

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Alright. Try this

     (host="e2e-onp-front" response="Message acknowledged successfully*") OR (host= "e2e-onp-bl-db" DocumentNotificationHookServiceImpl Successfully created notification ) org=*
     |rex "Successfully (?<response>created) notification"|eval notifications=if(match(response,"created"),"Total", "Acknowledged")
     |stats  count by notifications
     |delta count as Difference p=1| appendpipe [|stats values(Difference) as Difference|eval notifications="Difference"|eval count=Difference]|fields - Difference
Happy Splunking!

sunnyparmar
Communicator

Great buddy.. thanks a ton...

0 Karma

sunnyparmar
Communicator

thanks for replying but still getting no difference with the query. Even by executing your query i have lost my notification column under which "Total" and "Acknowledged" parameters showing previously. Now it is showing only Total=38 and Acknowledged=2 columns. Notification column is vanished.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Diff is not showing since there is a typo ie : | eval Diff = Total - Acknowledge

In your first search , the Total and Acknowledged are on diff rows (ie: stats count by notification). So where do you want to dislpay the difference?

Happy Splunking!
0 Karma

sunnyparmar
Communicator

i want to display it in the dashboard result.. Currently i am getting two columns. first is notifications under which i am getting "Total" and "Acknowledged" values and other one is count column under which i am getting "38" and "2" values respectively so now i want third column in which it will show the difference of Total-Acknowledged.

Thanks

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Yes, got it, Your result is

notification                  count
Acknowledged            38
Total                             2

So in third column, which row you want to display the diff ?

  1. Easiest method is using delta ie : | stats count by notifications | delta count as diff p=1

  2. If you want to transpose the complete table, then use

    your search| stats count by notification|transpose 2|rename "row 1" as Acknowledged,"row 2" as Total|eval Diff=Total-Acknowledged|search column=count
    |fields - column

  3. If you want to print diff in all columns

    your search |stats count by notification|streamstats last(count) as newcount current=f|eval Diff=newcount-count|eventstats last(Diff) as Diff|fields - newcount

Happy Splunking!
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Are you sure your base query is producing events with Total and Acknowledged fields? Without the fields, a difference cannot be computed.

---
If this reply helps you, Karma would be appreciated.
0 Karma

sunnyparmar
Communicator

thanks for answering.. yes my base query is production events with total=38 and acknowledged=2 so i want the rest of the difference 36 to be shown in dashboard with these two values.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Case is significant with field names. If the names are "total" and "acknowledged" then your query must be ... | eval Diff = total - acknowledged.

---
If this reply helps you, Karma would be appreciated.
0 Karma

sunnyparmar
Communicator

in both words first alphabet are capital like i have pasted in my own query.

| eval Diff = Total - Acknowledged

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...