topic Re: Search with _time in Splunk Search

Search with _time

madhav_dholakia — Fri, 31 Dec 2021 09:28:26 GMT

Hello,

I am monitoring a csv file using universal forwarder and the first column in the csv file is Last_Updated_Date. This file is indexed based on this field (_time = Last_Updated_Date).

This file also has a column called Created_Date. While writing a search, I want to use Created_Date as _time to filter the data and the search I have written is given below:

index="tickets" host="host_1" | foreach * [ eval newFieldName=replace("<<FIELD>>", "\s+", "_"), {newFieldName}='<<FIELD>>' ] | fields - "* *", newFieldName | eval _time=strptime(Created_Date, "%Y-%m-%d %H:%M:%S") | sort 0 -_time | addinfo | where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity") | dedup ID | where Status!="Closed" | eval min_time=strftime(info_min_time, "%Y-%m-%d %H:%M:%S") | eval max_time=strftime(info_max_time, "%Y-%m-%d %H:%M:%S") | eval index_time=strftime(_indextime, "%Y-%m-%d %H:%M:%S") | rename Created_Date as Created, Last_Updated_Date as "Last Updated" | table ID Type Created "Last Updated" _time min_time info_min_time max_time info_max_time index_time | sort 0 Created

When I run this search for a period, say 1st Feb 2021 - 31st Jul 2021, it gives results as below:

When I checked this for a longer period, say All Time - it gives results as below:

There are many open tickets (created between Feb and Jul) and not just two, as shown in the first screenshot, but it seems still the timepicker is using Last_Updated_Date to filter the events and not the Created_Date.

Can you please suggest how I can fix this?

Thank you.

Re: Search with _time

ITWhisperer — Fri, 31 Dec 2021 10:56:01 GMT

Search from 1 Feb until now, that way you will get events including the creation event and any subsequent updates. You can then filter on create date between 1 Feb and 31 Jul

Re: Search with _time

inventsekar — Fri, 31 Dec 2021 13:10:57 GMT

Hi @madhav_dholakia

as per my understanding, its not a best practice to eval something to "_time" (as _time is an system internal field).. so lets have it as "CreatedDateParsed". may we know what happens with this query please:

index="tickets" host="host_1" | foreach * [ eval newFieldName=replace("<<FIELD>>", "\s+", "_"), {newFieldName}='<<FIELD>>' ] | fields - "* *", newFieldName | eval CreatedDateParsed=strptime(Created_Date, "%Y-%m-%d %H:%M:%S") | sort 0 -CreatedDateParsed | addinfo | where CreatedDateParsed>=info_min_time AND (CreatedDateParsed<=info_max_time OR info_max_time="+Infinity") | dedup ID | where Status!="Closed" | eval min_time=strftime(info_min_time, "%Y-%m-%d %H:%M:%S") | eval max_time=strftime(info_max_time, "%Y-%m-%d %H:%M:%S") | eval index_time=strftime(_indextime, "%Y-%m-%d %H:%M:%S") | rename Created_Date as Created, Last_Updated_Date as "Last Updated" | table ID Type Created "Last Updated" _time min_time info_min_time max_time info_max_time index_time | sort 0 Created

and what happens when you have this table at the last...

| table _time ID Type Created "Last Updated" _time min_time info_min_time max_time info_max_time index_time

Re: Search with _time

madhav_dholakia — Mon, 03 Jan 2022 07:19:17 GMT

Thanks, @ITWhisperer - I want to avoid searching for All Time or a longer period than the actual event period (based on created date) I am interested with. This because the huge data we have in this index.

For example, if I am looking for tickets created between March 2021 and May 2021, I do not want to use time picker from March 2021 until now.

Using the time picker range as Feb 2021 to Till Date - is this the only option I have in this use case?

Thank you.

Re: Search with _time

madhav_dholakia — Mon, 03 Jan 2022 07:18:50 GMT

Thanks, @inventsekar - I have used below query with time picker value between Feb, 2021 to Jul 2021, it gives 7 events but there are more than 30 open tickets created between Feb 2021 and Jul 2021.

index="tickets" host="host_1" | foreach * [ eval newFieldName=replace("<<FIELD>>", "\s+", "_"), {newFieldName}='<<FIELD>>' ] | fields - "* *", newFieldName | eval CreatedDateParsed=strptime(Created_Date, "%Y-%m-%d %H:%M:%S") | sort 0 -CreatedDateParsed | addinfo | where CreatedDateParsed>=info_min_time AND (CreatedDateParsed<=info_max_time OR info_max_time="+Infinity") | dedup ID | where Status!="Closed" | eval min_time=strftime(info_min_time, "%Y-%m-%d %H:%M:%S") | eval max_time=strftime(info_max_time, "%Y-%m-%d %H:%M:%S") | eval index_time=strftime(_indextime, "%Y-%m-%d %H:%M:%S") | rename Created_Date as Created, Last_Updated_Date as "Last Updated" | table _time CreatedDateParsed ID Type Created "Last Updated" min_time info_min_time max_time info_max_time index_time | sort 0 Created

Using the time picker range as Feb 2021 to Till Date - is this the only option I have in this use case?

Thank you.

Re: Search with _time

ITWhisperer — Mon, 03 Jan 2022 09:53:39 GMT

Create a second index (possibly a summary index) which is based on create time rather than event time.

Re: Search with _time

inventsekar — Tue, 04 Jan 2022 01:56:31 GMT

if you dont want to use time-picker, can you use earliest and latest pls:

index="tickets" host="host_1" earliest="02/01/2021:00:00:00" latest="01/01/2022:00:00:00" | foreach * [ eval newFieldName=replace("<<FIELD>>", "\s+", "_"), {newFieldName}='<<FIELD>>' ] | fields - "* *", newFieldName | eval CreatedDateParsed=strptime(Created_Date, "%Y-%m-%d %H:%M:%S") | sort 0 -CreatedDateParsed | addinfo | where CreatedDateParsed>=info_min_time AND (CreatedDateParsed<=info_max_time OR info_max_time="+Infinity") | dedup ID | where Status!="Closed" | eval min_time=strftime(info_min_time, "%Y-%m-%d %H:%M:%S") | eval max_time=strftime(info_max_time, "%Y-%m-%d %H:%M:%S") | eval index_time=strftime(_indextime, "%Y-%m-%d %H:%M:%S") | rename Created_Date as Created, Last_Updated_Date as "Last Updated" | table _time CreatedDateParsed ID Type Created "Last Updated" min_time info_min_time max_time info_max_time index_time | sort 0 Created

Re: Search with _time

madhav_dholakia — Mon, 17 Jan 2022 08:14:38 GMT

Thanks, @inventsekar - if I understand correctly, using earliest & latest is another way of setting the time picker (with a precedence over time picker range), isn't it? In this case, it will still look for the data starting from Jan 2021 to Jan 2022 and will be (almost) same performance as using time picker?

I want to avoid searching for All Time or a longer period than the actual event period (based on created date) I am interested with. This because the huge data we have in this index.

Thank you.