single value trend with earliest

[Mike6960]

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?

[gcusello]

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

[gcusello]

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

[Mike6960]

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

[gcusello]

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

[Mike6960]

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

[gcusello]

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

[Mike6960]

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?

[gcusello]

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

[Mike6960]

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

[gcusello]

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

[Mike6960]

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.

[Mike6960]

Got this from you,

https://answers.splunk.com/answers/585581/how-to-calculate-the-days-between-earliest-date-an.html

[gcusello]

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