topic Re: How do I substract the results of two different searches including sseabalytics ? in Splunk Search

How do I substract the results of two different searches including sseabalytics ?

AcePilot — Fri, 16 Aug 2024 21:12:51 GMT

Im trying to substract the total number i have of alerts that send and email from the total amount of alerts that are bookmarked in SSE. The only examples I found on the community used either the same index, or sub-searches (neither worked in my scenario)

My query for the alerts is :

| rest /services/saved/searches | search alert_type!="always" AND action.email.to="production@email.com" AND title!="*test*" | stats count(action.email.to) AS "Count"

My query for bookmarks is:

| sseanalytics 'bookmark' | where bookmark_status="successfullyImplemented" | stats count(bookmark_status_display) AS "Bookmark Status" by bookmark_status_display

Re: How do I substract the results of two different searches including sseabalytics ?

yuanliu — Sat, 17 Aug 2024 06:10:49 GMT

You realize that the first search results in one single row, and the second gives a series of rows, right? Without illustrating or describing what your desired output look like, you are asking volunteers to read your mind. This is generally a bad idea on a forum like this.

If your requirement is to subtract singular Count in the first search from "Bookmark Status" in every row in the second search, you can do something as simple as

| rest /services/saved/searches | search alert_type!="always" AND action.email.to="production@email.com" AND title!="*test*" | stats count(action.email.to) AS "Count" | appendcols [sseanalytics 'bookmark' | where bookmark_status="successfullyImplemented" | stats count(bookmark_status_display) AS "Bookmark Status" by bookmark_status_display] | eventstats values(Count) as Count | eval diff = 'Bookmark Status' - Count

Here I am using appendcols instead of the usual approach using append because one of the searches only gives out one single row. This is not the most semantic approach but sometimes I like code economy. In fact, this method applies to any two searches as long as one of them yields a single row.

Here is an emulation as proof of concept:

| tstats count AS Count where index=_internal ``` the above emulates | rest /services/saved/searches | search alert_type!="always" AND action.email.to="production@email.com" AND title!="*test*" | stats count(action.email.to) AS "Count" ``` | appendcols [tstats count AS "Bookmark Status" where index=_introspection by sourcetype | rename sourcetype AS bookmark_status_display ``` this subsearch emulates | sseanalytics 'bookmark' | where bookmark_status="successfullyImplemented" | stats count(bookmark_status_display) AS "Bookmark Status" by bookmark_status_display ``` ] | eventstats values(Count) as Count | eval diff = 'Bookmark Status' - Count

You will get something like

Count	Bookmark Status	bookmark_status_display	diff
151857	201	http_event_collector_metrics	-151656
151857	2365	kvstore	-149492
151857	57	search_telemetry	-151800
151857	462	splunk_disk_objects	-151395
151857	303	splunk_telemetry	-151554

Re: How do I substract the results of two different searches including sseabalytics ?

PickleRick — Sat, 17 Aug 2024 07:03:10 GMT

Apart from the technicalities which @yuanliu already tackled, there is also a logical flaw in your approach. Even if you aggregate your second search output into a single count you have two relatively unrelated values. Substracting cardinalities makes sense only if one set is a subset of another one. In your case those sets may overlap but one doesn't have to be included in the other.

Re: How do I substract the results of two different searches including sseabalytics ?

AcePilot — Mon, 19 Aug 2024 18:02:22 GMT

Hi thanks for pointing that out.
The "by bookmark_status_display" was indeed unneeded as I'm specifying which status it is in the query hence the actual query should be:

| sseanalytics 'bookmark' | where bookmark_status="bookmarked" | stats count(bookmark_status_display) AS "Bookmark Status"

- Once taking that into considereation i was able to use the following for the result :

| rest /services/saved/searches | search alert_type!="always" AND action.email.to="production@email.com" AND title!="*test*" | stats count(action.email.to) AS "Count" | appendcols [sseanalytics 'bookmark' | where bookmark_status="successfullyImplemented" | stats count(bookmark_status_display) AS "Bookmark Status"] | eventstats values(Count) as Count | eval diff = 'Bookmark Status' - Count | table diff

Thank you 100!