topic Re: Timechart question:- combining two values for plotting timechart in Splunk Search

Timechart question:- combining two values for plotting timechart

cbhattad — Tue, 22 Oct 2019 11:38:19 GMT

My query is something like below

index "B" consists of real time events and we get distinct user counts in variable "active".
index "A" consists of total user count

I want to plot ratio over a period of time (span = 1h)

Tried few queries but couldn't get to the result

Re: Timechart question:- combining two values for plotting timechart

gcusello — Tue, 22 Oct 2019 11:55:01 GMT

Hi
did you already tried something like this?

(index = "A") OR (index = "B" earliest="d" latest="@now")
| timechart dc(x) as total dc(y) as active  
| eval ratio = active/total

Ciao.
Giuseppe

Re: Timechart question:- combining two values for plotting timechart

cbhattad — Wed, 23 Oct 2019 06:35:51 GMT

Actually one of them is inputlookup and other is an index.

Re: Timechart question:- combining two values for plotting timechart

gcusello — Wed, 23 Oct 2019 07:01:14 GMT

Hi cbhattad,
theoretically it's the same thing, put the index in the main search and then add the lookup

index = "B" earliest="d" latest="@now"
| append [ | inputlookup your_lookup.csv | fields y _time ]
| timechart dc(x) as total dc(y) as active  
| eval ratio = active/total

But you can use timechart only if you have _time field also in lookup.

Could you share more information about your use case?

Ciao.
Giuseppe

Re: Timechart question:- combining two values for plotting timechart

cbhattad — Wed, 23 Oct 2019 07:22:33 GMT

Oh, now I get it.
inputlookup just stores the total count and does not have a time column.
It's like a static value.

Re: Timechart question:- combining two values for plotting timechart

gcusello — Wed, 23 Oct 2019 07:25:44 GMT

Hi cbhattad,
why you need the lookup values?, you could calculate totals in the same search.

Ciao.
Giuseppe

Re: Timechart question:- combining two values for plotting timechart

cbhattad — Wed, 23 Oct 2019 07:30:31 GMT

Hi @gcusello
The other index only stores the realtime activity, not the totals.
The lookup is updated by a different process which maintains the totals in lookup.

Re: Timechart question:- combining two values for plotting timechart

gcusello — Wed, 23 Oct 2019 07:43:49 GMT

Hi cbhattad,
try to use appendpipe command:

index = "B" earliest="d" latest="@now"
| timechart dc(y) as active  
| appendpipe [ | inputlookup your_lookup | fields total]
| stats values(active) As active values(total) AS total BY _time
| eval ratio = active/total

Ciao.
Giuseppe

Re: Timechart question:- combining two values for plotting timechart

cbhattad — Wed, 23 Oct 2019 07:57:57 GMT

index = "A" | timechart dc(user_id) as active  | appendpipe [ | inputlookup users.csv | fields user_id | stats dc(user_id) as total ] | stats values(active) As active values(total) AS total BY _time | eval ratio = active/total

Tried the above, in this "total" column is always empty

Re: Timechart question:- combining two values for plotting timechart

to4kawa — Wed, 23 Oct 2019 10:09:26 GMT

| makeresults count=2
| streamstats count
| eval _time=round(_time / 60 ) * 60
| eval _time=if(count==2,relative_time(_time,"-24h"),_time)
| makecontinuous _time span=1m
| eval user_id = random() % 100 + 1
| timechart span=1h dc(user_id) as active
| eval total = [| makeresults count=100
| eval user_id = random() % 100 + 1
| stats dc(user_id) as total
| return $total]
| stats values(active) As active values(total) AS total BY _time 
| eval ratio = round(active/total,2)

Hi,All
This is sample query.

index = "A" 
| timechart dc(user_id) as active
| eval total =  [ | inputlookup users.csv 
| fields user_id 
| stats dc(user_id) as total 
| return $total] 
| eval ratio = active/total
| fields _time active total ratio

How about this?
@gcusello , I'm sorry to break into a conversation.

Re: Timechart question:- combining two values for plotting timechart

gcusello — Wed, 23 Oct 2019 10:13:51 GMT

No problem!
Ciao.
Giuseppe

Re: Timechart question:- combining two values for plotting timechart

cbhattad — Wed, 23 Oct 2019 10:44:32 GMT

Thank you so much @to4kawa
you saved my day

Re: Timechart question:- combining two values for plotting timechart

to4kawa — Wed, 23 Oct 2019 11:02:45 GMT

your welcome, Happy Splunking.

Re: Timechart question:- combining two values for plotting timechart

cbhattad — Wed, 23 Oct 2019 12:57:28 GMT

One more thing, as shown in the image, for 2:00 am it shows 0.79, actually, the value should be it should be for 3:00 am. Somehow, splunk searches in reverse way and scans for events in from 3:00 am to 2:00 am and then assigns the value for 2:00 am as in the below image.
I want it in other way round. the value should be shown at 3:00 am

Any idea how can we do it?

Re: Timechart question:- combining two values for plotting timechart

to4kawa — Wed, 23 Oct 2019 14:45:11 GMT

_time   active  total   ratio
2019-10-22 23:00    20  69  0.29
2019-10-23 00:00    45  69  0.65
2019-10-23 01:00    45  69  0.65
2019-10-23 02:00    43  69  0.62

If time and numerical values are described in statistical information in this way, they will not deviate.

Please check the statistics.