How can I use timechart with Where condition and stats?

[Alanmas]

Hello!

I am trying to figure out how to convert an table query into a histogram using timechart(), but I am having issues as no data is flowing (I read that is because when you use stats the value of _time disappear or something).

Here is my old query:

index="something" source="*-value*" ("random value 1" OR "*random value 2*")
| stats count(eval(match(_raw, "random value 1"))) as value_1,
       count(eval(match(_raw, "random value 2"))) as value_2
       by source
| where value_1 > 0 AND value_2 > 0
| table source

And this is what I have so far:

index="something" source="*-value*" ("random value 1" OR "*random value 2*")
| stats count(eval(match(_raw, "random value 1"))) as value_1,
       count(eval(match(_raw, "random value 2"))) as value_2
       by source
| where value_1 > 0 AND value_2 > 0
| timechart span=1d dc(source) as unique_sources

But not data is flowing, I already tried other ways and I am sure should be something easy that I am not able to figure out 😞

[Alanmas]

@yeahnah and just out of curiosity, is it possible to create a Multi-Series Line Chart using 2 different queries that works by themselves?

For example:
1 line:

index="something" source="*-value*" ("random value 1" OR "*random value 2*")
| bin span=1d _time
| stats count(eval(match(_raw, "random value 1"))) as value_1,
       count(eval(match(_raw, "random value 2"))) as value_2
       by _time source
| where value_1 > 0 AND value_2 > 0
| timechart span=1d dc(source) as unique_sources

2nd line chart:

index="something" source="*-value*" ("random value 1" OR "*random value 3*" OR "*random value 4*" OR "*random value 5*")
| bin span=1d _time
| stats count(eval(match(_raw, "random value 1"))) as value_1,
       count(eval(match(_raw, "random value 3"))) as value_3,
       count(eval(match(_raw, "random value 4"))) as value_4,
       count(eval(match(_raw, "random value 5"))) as value_5,
       by _time source
| where value_1 > 0 AND (value_3 > 0 OR value_4 OR value_5)
| timechart span=1d dc(source) as unique_sources

It looks like it is easier just to split into to 2 billboards, but might be be better to have them in the same one (in case it is possible)

[Alanmas]

Just to answer my own question, yes it is possible just adding union between them 🙂

[yeahnah · Accepted solution]

Hi @Alanmas 

That is correct, the stats command summarised/transforms the data stream, so if you want to use a field in subsequent commands then you must ensure the field is based by either grouping (BY clause) or using a function.

In this case, you look to be summarising results on a daily bases so something like this might meet your needs  

index="something" source="*-value*" ("random value 1" OR "*random value 2*")
| bin span=1d _time
| stats count(eval(match(_raw, "random value 1"))) as value_1,
       count(eval(match(_raw, "random value 2"))) as value_2
       by _time source
| where value_1 > 0 AND value_2 > 0
| timechart span=1d dc(source) as unique_sources

Hope that helps

[Alanmas]

@yeahnah THANK YOU!!!

I did not know the usage of bin + by _time

This is exactly what I was looking for!! YOU ARE AWESOME 🙂