Splunk Search

How to sort date fields chronologically in a stacked bar chart and group dates by week numbers?

timgirgis
Explorer

I have a CSV import that has a date field in the format dd/mm/yyyy that I want to be able to chart chronologically on the x-axis in a graph in Splunk. However, when the field is sorted, it sorts the dates based on the dd and not the actual date e.g. 01/02/2016, 01/05/2016 instead of 01/02/2016, 03/02/2016 etc. How do I tell Splunk to recognize the field as a date and sort chronologically (there are no time stamps)?

As a second question, how do I group these dates up into weeks on a graph? I want to show all the dates between Monday and Sunday on the first week into Week 1, then those dates in Week 2 etc.

The end goal is to plot out a simple stacked bar chart where "Delivery Start _ Triage Date" is the date (grouped by week and plotted chronologically) along the x-axis, and "Title" is counting the number of projects along the y-axis. "Pipeline" is what I want to break the stacks into (it shows the office where these projects are taking place).

index="pipedrive_modified" | sort by "Delivery Start _ Triage Date" | chart count("Title") over "Delivery Start _ Triage Date" by "Pipeline"

alt text

alt text

Obviously I want this particular search to group dates by week and then plot them chronologically, but I would also love to know the logic behind / how to get Splunk to recognize fields as a date range and then sort them chronologically (without grouping them into weeks - as per my first query).

Thanks

0 Karma

renjith_nair
Legend

You can convert a string to date by using strptime(field,"format")

For sorting , try

index="pipedrive_modified" | eval my_date=strptime(datefield,"%d/%m/%Y")|sort my_date

For the final search try

index="pipedrive_modified" | eval my_date=strptime(datefield,"%d/%m/%Y")|eval week_no=strftime(my_date,"%U")|chart count(Title) over week_no by Pipeline
---
What goes around comes around. If it helps, hit it with Karma 🙂

timgirgis
Explorer

Hi Renjith,

We couldnt get the query to return the results we wanted, it is still sorting by the day field and not the day/month combination. Any idea why?

0 Karma

somesoni2
Revered Legend

Can you provide the query that you tried (and didn't work)?

0 Karma

renjith_nair
Legend

That's strange. Are you sure your date column is string and not date and is in dd/mm/yyyy format?

I have tried with a dummy data and is working as below.

|stats count|eval dt="01/02/2016,02/02/2016,08/02/2016,01/08/2016,02/12/2016,10/01/2016" |eval splitted=split(dt,",")
|mvexpand splitted|fields splitted|eval converted=strptime(splitted,"%d/%m/%Y")|sort converted

here if you sort by "dt" and "sorted" , you can see the difference in the order.

---
What goes around comes around. If it helps, hit it with Karma 🙂
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 ...