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

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

Batidou — Mon, 15 Jun 2020 19:22:07 GMT

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:

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!

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

to4kawa — Sun, 14 Jun 2020 12:08:12 GMT

sample:

Why don't you try append ?

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

DalJeanis — Mon, 15 Jun 2020 16:57:56 GMT

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:

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