How to edit my search to filter events based on a string date field?

[maximusdm]

Hello.

I've imported this Excel CSV file into Splunk and and trying to figure out how do I filter the results by a range date.
Here is the sample data:

_time                 Site Section                         Event Date   
2016-12-14 17:08:20     Amazon Fire Watch LIVE 11/3/2016
2016-12-14 17:08:20     Amazon Fire Watch LIVE 11/3/2016
2016-12-14 17:08:20     Amazon Fire Watch LIVE 12/5/2016
2016-12-14 17:08:20     Amazon Fire Watch LIVE 12/4/2016
2016-12-14 17:08:20     Amazon Fire Watch LIVE 12/3/2016
2016-12-14 17:08:20     Amazon Fire Watch LIVE 12/2/2016 

Current search:

source="data001.csv" host="host001" index="freewheel" sourcetype="csv"
| eval test = strptime("Event Date", "%m/%d/%Y")
| table _time "Site Section" "Event Date" test
| where data range is current week (last 7 days)
or where data range is current month (only events from current month)
or where data range is previous month (last 30 days)

I tried using strptime, but nothing showed in the results table. I believe the events are not indexed at the time they occur. That's why I need to rely on my date instead of the the index date. The _time field shows the date I imported the data so they are all equal.

I am looking at all posts, but getting the syntax for this in Splunk has been a challenge since I am a total rookie.

Thank you

[niketn]

If all events in your indexed csv file show the same time, it implies splunk was unable to parse timestamp available under the Event Date field and defaulted to file modified timestamp which is the last logic for Splunk to identify event timestamp. You should consider reindexing data properly with correct event time stamp. (Refer to attached screenshot).

Option 1
Please re-index by adding a single file csv file and making sure Splunk is able to read Event Timestamp from Event Date field.
Select the Timestamp format as %m/%d/%Y and Timestamp Fields as Event Date. (ideally splunk fields should not have spaces otherwise most of the SPL commands would required you to use single quote or double quote to identify field name with space. In your case see if you can modify csv column headers to Site_Section and Event_Date instead).
Also you should try to create your own custom sourcetype like <YourCustomSourcetype>_csv instead of using existing csv sourcetype if you have done some custom properties definition.

Option 2
You can modify the props.conf file in your existing splunk app's local folder for sourcetype [csv] and add the following for correct timestamp identification.

TIME_FORMAT=%m/%d/%Y
TIMESTAMP_FIELDS=Event_Date

For details on setting the sourcetype from the following links (please do your due diligence around defining sourcetype as this is the most crucial task for Splunk which helps it identify Time Stamp and events properly): http://docs.splunk.com/Documentation/Splunk/6.5.1/Data/Configuretimestamprecognition
http://docs.splunk.com/Documentation/Splunk/6.5.1/admin/Propsconf
http://docs.splunk.com/Documentation/Splunk/6.5.1/Data/Whysourcetypesmatter

If you choose either of the above two options, your time selection should work and allow you to pick as per the conditions you have defined like this week, previous week, previous month etc. Since you do not have time field in Event Date, you will still not have event time which is defaulted to 12:00 AM for everyday's events. (PS: If your CSV file has separate Event Time column as well, try to include that also as TIMESTAMP_FIELDS (comma separated) for required precision.)

I would not recommend having events with incorrect timestamp and afterwards modifying your Splunk query to parse/modify Event Date field afterwards. Time based filter of your input data in your base search will perform better than filtering afterwards using a where clause.

[maximusdm]

Thanks for all this info! it was a very good explanation and I learned a lot! so I went ahead and deleted my index and data and re-imported the CSV and I made sure I renamed the header field from Event Date to Event_Date. However, during the import process Splunk cannot see/list the data on the right side like you showed me in your screen shot 😞
I tried it in another machine and it worked but not in my VM. Sighs....
Thanks for all the info sir.

[niketn]

You can add just a single data file to see preview. When you are adding multiple files you have to Turn Preview Data on. In any case, if it has worked once for you on any machine, all you are interested in, is exporting the props.conf file with the properties. Based on the name of the sourcetype you have selected. By default in your case it was [csv] however you can create any custom sourcetype like [mycustomcsv]. All you would need is to add the following properties to the props.conf file, under specific sourcetype, manually if you can't do the same through preview mode.

 TIME_FORMAT=%m/%d/%Y
 TIMESTAMP_FIELDS=Event_Date

Like I had mentioned it would be most effective if you can ingest the data the way you want rather than modify things afterwards in the SPL.

[gokadroid · Accepted solution]

If you already have extracted epoch time from Event Date into test using | eval test = strptime("Event Date", "%m/%d/%Y") then you can build a where statement as follows to give you a hint of when the event occurred using now() and relative_time :

For current week

| where test >= relative_time(now(), "-7d")

For current month

| where test >= relative_time(now(), "-30d")

For last month

| where test >= relative_time(now(), "-60d") AND test < relative_time(now(), "-30d")

and so on. Hope this helps or works out for you.

[maximusdm]

thanks for your response. I am trying to show the value of time but all I get is a blank value:
| eval test = strptime("Event Date", "%m/%d/%Y")
| table "Event Date" test

[gokadroid]

Can you please ensure that any of the Field Names if they have a space in their field name are enclosed within quotes. Also if possible to re-upload the data as plain csv. Before uploading, try to open the csv in notepad to see if really there are some strings which you should be taking care as quoted strings like the fieldNames with spaces in between (or remove the spaces if possible)