Splunk Search

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

Batidou
Loves-to-Learn

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!

Labels (3)
0 Karma

DalJeanis
Legend

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

 

 

 

 

 

0 Karma

to4kawa
Ultra Champion

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 ?

0 Karma
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...