Splunk Search

Timechart question:- combining two values for plotting timechart

cbhattad
Path Finder

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

0 Karma
1 Solution

to4kawa
Ultra Champion
| 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.

View solution in original post

to4kawa
Ultra Champion
| 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
Path Finder

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?

alt text

0 Karma

to4kawa
Ultra Champion
_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.

0 Karma

cbhattad
Path Finder

Thank you so much @to4kawa
you saved my day

0 Karma

to4kawa
Ultra Champion

your welcome, Happy Splunking.

0 Karma

gcusello
SplunkTrust
SplunkTrust

No problem!
Ciao.
Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

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
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

cbhattad
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

Ciao.
Giuseppe

0 Karma

cbhattad
Path Finder

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

cbhattad
Path Finder
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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...