Knowledge Management

Use field as _time in lookup does not work

maurelio79
Communicator

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.

Tags (1)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Communicator

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Communicator

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!

0 Karma

cmerriman
Super Champion

can you try %6N instead of %9N

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...