Use alternate field for time and filtering

[tlmayes]

I am stuck.  Have tried all of the options I have found.  Most come close, but cannot make it work.  I collect data from a CMDB that has field with a date I need to filter on, created_date. 
What I am trying to accomplish: Generate a query for all events of the past 3 weeks where there are CMDB events that have a field "created_date" spanning multiple months, I only want those events that have a created_date that falls with the the 3 week period.  

If I use the following query, it returns as expected all events within the three week period.  What I want are all events based on the created_date, not based on _time.
BTW, created_date has a standard time output: "%Y-%m-%d %H:%M:%S"

index=cmdb dv_number=* dv_assigned_to=* dv_state=* created_date earliest=3w@w latest=@w
| search [| inputlookup cmdb_users.csv| table dv_assigned_to ]
| timechart span1w count(dv_number)

What I also tried, was converting the field created_date, to _time using the following, which turned created_date into epoch, but did produce the correct _time ouput, but cannot use earliest/latest since my understanding is earliest/latest only work on the initial search. 

index=cmdb dv_number=* dv_assigned_to=* dv_state=* created_date earliest=3w@w latest=@w
| search [| inputlookup cmdb_users.csv| table dv_assigned_to ]
| eval created_date=strptime(created_date,"%Y-%m-%d %H:%M:%S") 
| eval _time=created_date
........
.....
.. 

I also tried using a where statement, which partially worked, but would only cover the outer boundary (3weeks), not the inner boundary of the end of the last week.

| where created_date <= relative_time(now(), "-3w@w") AND created_date >= relative_time(now()), "@w")
 

[tlmayes]

Yes, I tried that as well, but returns all events for the past 3 weeks, plus 4 days.  The problem is, I need all events in the past full 3 weeks, so If I run the query today, I only want the events for weeks #14, 15, and 16 (along the 7 day boundary)

[bowesmana · Accepted solution]

Just bound the where clause with the time range you want, e.g.

| eval _time=strptime(created_date,"%Y-%m-%d %H:%M:%S")
| where _time>=relative_time(now(), "-3w@w") AND _time<=relative_time(now(), "@w") 

Is that what you mean?

[tlmayes]

Yes... I tried that earlier and kept getting a complaint about a missing ')'... just typed in all in again, and this time it worked.  I ended up with:

| eval created_date=strptime(created_date,"%Y-%m-%d %H:%M:%S")
| where created_date>=relative_time(now(), "-3w@w") AND created_date<=relative_time(now(), "@w") 

Thanks for the assist

[bowesmana]

The data that is ingested will always get allocated a _time field based on your config for that input - that totally defines what events you get back based on your earliest/latest time range.

I didn't quite understand your goal

Generate a query for all events of the past 3 weeks where there are CMDB events that have a field "created_date" spanning multiple months, I only want those events that have a created_date that falls with the the 3 week period.  

This is really about understanding what an 'event' ingested into Splunk will have as its _time value (ingest/CMDB event time?)

Then you must define your search time range to include any events that need to be found.

You can manipulate _time as you have done in your second example and can test for that _time within the last 3 weeks with

| eval _time=strptime(created_date,"%Y-%m-%d %H:%M:%S")
| where _time>=relative_time(now(), "-3w@w")

which will filter out only those created_date times within the last week.

Hope this helps