topic Re: Combine the two queries and calculate count in Splunk Search

Combine the two queries and calculate count

zork67 — Thu, 21 Sep 2017 15:35:19 GMT

Hello experts.

I tried to execute the query, as described here https://answers.splunk.com/answers/106906/how-to-perform-math-on-single-values.html

In my case, too, there are two requests.

1st search:

index=ns SUBMIT_SM  REQUEST host="notif*" | rex field=_raw "CID\:(?<CID>.*)\ actor-id" | dedup CID 
 | stats count as part

2nd search:

index=ns SUBMIT_SM  REQUEST host="notif*" | stats count as uniq

I tried to combine these requests into one to calculate the ratio

  | multisearch
       [ search index=ns SUBMIT_SM  REQUEST host="notif*" | rex field=_raw "CID\:(?<CID>.*)\ actor-id" | dedup CID 
         | eval marker="s" ]
       [ search index=ns SUBMIT_SM  REQUEST host="notif*" 
         | eval marker="o" ]
     | stats count(eval(marker=="s")) as part
             count(eval(marker=="o")) as uniq
     | eval velocity=(part/uniq)*100)

I receive an error:

Error in 'multisearch' command: Multisearch subsearches may only contain purely streaming operations (subsearch 1 contains a non-streaming command.)
The search job has failed due to an error. You may be able to see the job in the Job Inspector.

I tried it differently

index=ns SUBMIT_SM  REQUEST host="notif*" 
 | stats count as part 
 | append [ search index=ns SUBMIT_SM  REQUEST host="notif*" | rex field=_raw "CID\:(?<CID>.*)\ actor-id" | dedup CID | stats count as uniq] | eval velocity=part/uniq

But velocity was not calculated

Help

Re: Combine the two queries and calculate count

somesoni2 — Thu, 21 Sep 2017 16:25:34 GMT

I would do like this (as both have same base search)

index=ns SUBMIT_SM  REQUEST host="notif*" | rex field=_raw "CID\:(?<CID>.*)\ actor-id"
| stats dc(CID) as part count as uniq
| eval velocity=(part/uniq)*100

Re: Combine the two queries and calculate count

lfedak_splunk — Fri, 22 Sep 2017 00:12:39 GMT

Hey @zork67, if @somesoni2 solved your problem, please don't forget to accept an answer! You can upvote posts as well. (Karma points will be awarded for either action.) Happy Splunking!

Re: Combine the two queries and calculate count

zork67 — Fri, 22 Sep 2017 09:37:43 GMT

Thanks, but still an error

Error in 'eval' command: The expression is malformed.
The search job has failed due to an error. You may be able view the job in the Job Inspector.

Re: Combine the two queries and calculate count

somesoni2 — Fri, 22 Sep 2017 14:37:09 GMT

There is an additional braces in the eval, removed. (search was copied from question so didn't see that)

Re: Combine the two queries and calculate count

zork67 — Fri, 22 Sep 2017 14:40:23 GMT

I decided this way:

index=ns SUBMIT_SM  REQUEST host="notif*" 
| stats count as part 
| appendcols [search index=ns SUBMIT_SM  REQUEST host="notif*" 
| rex field=_raw "CID\:(?<CID>.*)\ actor-id" 
| dedup CID
| stats count(CID) as uniq ]
| eval ratio=part/uniq
| fields ratio

Thanks to all

Re: Combine the two queries and calculate count

richgalloway — Fri, 22 Sep 2017 15:20:56 GMT

@zork67 If your problem is resolved, please accept an answer (it's OK to accept your own answer) to help future readers.