Splunk Search

single value trend with earliest

Mike6960
Path Finder

I've got the followingsearch:

| stats values earliest(AG_Z) AS A_Z values earliest(D_AG) AS D_A_I
| eval eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N")
| eval eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval G_w=floor((eD_A_I-eA_Z)/86400)
| search G_w > 14
| timechart span=1w avg(G_w) As GDA

Somehow this does not give any results, when ik remove the first line
(| stats values earliest(AG_Z) AS A_Z values earliest(D_AG) AS D_A_I )
It does, but i need to use the earliest dates. How can i fix this so i use the earliest dates and generate a single value with trend?

Tags (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi Mike6960,
what's the meaning of the "values" word in the first line?
Bye.
Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

There are two problems:

  • the two "values" word in the first line,
  • timechart command runs using _time that you havent after your first line

For the first problem you have to delete the "values" words.
For the second, if you want to plot a graphic by A_Z or D_A_I, you can use the chart command

| bin span=1w A_Z
| chart avg(G_w) As GDA BY A_Z

Bye.
Giuseppe

0 Karma

Mike6960
Path Finder

But if I use Chart, i cannot use a single value withe trendline....

0 Karma

gcusello
SplunkTrust
SplunkTrust

To use timechart you need to have _time, so bring it in the first stats

| stats earliest(_time) AS _time earliest(AG_Z) AS A_Z earliest(D_AG) AS D_A_I 

or otherwise if you want to plot your trend by A_Z add an eval command

| eval _time=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N") 

in other words

| stats earliest(_time) AS _time earliest(AG_Z) AS A_Z earliest(D_AG) AS D_A_I 
| eval 
     eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N"), 
     eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now()),
     G_w=floor((eD_A_I-eA_Z)/86400) 
| search G_w > 14 
| timechart span=1w avg(G_w) AS GDA

or

| stats earliest(AG_Z) AS A_Z earliest(D_AG) AS D_A_I 
| eval 
     eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N"), 
     eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now()),
     G_w=floor((eD_A_I-eA_Z)/86400),
     _time=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N") 
| search G_w > 14 
| timechart span=1w avg(G_w) AS GDA

Bye.
Giuseppe

0 Karma

Mike6960
Path Finder

In your first line _time does not really do anything, correct? I want to plot by G_w so then i use;

| stats earliest(_time) AS _time earliest(AG_Z) AS A_Z earliest(D_AG) AS D_A_I
| eval
eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N"),
eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now()),
G_w=floor((eD_A_I-eA_Z)/86400)
| search G_w > 14
| timechart span=1w avg(G_w) AS GDA
? Strange thing is that i have searches with timechart without the _time

0 Karma

gcusello
SplunkTrust
SplunkTrust

No, it's only reported to be used below.
Use earliest or latest to have only one value.
About "searches with timechart without the _time", check them, maybe it's not highlighted, but it must be present.
Bye.
Giuseppe

0 Karma

Mike6960
Path Finder

unfortunately i cant get it working. I used ;

| stats earliest(_time) AS _time earliest(AG_Z) AS A_Z earliest(D_AG) AS D_A_I
| eval
eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N"),
eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now()),
G_w=floor((eD_A_I-eA_Z)/86400)
| search G_w > 14
| timechart span=1w avg(G_w) AS GDA

I get only one value and no trend. You say _time is only reported to use below, but below i dont see _time back?

0 Karma

gcusello
SplunkTrust
SplunkTrust

below you use _time only in timechart.
probably all your events have the same or near _time, did you tried with a different time span?
Bye.
Giuseppe

0 Karma

Mike6960
Path Finder

But i dont want to plot on the eventtime, i want to plot on the dates mentioned in the events. In this case eA_Z and eD_A_I. This reults in a number of days: G_w. which i want to plot. Still, if use my own search it workts without adding _time, this is something i don't understand:

| eval eA_Z=strptime(A_Z,"%Y-%m-%d %H:%M:%S.%N")
| eval eD_A_I=coalesce(strptime(D_A_I, "%Y-%m-%d %H:%M:%S.%N"),now())
| eval G_w=floor((eD_A_I-eA_Z)/86400)
| search G_w > 14
| timechart span=1w avg(G_w) As GDA

0 Karma

gcusello
SplunkTrust
SplunkTrust

if you don't use the fist line stats, you don't mention _time, but the important thing is that you still have it, instead after stats command you have only the mentioned fields, the other are lost for this search.

To plot using the dates mentioned in the event you have to use chart instead timechart, but you haven't trend because you havent _time.

Did you tried my second suggestion: to pass _time as an elaboration of one of the dates mentioned in the event?

Bye.
Giuseppe

0 Karma

Mike6960
Path Finder

Yes, i tried that to, also all kind of different time spans. My events are getting in since the beginning of october through by an insert once every day, so _time is different in the events. Somehow i either dont get a trend or i get only data from one date (the earliest)
I think i will give up on this one.

0 Karma

Mike6960
Path Finder
0 Karma

gcusello
SplunkTrust
SplunkTrust

in the above answer there was values(D_A) AS D_A not only values
Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...