topic single value trend with earliest in Splunk Search

single value trend with earliest

Mike6960 — Tue, 29 Sep 2020 16:46:22 GMT

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?

Re: single value trend with earliest

gcusello — Mon, 13 Nov 2017 10:48:24 GMT

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

Re: single value trend with earliest

Mike6960 — Mon, 13 Nov 2017 10:57:53 GMT

Got this from you,

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

Re: single value trend with earliest

gcusello — Tue, 29 Sep 2020 16:42:53 GMT

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

Re: single value trend with earliest

gcusello — Mon, 13 Nov 2017 11:05:34 GMT

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

Re: single value trend with earliest

Mike6960 — Mon, 13 Nov 2017 11:09:12 GMT

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

Re: single value trend with earliest

gcusello — Mon, 13 Nov 2017 11:16:27 GMT

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

| 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

Re: single value trend with earliest

Mike6960 — Tue, 29 Sep 2020 16:46:25 GMT

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

Re: single value trend with earliest

gcusello — Mon, 13 Nov 2017 11:39:01 GMT

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

Re: single value trend with earliest

Mike6960 — Tue, 29 Sep 2020 16:46:28 GMT

unfortunately i cant get it working. I used ;

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

Re: single value trend with earliest

gcusello — Mon, 13 Nov 2017 12:04:58 GMT

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

Re: single value trend with earliest

Mike6960 — Tue, 29 Sep 2020 16:46:30 GMT

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:

Re: single value trend with earliest

gcusello — Mon, 13 Nov 2017 12:31:10 GMT

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

Re: single value trend with earliest

Mike6960 — Tue, 14 Nov 2017 07:27:52 GMT

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.