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
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>
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>
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.
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.
It should be noted that the search will probably need to be more generic to get good results...