Splunk Search

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

ryhluc01
Communicator

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

1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

ryhluc01
Communicator

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

| eval _time = _time + 84000

Thank you so much!

0 Karma

ryhluc01
Communicator

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

| eval day = " " . day

0 Karma

woodcock
Esteemed Legend

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

0 Karma

ryhluc01
Communicator

Thanks ^_^

0 Karma

ryhluc01
Communicator

@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

0 Karma

Vijeta
Influencer

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

0 Karma

ryhluc01
Communicator

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.

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

Is this more helpful?

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

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.

0 Karma

ryhluc01
Communicator

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

0 Karma

tread_splunk
Splunk Employee
Splunk Employee

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 "<>" ]

Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...