Archive

single value trend with earliest

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

Legend

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

0 Karma

Legend

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

Path Finder

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

0 Karma

Legend

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

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

Legend

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

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

Legend

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

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

Legend

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

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

Path Finder
0 Karma

Legend

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!