Use field as _time in lookup does not work

[maurelio79]

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.

[somesoni2]

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.

[maurelio79]

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.

[somesoni2]

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

[maurelio79]

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!

[cmerriman]

can you try %6N instead of %9N