Filter on service end date

[Damianv]

Good day,

I have been trying to figure out how to accomplish the following task for a few days now and thought I would ask the community for ideas.

I have got events coming into Splunk that have got a service start and service end date like the example provided below.

ServiceStartDate="2021-01-26", ServiceEndDate="2021-03-31"

I have been trying to figure out how I can filter based on the ServiceEndDate. I want to be able to select either a date range or just a specific date. This should then produce all events with a ServiceEndDate within that range or specific date selected.

The search I have been testing is the following:
index="my_index" sourcetype="my_sourcetype" source="my_source" | eval _time=strptime(ServiceEndDate,"%Y-%m-%d") | sort limit=0 - _time | addinfo | where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity")

This allows me to use the time picker to filter on ServiceEndDate, but does not really produce all the results I ask for.

For example, I would choose a date range from 01/20/2020 to 12/20/2021
The search won't produce all events for that range unfortunately. I know that there is indeed events with a ServiceEndDate in that range that is not displayed because if I select "All time" in the time picker I can see them.
The amount of events that it should return does not exceed 10,000 but I put the limit=0 in there just in case.

The end goal will be to put this into a dashboard so I can produce the filtered events in a table.

Any ideas would be greatly appreciated.

[ITWhisperer]

I think your issue may be because the time picker is limiting your initial set of events to the events where the indexed value of _time falls within the time picker range. Evaluating a new value for _time makes no difference to expanding this set to include events prior to the earlier time or after the later time which may have ServiceEndDate's in the range you are looking for. What is the _time value in your events based on? Can you re-index based on the ServiceEndDate? What do you do about events which have no ServiceEndDate in these circumstances?

[Damianv]

_time value is based on the time the event was ingested.

I can possibly index based on ServiceEndDate, but I also use ServiceStartDate in another search query which functions correctly at the moment. I don't want to break that, so not sure if I will have to index the source twice, one based on ServiceEndDate and the other based on ServiceStartDate.

I filter out events that do not contain ServiceEndDate. I have a field called "type". Only events of a certain type are used in the search and all of those contain a ServiceEndDate.