Timechart question:- combining two values for plotting timechart

[cbhattad]

My query is something like below

index = "A" | table x | stats dc(x) as total | appendcols [search index = "B" earliest="d" latest="@now" | table y | stats dc (y) as active ] | eval ratio = active/total

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

[to4kawa · Accepted solution]

| 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.

[cbhattad]

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?

[to4kawa]

_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.

[cbhattad]

Thank you so much @to4kawa
you saved my day

[to4kawa]

your welcome, Happy Splunking.

[gcusello]

No problem!
Ciao.
Giuseppe

[gcusello]

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

[cbhattad]

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

[gcusello]

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

[cbhattad]

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

[gcusello]

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

Ciao.
Giuseppe

[cbhattad]

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.

[gcusello]

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

[cbhattad]

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