topic Re: How do I get an Xyseries to display dates in descending order? in Splunk Search

How do I get an Xyseries to display dates in descending order?

ryhluc01 — Wed, 30 Jan 2019 19:19:05 GMT

sample query:

index=foo "string of data"="age needed"age earliest=-5d
| stats dedup_splitvals=t , values(_time) AS _time  by dept, "age_needed"
| sort department 
| fields - _span  
| eval conv_age=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q")
| eval age=((now()-conv_age)/84000)
| eval day=strftime(_time,"%Y-%m-%d")
| stats avg(age) as super_Age by dept, day 
| eval super_Age=round(super_Age,2)
| xyseries Vertical day super_Age

This is a more detailed explanation of what I am looking for:
Okay, so the column headers are the dates in my xyseries. I have a filter in my base search that limits the search to being within the past 5 days.

Xyseries is displaying the 5 days as the earliest day first (on the left), and the current day being the last result to the right.

Dont Want
Dept.......... 1/26........1/27........1/28.........1/29...........1/30 (with the most recent being last)
dept1..... value....... value .....value.......value...........value
dept2..... value....... value .....value.......value...........value

I need for the order that the dates are being displayed to show me the most recent day first (i.e the current day)

This is what I want
Dept.......... 1/30........1/29........1/28.........1/27...........1/26 (with the oldest result being last)
dept1..... value....... value .....value.......value...........value
dept2..... value....... value .....value.......value...........value

Re: How do I get an Xyseries to display dates in descending order?

chrisyounger — Wed, 30 Jan 2019 19:22:47 GMT

Hi @ryhluc01

Is it as simple as doing this:

sample query: 
index=foo "string of data"="age needed"age earliest=-5d
| stats dedup_splitvals=t , values(_time) AS _time by dept, "age_needed"
| sort department 
| fields - _span 
| eval conv_age=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q")
| eval age=((now()-conv_age)/84000)
| eval _time = _time + 84000
| eval day=strftime(_time,"%Y-%m-%d")
| stats avg(age) as super_Age by dept, day 
| eval super_Age=round(super_Age,2)
| xyseries Vertical day super_Age

Apologies if I have misunderstood the problem.

Re: How do I get an Xyseries to display dates in descending order?

ryhluc01 — Wed, 30 Jan 2019 19:36:25 GMT

Hey thanks for the speedy response! And no need to apologize. I appreciate your help.

Ok so the addition of your time eval gives me tomorrows date and removes all other dates.
essentially it ends up looking like this:

department............1/31
dept8......................value

Re: How do I get an Xyseries to display dates in descending order?

Vijeta — Wed, 30 Jan 2019 19:56:33 GMT

Isn't the date already in descending . Sorry can you be more precise on the question?

Re: How do I get an Xyseries to display dates in descending order?

ryhluc01 — Wed, 30 Jan 2019 20:11:18 GMT

Sure!

Okay so the column headers are the dates in my xyseries. I have a filter in my base search that limits the search to being within the past 5 day's.
Xyseries is displaying the 5 day's as the earliest day first(on the left) and the current day being the last result to the right.

I need for the order that the dates are being displayed to show me the most recent day first (i.e the current day)

Is this more helpful?

Re: How do I get an Xyseries to display dates in descending order?

woodcock — Wed, 30 Jan 2019 20:42:07 GMT

Like this (by prepending each date with an ascending number of initial and INVISIBLE spaces):

index=foo "string of data"="age needed"age earliest=-5d
| stats dedup_splitvals=t , values(_time) AS _time by dept, "age_needed"
| sort department 
| fields - _span 
| eval conv_age=strptime(Date,"%Y-%m-%dT%H:%M:%S.%Q")
| eval age=((now()-conv_age)/84000)
| eval _time = _time + 84000
| eval day=strftime(_time,"%Y-%m-%d")
| stats avg(age) as super_Age by dept, day 
| eval super_Age=round(super_Age,2)

| sort 0 day
| streamstats dc(day) AS spaces_count
| eval spaces_count = spaces_count - 1
| eval initialLength = len(day) 
| eval day = "                                                  " . day
| eval paddedLength = len(day)
| eval day = substr(day, paddedLength - initialLength - spaces_count + 1)

| xyseries dept day super_Age

Re: How do I get an Xyseries to display dates in descending order?

ryhluc01 — Wed, 30 Jan 2019 21:26:23 GMT

@woodcock
Ahhh! Your query gives me what I am looking for while its parsing, but when the search finishes it snaps to this instead and I am not sure why:(

This removes all other date columns (the past 5 days) & displays only one day(31st which is outside of my 5 day range).

It now only looks like this
Department.....................1/31
dept1.............................. value

Re: How do I get an Xyseries to display dates in descending order?

ryhluc01 — Wed, 30 Jan 2019 21:50:12 GMT

Woo hoo!!! It works when I remove this:

| eval _time = _time + 84000

Thank you so much!

Re: How do I get an Xyseries to display dates in descending order?

ryhluc01 — Wed, 30 Jan 2019 22:20:49 GMT

@woodcock
Hey is there a cleaner way to display this part?

| eval day = " " . day

Re: How do I get an Xyseries to display dates in descending order?

woodcock — Wed, 30 Jan 2019 22:24:37 GMT

Check out the printf function in splunk and also this Q&A for other potential options:
https://answers.splunk.com/answers/46642/is-there-a-cleaner-way-to-zeropad-numeric-values-ie-add-leading-zeros.html

Re: How do I get an Xyseries to display dates in descending order?

ryhluc01 — Thu, 31 Jan 2019 14:56:58 GMT

Thanks ^_^

Re: How do I get an Xyseries to display dates in descending order?

tread_splunk — Tue, 29 Sep 2020 23:58:26 GMT

I've spent a lot of time on "ordering columns" recently and its uncovered a subtle difference between the xyseries command and an equivalent approach using the chart command. So I think you'll find this does something close to what you're looking for ...
index=_internal earliest=-5d@d latest=@d
| bin _time span=1d
| stats count by _time sourcetype
| eval day=strftime(_time,"%m/%d")
| sort - day
| streamstats current=t window=0 global=t dc(day) AS dayNo
| eval day=dayNo.":".day
| chart first(count) over sourcetype by day
| foreach :
[ rename "<>" AS "<>" ]
Play around with the +/- in the sort command (line 4) to get the column order you're looking for. What I found strange is if you rewrite this using the xyseries command in place of the chart command (which is how I began), i.e. like this...
index=_internal earliest=-5d@d latest=@d
| bin _time span=1d
| stats count by _time sourcetype
| eval day=strftime(_time,"%m/%d")
| sort + day
| streamstats current=t window=0 global=t dc(day) AS dayNo
| eval day=dayNo.":".day
| xyseries sourcetype day count
| foreach :
[ rename "<>" AS "<>" ]
...the rename command after the xyseries reorders the columns alphabetically. Messing things up.
By the way, either example above can be turbo-charged with tstats i.e.
| tstats count where index=_internal earliest=-5d@d latest=@d by _time span=1h sourcetype
| eval day=strftime(_time,"%m/%d")
| sort + day
| streamstats current=t window=0 global=t dc(day) AS dayNo
| eval day=dayNo.":".day
| chart first(count) over sourcetype by day
| foreach :
[ rename "<>" AS "<>" ]