topic Re: Use alternate field for time and filtering in Splunk Search

Use alternate field for time and filtering

tlmayes — Thu, 28 Apr 2022 02:22:07 GMT

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.

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

Re: Use alternate field for time and filtering

bowesmana — Thu, 28 Apr 2022 02:29:59 GMT

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

Re: Use alternate field for time and filtering

tlmayes — Thu, 28 Apr 2022 02:36:40 GMT

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)

Re: Use alternate field for time and filtering

bowesmana — Thu, 28 Apr 2022 03:18:54 GMT

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?

Re: Use alternate field for time and filtering

tlmayes — Thu, 28 Apr 2022 03:32:58 GMT

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