Getting Data In

Searching mulitple Time Fields within a record

New Member

Relatively new to splunk. I have a csv that has been splunked and splunk extracted the header record and assigned the fields accordingly. Within each record, there are multiple date/time fields. Splunk extracted the first as the record date, which is fine, but I would also like to leverage the other date fields for searches. When I try to perform a > , it doesn't process the search properly. Below is my search and also header record and one example from raw data.

index=gops STATUS=closed CLOSE_DATE>"6/1/10 00:00"

P_NUMBER,OPEN_TIME,CATEGORY,ASSIGNMENT,UPDATE_TIME,STATUS,CLOSE_TIME,P_LAST,FLAG,BRIEF_DESCRIPTION,UPDATED_BY,ASSIGNEE_NAME,AGREEMENT_ID,PROBLEM_STATUS,PRIORITY_CODE,NETWORK_NAME,PROD_OUTAGE_FMR,IMPACT_DURATION_FMR,IMPACT_AFFECTS_FMR,OPENED_BY,TICKET_OWNER,INCIDENT_HYPERLINK,TICKET_AGE

IM102265071,6/28/10 10:02,application,group,6/28/10 11:05,closed,6/28/10 11:05,t,f,desciption,NAME,NAME,[NULL],Closed,High,CI,1.05,0.216667,[NULL],NAME,group,link

Any help would be greatly appreciated.

0 Karma

Super Champion

The > and < operators are only going to compare numeric values (if splunk detects that your field is numeric) or compare text based on ASCII sort order. Splunk does not have a built in date comparison operation like this. Normally this isn't a problem because you use the date range picker (or relative date/time expressions) to very conveniently pick the timerange of the events you are looking for. But in your case, since you have multiple date fields, your search must be more sophisticated to handle some date conversions before you can do your comparison on a date field.

This search should do what you are looking for:

index=gops STATUS=closed | eval close_date=strptime(CLOSE_DATE,"%m/%d/%y %H:%M") | where close_date>strptime("6/1/10 00:00", "%m/%d/%y %H:%M")

NOTE: This solution will require splunk 4.1, if you are using Splunk 4.0 another approach can be used but it's slightly more complicated


Just a side note: If your timestamp format was something like YYYY-MM-DD HH:MM:SS then you can get away with an ASCII sort order comparison, so I think a search like you tried could work in that situation.