Reply
Highlighted

DHCP leases statistics by Vendor

Adviser
Posts: 137
5360     1

 

This report shows q-ty uniq leases issued for appliances of different vendors.

 

DHCPbyVendor.png

 

<form>
  <label>DHCP leases by Vendor</label>
  <fieldset submitButton="false" autoRun="true">
    <input type="text" token="tMAC" searchWhenChanged="true">
      <label>MAC filter</label>
      <default>*</default>
      <initialValue>*</initialValue>
    </input>
    <input type="time" token="_time">
      <label>Period</label>
      <default>
        <earliest>-30d@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history Issued  MAC_DUID=$tMAC$ | table MAC_DUID  | dedup MAC_DUID | rex field=MAC_DUID "(?&lt;Vendor&gt;\w{2}\:\w{2}\:\w{2}):"   | stats count as CNT by Vendor | sort -CNT</query>
          <earliest>$_time.earliest$</earliest>
          <latest>$_time.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
</form>

Vadim

Re: DHCP leases statistics by Vendor

[ Edited ]
GHorne Community Manager
Community Manager
Posts: 254
5361     1

Is there a vendor Prefix lookup table available that could turn these addresses into plain text ?

Re: DHCP leases statistics by Vendor

[ Edited ]
Expert
Posts: 181
5361     1

Humm, Must have hit some internal limits.   Running for a day or two, works fine and I get believable results.   Running for 30 days as posted, The first several pages of MAC's have exactly 10,311 hits.  

Not that I would really ever need to run it over a few days as a trouble shooting tool.


***EDIT***  I never could duplicate the result where I had the same count on many different MAC's.  Not sure what happened that time.

Re: DHCP leases statistics by Vendor

Expert
Posts: 181
5361     1

Here is a version that combines the DHCP top talkers by MAC and by OUI as above.   It also adds the vendor lookup. 
I am using this as a trouble shooting took looking for individual devices or device types that are stuck in some kind of a loop, pounding on our DHCP servers, so I removed some of the de-duplication of the records in the above example as well.

 

For the vendor lookup I added in the below CSV into the reporter without any modification.

https://regauth.standards.ieee.org/standards-ra-web/pub/view.html#registries

 "MAC Address Block Large (MA-L)"  CSV


<form>
  <label>DHCP Top Talkers V2</label>
  <fieldset submitButton="true">
    <input type="time" token="time">
      <label>Time</label>
      <default>
        <earliest>-2h</earliest>
        <latest>now</latest>
      </default>
    </input>
    <input type="multiselect" token="members">
      <label>Members</label>
      <choice value="*">All</choice>
      <search>
        <query>index=ib_dhcp_summary report=si_dhcp_message
               | stats count by orig_host</query>
        <earliest>$time.earliest$</earliest>
        <latest>$time.latest$</latest>
      </search>
      <fieldForLabel>orig_host</fieldForLabel>
      <fieldForValue>orig_host</fieldForValue>
      <default>*</default>
      <prefix>(</prefix>
      <suffix>)</suffix>
      <valuePrefix>host="</valuePrefix>
      <valueSuffix>"</valueSuffix>
      <delimiter> OR </delimiter>
      <initialValue>*</initialValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <title>DHCP Top Talker</title>
        <search>
          <query>sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history dhcpd OR dhcpdv6 r-l-e $members$            
            | eval Protocol=if(PROTO=="dhcpdv6","IPV6","IPV4")           
            | lookup os_number_fingerprint_lookup OS_NUMBER output SFP           
            | eval FINGER_PRINT=if(isnull(OS_NUMBER) OR OS_NUMBER==0,FP,SFP)           
            | lookup nios_member_ip_lookup host output MEMBER_IP           
            | lookup fingerprint_device_class_lookup FINGER_PRINT output DEVICE_CLASS            
            | eval DEVICE_CLASS=if(isnull(DEVICE_CLASS), "Modified or Deleted", DEVICE_CLASS)            
            | rename host as "Member", ACTION as "Action", LEASE_IP as "Lease IP", MEMBER_IP as "Member IP", OPTION12HOST as "Host Name", FINGER_PRINT as "Fingerprint"          
            | stats values(Member) AS "Member Name" values("Lease IP") AS IP  values("Host Name") AS Host_Name count as PacketCount by MAC_DUID           
            | eval IP=mvindex(IP,0,10)
            | eval Host_Name=mvindex(Host_Name,0,10)
            | sort -PacketCount</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="maxLines">10</option>
        <option name="wrap">false</option>
        <option name="rowNumbers">false</option>
        <option name="drilldown">none</option>
        <option name="dataOverlayMode">none</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table>
        <title>By MAC Vendor</title>
        <search>
          <query>sourcetype=ib:dhcp:lease_history index=ib_dhcp_lease_history $members$  dhcpd OR dhcpdv6 r-l-e
            | table MAC_DUID
            | rex field=MAC_DUID "(?&lt;Vendor&gt;\w{2}:\w{2}:\w{2})"
            | eval Assignment=upper(Vendor)
            | rex field=Assignment mode=sed "s/\://g"
            | lookup vendortoMAC Assignment output "Organization Name"
            | stats values("Organization Name") as "MAC Vendor" count as CNT by Vendor
            | sort -CNT</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
      </table>
    </panel>
  </row>
</form>




 

Re: DHCP leases statistics by Vendor

macfee
Techie
Posts: 2
5361     1

David, 

 

I am loving this thread!

So many of our customers are having simular issues determining where those broken phone/printer/PC's are located. This is a great way in assisting them in tracking down those nasty clients!!

 

Ed Fee

Showing results for 
Search instead for 
Do you mean 

Recommended for You