How to combine timechart, stats, and eval?

[vanheer]

Hi,

I have multiple fields like, counting how many items passing through gates:

| timechart count(eval(like(gate_id, "RG%") )) as items_RG, count(eval(NOT like(gate_id, "RG%") )) as all_items by building

 I want to exclude the counts of items_RG from the all_items, so I'm using :

| eval Total=all_items-items_RG

But it is not showing Total in the output, but when I use stats instead, I don't get the time column to show the graph as timechart.

| stats count(eval(like(gate_id, "RG%") )) as items_RG, count(eval(NOT like(gate_id, "RG%") )) as all_items by building
| eval Total=all_items-items_RG

I tried to use eventstats also couldn't get what I want.

[vanheer]

Thanks @venky1544 , almost there! now I have each building in a field,

|bin _time span=5m| stats count(eval(like(gate_id, "RG%") )) as items_RG, count(eval(NOT like(gate_id, "RG%") )) as all_items by _time,building
| eval Total=all_items-items_RG
|fields - all_items,items_RG

_time	building	Total
2022-04-13 17:20:00	GT01	24
2022-04-13 17:20:00	GT02	13
2022-04-13 17:20:00	GT03	29
2022-04-13 17:25:00	GT01	64
2022-04-13 17:25:00	GT02	52
2022-04-13 17:25:00	GT03	94

I need each building in a separate field to stack them in the graph.

_time	GT01	GT02	GT03	Total
2022-04-13 17:20:00	24	13	29	=24+13+29
2022-04-13 17:25:00	64	52	94	=64+52+94

[venky1544 · Accepted solution]

Hi @vanheer 

|bin _time span=5m| stats count(eval(like(gate_id, "RG%") )) as items_RG, count(eval(NOT like(gate_id, "RG%") )) as all_items by _time,building
| eval Total=all_items-items_RG
|fields - all_items,items_RG |xyseries _time,building,Total

try to append with xyseries command it should give you the  desired result 

if this help karma points are  appreciated /accept the solution it might help others 

[vanheer]

@venky1544 Thank you very much, I haven't used xyseries before.

One more optional question 😀:
I've created a manual dropdown span interval, but can we keep it automated as in timechart?

[venky1544]

Hi @vanheer 

try the below

|bin _time span=5m| stats count(eval(like(gate_id, "RG%") )) as items_RG, count(eval(NOT like(gate_id, "RG%") )) as all_items by _time,building
| eval Total=all_items-items_RG

if it helps karma points are appreciated