Splunk Search

Filter on service end date

Damianv
New Member

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.

Labels (2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

Damianv
New Member

_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.

0 Karma
Get Updates on the Splunk Community!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...