Dashboards & Visualizations

How to sort columns from left to right by date?

danskow
Engager

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. 

Labels (2)
0 Karma
1 Solution

dural_yyz
Communicator

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

 

View solution in original post

0 Karma

dural_yyz
Communicator

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

 

0 Karma

danskow
Engager

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#18/8/2022
Certificate_Name#29/9/2022
Certificate_Name#310/10/2022
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...