topic Re: Extract values bucketed by time in Splunk Search

Extract values bucketed by time

varsuvius — Thu, 24 Nov 2016 12:43:46 GMT

Hello,

I have a bucketed chart in this format:

Is it possible to calculate the geometric mean of the values in each timestamp and add it to another field??

Re: Extract values bucketed by time

sundareshr — Thu, 24 Nov 2016 13:50:50 GMT

Try this

.... | mvexpand solrTime | eval natural_logs=ln(solrTime) | stats values(solrTime) as solrTime mean(natural_logs) as log_mean by _time | eval geometric_mean = exp(log_mean)

Got this from this great post by @aljohnson

Re: Extract values bucketed by time

varsuvius — Tue, 29 Sep 2020 11:55:28 GMT

Did not work.

I tried this command:

| rex field=_raw ".*Solr Time: (?\d+)" | mvexpand solrTime | eval natural_log=ln(solrTime) | bucket span=10m _time | stats values(solrTime) as solrTime mean(natural_logs) as log_mean by _time | eval geometric_mean = exp(log_mean)

Re: Extract values bucketed by time

sundareshr — Thu, 24 Nov 2016 14:50:42 GMT

I don't believe you need the rex command. Add this to the query that resulted in the table you have in your screenshot. Make sure the table has two columns _time and solrTime

Re: Extract values bucketed by time

varsuvius — Tue, 29 Sep 2020 11:55:30 GMT

The query between --- is the initial one that generates that chart.

After that, the results are returning only the timestamp.

Re: Extract values bucketed by time

sundareshr — Thu, 24 Nov 2016 15:23:11 GMT

Try this

---  | rex field=_raw ".*Solr Time: (?\d+)" | bucket span=10m _time | chart values(solrTime) as solrTime by _time | mvexpand solrTime | eval natural_log=ln(solrTime) | stats values(solrTime) as solrTime mean(natural_logs) as log_mean by _time | eval geometric_mean = exp(log_mean)

Re: Extract values bucketed by time

varsuvius — Thu, 24 Nov 2016 15:35:26 GMT

Still not working.

Re: Extract values bucketed by time

sundareshr — Thu, 24 Nov 2016 15:39:52 GMT

Try this. Fixed typo

---  | rex field=_raw ".*Solr Time: (?\d+)" | bucket span=10m _time | chart values(solrTime) as solrTime by _time | mvexpand solrTime | eval natural_logs=ln(solrTime) | stats values(solrTime) as solrTime mean(natural_logs) as log_mean by _time | eval geometric_mean = exp(log_mean)

If this doesn't work, try taking out one segment at a time to see where it stops working.

Re: Extract values bucketed by time

varsuvius — Thu, 24 Nov 2016 15:56:53 GMT

It's working now. Thanks a lot for your help. 😄