How to sort columns from left to right by date?

[danskow]

I'm trying to create a dashboard panel that shows my F5 SSL Certificates and their expiration dates, and sorts the columns from left to right by date so the leftmost column would be the certificate expiring soonest. Here's what I have for a search:

index=f5_tstream source="f5.telemetry" telemetryEventCategory=systemInfo
| convert timeformat="%m/%d/%Y" ctime(sslCerts.*.expirationDate) AS *c_time
| stats latest(*c_time) by host
| rename host as Host

My results look something like this:

Host	latest(Certificate#1c_time)	latest(Certificate#2c_time)	latest(Certificate#3c_time)	latest(Certificate#4c_time)
Device#1	1/1/2023		7/7/2024
Device#2		10/10/2022		9/9/2023
Device#3	1/1/2023		7/7/2024

So basically I want to sort all columns containing "latest(*c_time)" by the date they're returning. Not sure if this is possible. 

[dural_yyz · Accepted solution]

I'm not certain I truly understand your end goal.  However, if you want left to right column closest to furthest you could try the following but seems inefficient but low hanging fruit.  Someone may have a more efficient means to do this.

1) Create the table you have

2) Transpose the table so headers become first column

3) Sort the first column as desired

4) Transpose back the table so first column becomes headers

Along the way watch out for default limit of 5 on transpose, set to as needed.  Also watch renaming headers and rows where required.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose

[danskow]

Thank you! After playing with the transpose command this morning, I came up with this:

index=f5_tstream source="f5.telemetry" telemetryEventCategory=systemInfo

| stats latest(sslCerts.*.expirationDate)

| transpose

| rename column AS Certificate "row 1" AS "Expiration"

| sort Expiration

| eval Expiration=strftime(Expiration, "%m/%d/%Y")

| eval Certificate=replace (Certificate, "latest\(sslCerts.", "")

| eval Certificate=replace (Certificate, ".expirationDate\)", "")

This gets the Unix time for all fields containing "latest(sslCerts.*.expirationDate)" and puts them in a table with the certificate name, sorts by expiration date, then translates the Unix time to m/d/y, then gets rid of the useless text at the beginning and end of the certificate name fields. This made my results populate in a table like this:

Certificate_Name#1	8/8/2022
Certificate_Name#2	9/9/2022
Certificate_Name#3	10/10/2022