Dashboards & Visualizations

Creating a punch card panel on dashboard and sorting order?

AndreasLasses
Explorer

Hello!

I am trying to create a punch card panel in a dashboard. I want to sort the X field on week number in ascending order and the Y on weekday in ascending order. My issue is that however I formulate the query, one of the axises will be in descending and the other in ascending. Here is my query:

 

 

 

index="*******" sourcetype=_json
| stats latest(Number_of_Commits) as Commits by week Day
| eval week_sort = case(
  Day=="Monday", 1, 
  Day=="Tuesday", 2, 
  Day=="Wednesday", 3, 
  Day=="Thursday", 4, 
  Day=="Friday", 5,
  Day=="Saturday", 6,
  Day=="Sunday", 7
)
| eval sort_field = week_sort*10 + case(
  Day=="Monday", 1, 
  Day=="Tuesday", 2, 
  Day=="Wednesday", 3, 
  Day=="Thursday", 4, 
  Day=="Friday", 5,
  Day=="Saturday", 6,
  Day=="Sunday", 7
)
| sort sort_field week_sort

 

 

 

The result of this query is as follows:

AndreasLasses_0-1680703803837.png

What am I missing to make both X & Y axises sort in ascending order?

Thanks in advance

Labels (2)
0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

index="_*"
| eval Day=strftime(_time, "%A")
| eval week=strftime(_time, "%V")
| stats count AS Commits BY week Day 
| eval _week_sort = case( Day=="Monday", 1, Day=="Tuesday", 2, Day=="Wednesday", 3, Day=="Thursday", 4, Day=="Friday", 5, Day=="Saturday", 6, Day=="Sunday", 7 ) 
| sort 0 - _week_sort

View solution in original post

woodcock
Esteemed Legend

Like this:

index="_*"
| eval Day=strftime(_time, "%A")
| eval week=strftime(_time, "%V")
| stats count AS Commits BY week Day 
| eval _week_sort = case( Day=="Monday", 1, Day=="Tuesday", 2, Day=="Wednesday", 3, Day=="Thursday", 4, Day=="Friday", 5, Day=="Saturday", 6, Day=="Sunday", 7 ) 
| sort 0 - _week_sort

AndreasLasses
Explorer

Thank you so much for this @woodcock  !
And let's say I want the days in descending order and the week still in ascending?

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

I'm not 100% sure but give this a try for the last line of @woodcock 's answer:

| sort 0 -_week_sort, +week

 

I hope this helps!!!

0 Karma

woodcock
Esteemed Legend

Not possible.  You get one or the other.  Don't forget to accept for karma.

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...