Splunk Search

Search with _time

madhav_dholakia
Contributor

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:

madhav_dholakia_1-1640942625066.png

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

madhav_dholakia_2-1640942703670.png

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.

 

Labels (2)

inventsekar
Ultra Champion

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

0 Karma

madhav_dholakia
Contributor

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

 

madhav_dholakia_0-1641194251316.png

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

Thank you.

0 Karma

inventsekar
Ultra Champion

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

 

0 Karma

madhav_dholakia
Contributor

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

madhav_dholakia
Contributor

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...