topic Re: Date range on inputlookup search in Splunk Search

Date range on inputlookup search

dyeo — Sat, 11 May 2019 16:48:19 GMT

Hi I'm trying to do an inputlookup search with a specific date range of the last 6 months, but am not having any success. I tried converting _time to epoch to then apply a time filter, but that epoch time just results in a blank field.

| inputlookup append=t licensing_epd 
| eval epoch=strptime(_time,"%Y-%m-%d") 
| timechart span=1d sum(count) as count 
| bucket _time span=1d 
| table _time epoch count

Re: Date range on inputlookup search

sduff_splunk — Mon, 13 May 2019 01:13:51 GMT

Please provide a sample of your licensing_epd data (five or ten rows should be sufficient). That will enable us to see the format of the time field you have stored, and how to convert it to something timechart recognises.

Re: Date range on inputlookup search

nabeel652 — Mon, 13 May 2019 07:25:50 GMT

Is there a _time field in your lookup? What format is it in?

Re: Date range on inputlookup search

dyeo — Wed, 30 Sep 2020 00:32:09 GMT

Please see sample data below.

_time index count
2017-12-13 _audit 96754
2017-12-13 _internal 7065998
2017-12-13 _introspection 534316
2017-12-13 _telemetry 251
2017-12-13 it_co 100924602
2017-12-13 main 1
2017-12-14 _audit 139595
2017-12-14 _internal 7411574
2017-12-14 _introspection 635669
2017-12-14 _telemetry 272
2017-12-14 it_co 105231185
2017-12-14 main 0
2017-12-14 test 223896
2017-12-15 _audit 175059
2017-12-15 _internal 12261227

Re: Date range on inputlookup search

dyeo — Fri, 17 May 2019 02:02:47 GMT

Please see sample data below.

Re: Date range on inputlookup search

Vijeta — Wed, 30 Sep 2020 00:35:23 GMT

Try like this

| inputlookup append=t licensing_epd
| eval _time =strptime(_time,"%Y-%m-%d")
| timechart span=1d sum(count) as count

Re: Date range on inputlookup search

dyeo — Fri, 17 May 2019 03:40:43 GMT

0 results when I run this query.

Re: Date range on inputlookup search

Vijeta — Fri, 17 May 2019 04:10:02 GMT

What are the results without timechart ?

Re: Date range on inputlookup search

dyeo — Fri, 17 May 2019 16:53:44 GMT

Without the timechart command, the _time column is empty.

_time   count       index
        96754       _audit
        7065998     _internal
        534316      _introspection
        251         _telemetry
        100924602   it_co
        1           main

Re: Date range on inputlookup search

Vijeta — Fri, 17 May 2019 16:56:20 GMT

@dyeo Only running the inputlookup command gives you values in _time column?

Re: Date range on inputlookup search

dyeo — Fri, 17 May 2019 16:58:52 GMT

Yes, the inputlookup command generates a _time value in the format:

2017-12-13
2017-12-14
2017-12-15

Re: Date range on inputlookup search

Vijeta — Fri, 17 May 2019 17:53:43 GMT

@dyeo Does this not work for you?

| inputlookup append=t licensing_epd |  stats sum(count) as count by _time

Re: Date range on inputlookup search

dyeo — Fri, 17 May 2019 18:13:23 GMT

That works, but how do I convert _time to epoch so that I can filter for the last 6 months?

Re: Date range on inputlookup search

Vijeta — Fri, 17 May 2019 18:19:03 GMT

You can assign epoch=_time, but that will not help you filter data I believe. Can you not use where after inputlookup on _time?

| inputlookup append=t licensing_epd |  stats sum(count) as count by _time| eval epoch=_time

Re: Date range on inputlookup search

dyeo — Fri, 17 May 2019 21:46:29 GMT

That was it. Thanks! Here's my full query I used to filter for the last 6 months.