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")
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?
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)
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?
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
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