Reporting

inputcsv with time range query

joeyfeb
Loves-to-Learn

test.csv contents:
capture_time,leased_ip
20150616235926,192.168.2.23
20150318205845,10.10.23.1
20150512195955,192.168.27.2
20150417154505,192.168.64.6
20150615222505,10.10.100.6
20150303213322,10.10.22.18
20150616233103,192.168.87.2

dhcp lease log events will capture the following fields only:
1. lease_time -> lease timestamp
2. leased_ip -> leased ip address
3. userid -> user
4. session -> either "START or STOP"


Notes:

  1. the user can use the leased IP for a maximum of 21 days before it requrests for a new IP.
    • this means that the "capture time" could be 21 days after the "lease time"
      1. the content of the CSV input file can range from 1 to 2000

Objective:

  1. capture the culprit
  2. find the first matching userid using the leased ip address(ip) with a "lease_timestamp" before the "capture_time" based on the csv file

Desired Output Table:

userid, leased_ip, lease_time, capture_time

I have the search command below for a start. But i cannot figure out how do a time range search for each capture time in the csv file.

index=dhcp_leaselog
| search [|inputcsv test.csv | fields leased_ip]

If i do "| fields leased_ip capture_time", obviously it cannot match anything because capture_time is not equal to the lease_time

I'm currently looking at "gentimes" but I'm not sure if it is the correct command to use for this case or considering on switching to "inputlookup".

Any suggestions are very much appreciated. Thank You!

0 Karma

woodcock
Esteemed Legend

You need map like this:

| inputcsv test.csv | map search="search index=dhcp_leaselog leased_ip=$leased_ip$ | where lease_timestamp<$capture_time$ | dedup | eval capture_time=$capture_time$ | eval leased_ip=$leased_ip$ | fields userid, leased_ip, lease_time, capture_time"
0 Karma

woodcock
Esteemed Legend

Did this work?

0 Karma
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 ...