How to count data from different time and use it in the same chart?

[Batidou]

Hi all,

Can someone help me on this problem? 

I'm working on a dashboard that I need to show how many users logged into the system and I need to have 2 views for each 30 minutes:

1. Today
2. Over time

(just to have the view if today we are getting more users logged on in history)

I can search it using earliest and latest function for each one, but I dont know how to join them for the same time.

Here is an example:

my-search logon-action earliest=1 latest=now()
| fields _time
| bucket span=30min _time
| eval hour=strftime(_time, "%H:%M")
| chart count as "Over-time" over hour

Statistics came like this:

Hour	Count
01:00	4
01:30	10
02:00	5
03:00	8
05:00	1

my-search logon-action earliest=-1@d latest=now()

| fields _time
| bucket span=30min _time
| eval hour=strftime(_time, "%H:%M")
| chart count as "today" over hour

Statistics came like this:

hour	count
01:30	1
03:00	8

I'm using the "append" command to have 1 result of the count per 30 min to chart it:

search logon-action earliest=1 latest=now()
| fields _time
| bucket span=30min _time
| eval hour=strftime(_time, "%H:%M")
| chart count as "Over-time" over hour
| appendcols [
| my-serach search logon-action earliest=-1@d latest=now()
| fields _time
| bucket span=30min _time
| eval hour=strftime(_time, "%H:%M")
| chart count as "today" over hour

and I'm having this:

hour	over time	today
01:00	4	1
01:30	10	8
02:00	5
03:00	8
05:00	1

So, the number 1 and 8 of "Today" is in the line of 01:00h and 01:30, but they actualy belongs to 01:30 and 03:00h

How can I fix it? I dont know how to do it and I appreciate if you guys can help me to have something like this:

hour	over time	today
01:00	4	0
01:30	10	1
02:00	5	0
03:00	8	8
05:00	1	0

any other idea is welcome to fix it

Thank you!

[DalJeanis]

The culprit for that is appendcols.  It is almost never the right tool for  the job, because it does not even try to line up the results, it just puts the first result from the second side onto the first result on the first side, and so on. 

When you are connecting two different kinds of data by a key (like the time) you should use append instead, then stats to join the two sides.  

More importantly, in this one, since one of the two groups is a subset of the other, you should do it this way:

index=foo   my-search logon-action earliest=1 
| fields _time
| bin _time span=30m
| eval totalCount=1
| eval todayCount=if(_time>relative_time(now(),"-1d"),1,0)
| eval Time=strftime(_time,"%H:%M")
| stats sum(totalCount) as totalCount sum(todayCount) as todayCount  by Time

And, by the way, to4kawa's suggestion to use tstats instead of a regular query is absolutely the right way to go, as long as all of the elements of your search were items that are index-time fields.  In this case they probably weren't, but here's how that would look if they were.

| tstats count as totalCount where index=foo earliest=1  by _time span=30m
| eval todayCount=if(_time>relative_time(now(),"-1d"),totalCount,0)
| eval Time=strftime(_time,"%H:%M")
| stats sum(totalCount) as totalCount sum(todayCount) as todayCount  by Time

[to4kawa]

sample:

| tstats count where index=_internal earliest=1 by _time span=30min
| eval status="OverTime"
| append [|tstats count where index=_internal earliest=-1d@d by _time span=30min | eval status="Today"]
| eval _time=strftime(_time,"%T")
| stats avg(count)  as count by _time status
| xyseries _time status count
| fillnull OverTime Today

Why don't you try append ?