Splunk Search

create a chart from a csv

chadman
Path Finder

I have a csv file that Splunk ingest and use it to create a chart. It works ok, but I'm not sure how to sort this by date since the data in the csv is not in a real date format. the csv looks like:

Month,Webcam,Speech
July-17,10566,1255
August-17,10612,1248
September-17,10703,1263
October-17,10770,1260
November-17,10872,1267

and this data goes back a few years. Is there a way I can still have it sort by the month/year in my chart? Currently have it sort by a count that so far as gone up. That works for now, but the number could go down and put the months out of order.

Tags (2)
0 Karma
1 Solution

elliotproebstel
Champion

I'd convert Month to a time field with a little bit of data massaging:
| eval time=strptime("01-".Month, "%d-%B-%y")

What I'm doing there is appending "01-" to the start of the month string before converting it withstrptime, because (to the best of my knowledge) strptime requires at least day, month, and year in order to create a valid timestamp. Since you only have one entry per month in your sample data, that should work fine. Then you can just sort based on the time field:
| sort - time

View solution in original post

0 Karma

LeeSart
Explorer

I'd suggest using strptime to convert the date field to epoch time, then sorting on the epoch field. The example below assumes that the field containing your date is called 'date':

base search | eval epoch=strptime(date, "%B-%y") | sort epoch
0 Karma

elliotproebstel
Champion

I'd convert Month to a time field with a little bit of data massaging:
| eval time=strptime("01-".Month, "%d-%B-%y")

What I'm doing there is appending "01-" to the start of the month string before converting it withstrptime, because (to the best of my knowledge) strptime requires at least day, month, and year in order to create a valid timestamp. Since you only have one entry per month in your sample data, that should work fine. Then you can just sort based on the time field:
| sort - time

0 Karma

chadman
Path Finder

thanks, that worked.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...