How do I change my current search with multiple subsearches into a time chart?

[john_byun]

The following query works for a specific time period.

eventtype=A | stats count |join type=outer [search eventtype=B | transaction host maxspan=3m  | stats count as B_Count] | join type=outer [search eventtype=C | stats count as B_Count]  | sort -count | eval B_Rate=(B_Count/count)*100 | fieldformat B_Rate=tostring(round(B_Rate,2))+"%" | eval C_Rate=(C_Count/count)*100 | fieldformat C_Rate=tostring(round(C_Rate,2))+"%" | table count, B_Count, B_Rate, C_Count, C_Rate 

Results:
Total number of A events
Total number of B events
B divided by A%
Total number of C events
C divided by A%

How can I make this into a timechart that will provide results over time? For example, I would like the above results for every day for the last 7 days in a single table. I can do this manually by running the above search 7 times for each day, but I would like to do this in single search.

[Richfez · Accepted solution]

If I'm reading your search right, then I think all you need is to insert a bin command in there after your searches/joins but before your eval, and tell it to split your data into chunks of _time one day each.

...| sort -count | bin _time span=1d | eval B_Rate=... 

I'd like to be more specific, but I have two addendums:

First, I am assuming there's a few typos from a "cleanup" of the search, for instance B_Count it in there an extra time when I think it should be C_Count. This isn't particularly a problem for this answer, though, I just note it in case you didn't and to explain it's part of the reason I didn't want to include the entire search again in this answer.

Second, as with the last note this next one is OK for now; just something to consider and keep in mind if this starts running slowly or if you want to make it faster (and likely better). If you check the Docs page for join, you'll see they expound quite thoroughly on the alternatives to join. I think this page is the only command they do that with, at least to this extent. The reason is that joins are very expensive and slow and only rarely actually needed. I recommend removing the joins and doing your search in a different way, but without seeing more detail about the events you are joining together I can't offer much in the way of good suggestions. There are some absolute "join-removing" experts in here, wizards that can remove joins from nearly anything (and speed up those queries sometimes by staggering amounts in the process). If that interests you some day, please post another question asking for help! Of course, if your search runs in under a second or two, it may not be worth worrying about.

[john_byun]

I've corrected the typo, replaced join with append, replaced stats with timechart, and replaced table with stats first(*) as * by _time.

My results are much closer now, but the 2 Rate calculations are coming up as Null%. Any idea what else I would need to add to make this work?

eventtype=A | timechart count span=1d | append [search eventtype=B | transaction host maxspan=3m | timechart count as B_Count span=1d] | append [search eventtype=C | timechart count as C_Count span=1d] | eval B_Rate=(B_Count/count)100 | fieldformat B_Rate=tostring(round(B_Rate,2))+"%" | eval C_Rate=(C_Count/count)*100 | fieldformat C_Rate=tostring(round(C_Rate,2))+"%" | stats first() as * by _time

[Richfez]

Ah! I should have noticed before but you have used append, which acts a lot like a SQL union and makes new rows. This is probably why your evals don't work, the items are all in different rows (which the method for debugging this I outline elsewhere in this would have pointed out). See example 2 here.

Instead you will need appendcols, which adds fields into the rows you already have.

Try replacing your append with appendcols wherever they appear.

If you continue to have problems, proceed like I mention and let us know at which point it does unexpected things, though it sounds like you'll be able to figure out the details on your own well enough.

[john_byun]

rich7177, thanks so much for your help.

I was able to get the query working by moving my eval commands to go after my stats.

eventtype=A | timechart count span=1d | append [search eventtype=B | transaction host maxspan=3m | timechart count as B_Count span=1d] | append [search eventtype=C | timechart count as C_Count span=1d] | stats first() as by _time | eval B_Rate=(B_Count/count)100 | fieldformat B_Rate=tostring(round(B_Rate,2))+"%" | eval C_Rate=(C_Count/count)100 | fieldformat C_Rate=tostring(round(C_Rate,2))+"%"

[Richfez]

Wow. I wasn't expecting such a thorough and quick rewrite. 🙂 Nicely done.

OK, so looking at that... Let's break it down a bit. (I also added a rename on the first count just to stay consist). Running just this:

eventtype=A | timechart count AS A_Count span=1d 
| append [search eventtype=B | transaction host maxspan=3m | timechart count as B_Count span=1d] 
| append [search eventtype=C | timechart count as C_Count span=1d]

gets you rows like

A_Count, B_Count, C_ Count
57, 98, 14
64, 108, 17

Correct? So now we've confirmed we have numbers in each field, add on on at a time, because there's no need to try to troubleshoot them all at once.

eventtype=A | timechart count AS A_Count span=1d 
| append [search eventtype=B | transaction host maxspan=3m | timechart count as B_Count span=1d] 
| append [search eventtype=C | timechart count as C_Count span=1d]
| eval B_Rate=(B_Count/A_Count)*100

That should, I hope, give you

A_Count, B_Count, C_ Count, B_Rate
57, 98, 14, 1.876565
64, 108, 17, 1.9265665

(I just squinted real hard and guessed on the rates, they're not mathamatically accurate.) There's no reason that shouldn't work. Don't worry about your fieldformats yet. If those are working then go back and add the fieldformat.

Speaking of which, you can likely do the fieldformat all at once like this:

...| foreach *_Rate  [fieldformat <<FIELD>>=tostring(round('<<FIELD>>',2))] ...

That's likely close to correct, but check the docs and examples here.