topic Re: Monthly distribution of query results in Splunk Search

Monthly distribution of query results

jbrenner — Tue, 29 Sep 2020 14:59:33 GMT

Hello,

I have the following query which gives me the percentage of successful orders for the time period selected in the drop-down:

I would like to use the timechart keyword to obtain this value (PERCENT_SUCCESSFUL) for each day over the last month, so I can display it as a chart.

Thanks!
Jonathan

Re: Monthly distribution of query results

sbbadri — Tue, 29 Sep 2020 14:59:39 GMT

try below

index=abc earliest=-30d@d latest=now "search string 1" | STATS COUNT AS ATTEMPTED_ORDERS | appendcols [search index=abc earliest=-30d@d latest=now "search string 2" | STATS COUNT AS SUCCESSFUL_ORDERS] | eval PERCENT_SUCCESSFUL = (SUCCESSFUL_ORDERS/ATTEMPTED_ORDERS) * 100 | timechart span=1d count by PERCENT_SUCCESSFUL

Re: Monthly distribution of query results

woodcock — Thu, 20 Jul 2017 20:30:46 GMT

Try this:

index=abc "search string 1"
| timechart span=1d count AS ATTEMPTED_ORDERS
| appendcols [search
   index=abc "search string 2" 
   | timechart span=1d count AS SUCCESSFUL_ORDERS]
| stats values(*) AS * BY _time
| eval PERCENT_SUCCESSFUL = (coalesce(SUCCESSFUL_ORDERS, 0)/coalesce(ATTEMPTED_ORDERS, SUCCESSFUL_ORDERS)) * 100
| table _time PERCENT_SUCCESSFUL

Re: Monthly distribution of query results

DalJeanis — Thu, 20 Jul 2017 20:41:22 GMT

Try this -

| multisearch 
    [search index=abc "search string 1"
     | eval  Attempts = 1
     | fields Attempts 
    ] 
    [search index=abc "search string 2"
     | eval Successes = 1
     | fields Successes 
    ]
| bin _time span=1d
| stats sum(*) as * by _time
| eval Attempts=if(Successes>Attempts, Successes, Attempts)
| eval Percent_Successful = round(100*Successes/Attempts,0)
| timechart span=1d max(Percent_Successful)

Re: Monthly distribution of query results

DalJeanis — Tue, 29 Sep 2020 15:01:59 GMT

@sbbadri - Not a bad try, but that's not going to work because the initial stats command has a single result, not one result per day, so there is no field _time remaining to go into the timechart command.

Go ahead and try again, you're on the right track. (no peeking at my version or @woodcock's version, they are not perfect so there's plenty of room for you to do better than us.

Also, when you change your strategy to fix the _time issue, I would suggest avoiding appendcols since it will match up the records based on their order rather than based on their dates. You'll need to bin the _time to days match them up by _time either with a stats command or a join.

I said "don't peek at woodcock's."

Re: Monthly distribution of query results

DalJeanis — Thu, 20 Jul 2017 20:51:18 GMT

Hey, under what circumstances is timechart ensured of coming up with the same start date for those two (sub) searches? I'm thinking that if there are no "search string 2" on the first day that there is a "search string 1", that the records will be matched up wrong.

Re: Monthly distribution of query results

woodcock — Thu, 20 Jul 2017 21:02:52 GMT

Good point, I have updated my answer.

Re: Monthly distribution of query results

jbrenner — Mon, 24 Jul 2017 17:16:57 GMT

Hi Woodcock,

Thanks for the answer.
When I run your query, I get the following error:

Error in 'bin' command: Invalid argument: 'span'

Thanks!
Jonathan

Re: Monthly distribution of query results

woodcock — Tue, 25 Jul 2017 15:04:03 GMT

Formatting error; get rid of the spaces around the equals-sign span=1d. If updated my answer to fix this.

Re: Monthly distribution of query results

woodcock — Tue, 25 Jul 2017 15:06:43 GMT

Sorry @DalJeanis; I didn't check to see if OP was right about his attribution to me (he wasn't) until after I edited your answer. I also pulled the bin out of each search.

Re: Monthly distribution of query results

jbrenner — Tue, 25 Jul 2017 15:15:09 GMT

Sorry for the confusion. Thanks, @DalJeanis. Your solution works!

Re: Monthly distribution of query results

woodcock — Tue, 25 Jul 2017 15:26:13 GMT

A more efficient answer (all in 1 search) is this:

index=abc "search string 1" OR "search string 2"
| bin _time span=1d
| stats count(eval(searchmatch("search string 1"))) AS ATTEMPTED_ORDERS
        count(eval(searchmatch("search string 2"))) AS SUCCESSFUL_ORDERS BY _time
| eval PERCENT_SUCCESSFUL = (coalesce(SUCCESSFUL_ORDERS, 0)/coalesce(ATTEMPTED_ORDERS, SUCCESSFUL_ORDERS)) * 100
| table _time PERCENT_SUCCESSFUL

Re: Monthly distribution of query results

DalJeanis — Tue, 25 Jul 2017 17:32:12 GMT

@woodcock - How DARE you fix my sloppy code?

The NERVE of some people.

Heh.