topic Re: How to write a search to find or track the host of a DHCP IP at a specific time? in Splunk Search

How to write a search to find or track the host of a DHCP IP at a specific time?

bigrichie90 — Mon, 28 Sep 2020 17:55:11 GMT

I am trying to build a query so that anytime someone needs to find the host of a DHCP IP at a specific time (since they change often), it is possible to track them. However, this query has become quite difficult and I am seeking help from you Splunxperts out there. There are summaries (such as Assign, Renew, Release, Nack, Conflict) and I am looking for the best way to go about this. Below is some sample data. I envision when someone needs to find a host, they type the IP into this query (or dashboard) and it brings the host up for them.

FirstOccurance=1413379981.000

LastOccurance=1413379981.000

date_hour=13

date_mday=15

date_minute=33

date_month=october

date_second=1

date_wday=wednesday

date_year=2014

date_zone=0

dest=BIGRICHIE90.splunk.com

dest_ip=123.45.67.890

dest_mac=AA:BB:CC:11:22:DD

dns=BIGRICHIE90.splunk.com
dvc_host=zzzaprot01

dvc_ip=123.45.68.9
endTime=1413379981.000

eventtype=external-referer

visitor-type-referred
host=SPLSQL01

index=network_dhcp

ip=123.45.67.890

key=AA:BB:CC:11:22:DD

leaseeventitemid=10409011

mac=AA:BB:CC:11:22:DD

nt_host=BIGRICHIE90.splunk.com

sourcetype=netw_DHCP (end network session start)

startTime=1413379981.000

summary=Assign

time=1413379981.000

timeendpos=45

timestartpos=31

_time=2014-10-15T09:33:01.000-04:00

Re: How to write a search to find or track the host of a DHCP IP at a specific time?

jlanders — Thu, 16 Oct 2014 02:12:00 GMT

I think this is best handled through a form-search-dashboard approach. You can start with the XML below and make it much more advanced by adding additional search fields. This XML would create a dashboard that lets the user put in an IP address and time range. When submitted, it'll execute your search and look for that IP address.

<form>
  <label>IP Sample Dashboard</label>
  <description>This dashboard accepts an IP address and gives you relevant logs for that address.</description>
  <fieldset submitButton="true" autoRun="false">
    <input type="text" token="ipaddr" searchWhenChanged="false">
      <label>IP Address</label>
    </input>
    <input type="time" token="timer" searchWhenChanged="false">
      <label>Time Range</label>
      <default>
        <earliestTime>-15m</earliestTime>
        <latestTime>now</latestTime>
      </default>
    </input>
  </fieldset>

  <row>
    <panel>
      <event>
        <title>sample</title>
        <searchString>dest=BIGRICHIE90.splunk.com dest_ip=123.45.67.890 dest_mac=AA:BB:CC:11:22:DD dns=BIGRICHIE90.splunk.com dvc_host=zzzaprot01 dvc_ip=$ipaddr$</searchString>
        <earliestTime>$timer.earliest$</earliestTime>
        <latestTime>$timer.latest$</latestTime>
        <option name="list.drilldown">full</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
        <option name="wrap">true</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">10</option>
      </event>
    </panel>
  </row>
</form>

Re: How to write a search to find or track the host of a DHCP IP at a specific time?

jlanders — Thu, 16 Oct 2014 02:12:41 GMT

It should be noted that the search will probably need to be more generic to get good results...

Re: How to write a search to find or track the host of a DHCP IP at a specific time?

bigrichie90 — Thu, 16 Oct 2014 14:29:22 GMT

The main concern I have is actually querying accurate data. It is difficult to match the IP to the correct hostname when the IP is constantly changing.

Re: How to write a search to find or track the host of a DHCP IP at a specific time?

jlanders — Mon, 28 Sep 2020 17:55:41 GMT

This may help too: http://blogs.splunk.com/2013/10/31/streamstats-example/

It's an example of using streamstats but they specifically show how to look at when DHCP changes for an IP address. As to your point, if you need to search by MAC address, I would still recommend the above method. You can create a simple form dashboard that takes a MAC address and time range. You can create a search like this:

host= sourcetype=dhcp mac_address=$mac$ | table _time, mac, hostname, ip_address, ...

When you submit a MAC address in the form, you can have it output data however you want but in this example it would output a table of results.

If you consume DHCP logs with MAC and IP address, you should be able to correlate the MAC address to a given host.