Splunk Search

AVG days for selection of events

Mike6960
Path Finder

In the following search I want to have the average for the events where GB_w is < 15 days

| stats earliest(A_Z) AS A_Z earliest(D_A) AS D_A
| eval eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N")
| eval eD_A_I=coalesce(strptime(D_A, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval GB_w=floor((eD_A_I-eA_Z)/86400)
| chart avg(GB_w) As GDA
| eval GDA=round(GDA,0)

I thought i could use something like this:

| eval GB_w=floor((eD_A_I-eA_Z)/86400)
|where GB_w <15
| chart avg(GB_w) As GDA
| eval GDA=round(GDA,0)

Somehow the result is 0. It also seems that the search always uses the 'now'time, even if there is a D_A time present in the event.
What am i doing wrong?

Tags (1)
0 Karma

cmerriman
Super Champion

do eA_Z, eD_A, GB_w and GDA all come back with expected values?

I think you could do something like this, as a different approach:

| convert mktime(A_Z) as eA_Z mktime(D_A) as eD_A timeformat="%Y-%m-%d %H:%M:%S.%N"
| stats earliest(eA_Z) AS eA_Z earliest(eD_A) AS eD_A
| eval eA_Z_I=relative_time(eA_Z,"@d")
| eval eD_A_I=if(isnull(eD_A),relative_time(now(),"@d"),relative_time(eD_A,"@d"))
| eval GB_w=eD_A_I-eA_Z_I
| search GB_w<15
| chart avg(GB_w) as GDA 
| eval GDA=round(GDA,0)
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...