Monthly distribution of query results

[jbrenner]

Hello,

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

index=abc "search string 1" | STATS COUNT AS ATTEMPTED_ORDERS | appendcols [search index=abc "search string 2" | STATS COUNT AS SUCCESSFUL_ORDERS] | eval PERCENT_SUCCESSFUL = (SUCCESSFUL_ORDERS/ATTEMPTED_ORDERS) * 100 | TABLE PERCENT_SUCCESSFUL

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

[woodcock]

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

[DalJeanis · Accepted solution]

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) 

[jbrenner]

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

[woodcock]

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

[woodcock]

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.

[DalJeanis]

@woodcock - How DARE you fix my sloppy code?

The NERVE of some people.

Heh.

[jbrenner]

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

[woodcock]

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

[DalJeanis]

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.

[woodcock]

Good point, I have updated my answer.

[sbbadri]

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

[DalJeanis]

@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."