topic Re: Use field as _time in lookup does not work in Knowledge Management

Use field as _time in lookup does not work

maurelio79 — Tue, 29 Sep 2020 16:48:30 GMT

Hi to all, i have a csv like:

host,last_report_time
host1,2017-07-28-14.23.00.000000
host2,2017-11-15-06.44.00.000000
host3,2017-11-15-06.13.00.000000
host4,2017-10-18-01.53.00.000000

i'm trying to use the field last_report_time in this way:

| inputlookup lastReportTime.csv |eval _time=strptime(last_report_time,"%Y-%m-%d-%H.%M.%S.%9N")

but it does not work.
Even if i change di time range, i get all records.

What is wrong?

Thanks and regards.

Re: Use field as _time in lookup does not work

cmerriman — Wed, 15 Nov 2017 20:08:38 GMT

can you try %6N instead of %9N

Re: Use field as _time in lookup does not work

somesoni2 — Tue, 29 Sep 2020 16:44:35 GMT

The timerange filter, based on _time field, only works on indexed data. If you expect to apply filter based on your time range picker in the search, you would need to use following workaround. (fixed strptime time format as well)

| inputlookup lastReportTime.csv |eval _time=strptime(last_report_time,"%Y-%m-%d-%H.%M.%S.%N")
| where _time>=[|gentimes start=-1  | addinfo | table info_min_time | rename info_min_time as search ] AND _time<[|gentimes start=-1  | addinfo | table info_max_time | rename info_max_time as search ]

The addinfo command adds the current time range as field info_min_time (earliest) and info_max_time (latest). The subsearches return those times and apply filter based on it.

Re: Use field as _time in lookup does not work

maurelio79 — Tue, 29 Sep 2020 16:48:40 GMT

Thanks, this works! But i think that i took the hard way to solve my problem.
What i need it's just to tell Splunk to consider the field 'last_report_time' as a date, because what i need is generate a pie chart (in an SDK application) with, for example, host with 'last_report_time' older than 3 month and all other host.

Re: Use field as _time in lookup does not work

somesoni2 — Tue, 29 Sep 2020 16:48:43 GMT

Ok. For that you probably need to create a field say category and set it's value by comparing relative_time (now()... with last_report_time. e.g.

| inputlookup lastReportTime.csv |eval _time=strptime(last_report_time,"%Y-%m-%d-%H.%M.%S.%N")
| eval category=if(_time>=relative_time(now(),"-3mon"),"Older than 3 months","All other hosts")
| stats count by category

Re: Use field as _time in lookup does not work

maurelio79 — Wed, 15 Nov 2017 22:53:09 GMT

just arrived me too:

| inputlookup lastReportTime.csv | rex field=last_report_time "(?<date>\d{4}-\d{2}-\d{2})\-" | eval _time=strptime(date,"%Y-%m-%d") | eval myrange=if(_time>relative_time(now(), "-90d@d"), "Last 3 months", "Older than 3 months") | stats count by myrange

P.S: exactly somesoni2, thanks very much!