How to compare only the time but not the date.

[dl70]

Hi!,

So my search query looks up an Excel Spreadsheet with a column called Time, that is populated with a time e.g. 10:00 AM (no date included) However, when I lookup this field in the splunk query, I notice that the Time Field is now associated with today's date. E.g 08-01-2021 10:00. 

This is an issue as I am trying to see if the event(time without the date) has occurred between two specific date and time ranges. 

I.e trying to see if an event with a time 10:00AM has occurred between 02-01-2021 and 03-01-2021. As splunk associates the time with today's date, the event is not being picked up as it is linked to today's date and thus not within the date range.

Any ideas?

Thanks in advance

[bowesmana]

Is this value from the raw data now the Splunk _time field or some other field?

When you talk about 'lookup' are you using this excel spreadsheet as a CSV lookup within a query, or have you ingested that data from excel to an index?

If it's into an index and it is stored in the _time field then that is what Splunk has decided is your event time. Unless that ingested data has the real date that is to apply to that time, then there is no way it can get the correct date.

If it's another field, what does that raw field look like in the Excel data that you have ingested to the index?

[dl70]

Hi,

I am using an excel spreadsheet as CSV lookup within the query and thus the _time field is empty.  The value is from the spreadsheet which in Splunk includes today's date. The issue I am facing is that as I am trying to see if the time is within a specified range e.g. does the event at 10:00AM everyday occur between 2nd Jan and 3rd Jan. The event should be displaying as yes it does occur between that time range, but as Splunk adds today's date to the time, it does not fit within the specified date range.

I am wondering if there is a way to remove the date part of the value, so that I am able to only compare the time.

Thanks in advance

[bowesmana]

Maybe you can post some of your query and an example of the raw data in your CSV lookup that contains the 10:00 AM time field. Splunk will not add anything extra from a lookup command to data that comes from that lookup result.

If the time field in Splunk has a date in it from a

| lookup your_excel_file.csv field

then the CSV also has the date part of that data

that would be easy enough to remove, e.g.

| rex field=Time mode=sed "s/.*(\d\d:\d\d [AP]M)/\1/"