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/05/2016 instead of
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"
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).
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
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.