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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...