ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to Passively Track Network Access Using Perl, SNMP, and SQL

Updated on July 29, 2018

What's in your network?

Do you have more than 20 Ethernet switches across your networks? Do you route between more than 5 IP routers? Do you have any idea who's connected to the edge of your network? I run a mid-sized network for a private university. I have picked up a trick or two over the years, and would like to share a couple of ideas with you. I hope to make your life easier by the end of this article.

I get the occasional phone call asking me if I can find where this MAC address is. Or maybe it's an off-site abuse complaint about a certain IP address at a certain point in time. Do you dread that phone call? I look forward to it. I have a plan. I'm prepared. I will show you how I do it.

Readers of this tutorial need to have some background in

  • writing Perl scripts
  • creating and querying SQL tables
  • querying network equipment with SNMP
  • router administration


Address Resolution Protocol

I don't know anything about your particular network. That's the beautiful thing about computer networks: there are 1,001 ways to solve any given problem. The most common way I've personally encountered of aggregating and distributing access networks is with the three-tiered approach shown in the side bar.

Whether a network routes at the access edge, or defers routing until the core, IP networks must make the transition from layer 2 to layer 3 somewhere. And when they do, there is a router that tracks which MAC address resolves to which IP address in order to deliver packets across the last hop to the destination. Every IP-speaking Ethernet device has an ARP table, and routers are no different.

The MAC address uniquely identifies a network adapter on a local segment. But what if you're tracking down an IP address? As long as the device you're looking for is currently online, then you're almost done. Log on to the router with the subnet for that IP address, show the arp table, and there is your MAC address. Nothing new here.

But what about going back to see who was on that IP address 36 hours ago? Six weeks ago? Six months ago? Most ARP entries time-out after 5 minutes. Some routers hold on longer, but never more than a couple of hours. If you want historical lookup, you will have to consider committing your ARP to persistent storage. You speak Perl, right? Let's head over to CPAN and get the DBD::SQLite package. (You already have DBI, surely!) While you're installing that piece, keep reading. We'll get back to the SQL setup a little later.

Now we need to figure out ... how to harvest the ARP table from the routers ... and how to transfer this information to a database. It's not that hard! Let's keep going.

Setting up a Perl Net::SNMP object for a call to get_next_request
Setting up a Perl Net::SNMP object for a call to get_next_request | Source
results from Net::SNMP get_next_request query
results from Net::SNMP get_next_request query | Source

Simple Network Management Protocol

Does SNMP live up to its name? "Simple"? I say yes. It's just a database engine that answers queries. The tables consist of rows of keys and values. Both the names of the tables and the keys within the tables are decimal-separated numeric strings, or OIDs.

The nut we want to crack here is a table called ipNetToMediaPhyAddress. That's the SNMP name for the ARP table. Its OID is (Look for a MIB parser to help figure out the name-to-OID lookup.)

Before we go too far, make sure you have SNMP access to your router. If you have snmp utilities installed, point to your router and ask this question:

snmpgetnext -c public

You'll get back the first row from the router's ARP table. Mine said it like this:

IP-MIB::ipNetToMediaPhysAddress. = STRING: 00:11:88:05:5f:2e

Sounds like the next step is to bring the SNMP queries into Perl. Let's head back to CPAN and this time, let's install Net::SNMP. Initialize your SNMP object with the right host name and credentials. Then check the picture on the right labeled Setting up ... Right after I run this script, I get the results in results from... The format of the answer is a little different this time. The key is numeric, and the value is printed in hexadecimal notation, without any separators. No worries, we'll get some regex to whip that into shape (see hex_to_mac function below).

quick function to convert hex strings into colon-separated MAC notation
quick function to convert hex strings into colon-separated MAC notation | Source
Example of Perl loop over an SNMP get_next_request query
Example of Perl loop over an SNMP get_next_request query | Source

Walking down the SNMP table

Without getting too deep into the details of SNMP, you need to know that each snmp_get_next returns the answer to the previous query, as well as a key that points to the next query. See Example of Perl loop ... for a snippet of Perl that feeds the next key into the query.

Create a table called arpCache in sqlite
Create a table called arpCache in sqlite | Source
Initialize your table with your full IP range using a dummy start date and zero MAC address
Initialize your table with your full IP range using a dummy start date and zero MAC address | Source
insert query to use with DBI's prepare statement
insert query to use with DBI's prepare statement | Source
Call execute on DBI statement handle
Call execute on DBI statement handle | Source

SQL, the Swiss Army Knife of data

Create a table to represent your IP space. My workplace has a Class B, which means I can capture our entire ARP space in 64K rows. (Less, because we're not fully utilized, but I digress.) Look for the sidebar titled Create a table ... I set initial values to default as I load the IP addresses in Initialize your table ...

The stage is set. All the tools are sitting in front of us. All that's left to do is bring it all together.

  • Query the arp table.
  • Insert the results into the database.
  • Put all that on a schedule and repeat at regular intervals.

Before inserting a row into your database, capture a statement handle as the return value from the prepare function on your DBI object. See the screencap for a suggested insert query. Then as you iterate over the SNMP results, push them into the table by calling the execute function on your statement handle, as shown in the next screencap.

With Linux or Mac OS, you have the built-in cron system to set up a scheduled task. I haven't attempted to query SNMP via Perl on Windows, let alone set it up on a schedule ... so I don't have much to offer there. Setting aside Windows for now, back to the crontab editor, set up your querier script to run against your router every n minutes, where n represents the router's arp timeout setting.

2014-02-20 23:41
2014-02-21 00:11
Sample data for arpCache DB table

Cache the ARP table

The end goal here is to query the ARP tables from all the client-facing routers, and then keep these records long enough to help out with later inquiries. If your organization hasn't yet established retention policies, just pick a number: 30 days? 6 months? At some point, you run out of disk space and DBMS performance, so don't get too crazy.

SQL has more tricks than we have time to discuss in this tutorial. I'll leave it as an exercise for the reader to figure out how to optimize the database operations beyond this introductory example. Some things to consider:

  • the schema presented in this example presents a "most recently seen" MAC per IP
  • how could you capture all MAC/IP pairs without bloating your database?
  • what kinds of view optimizations are possible using cidr math?
  • how do you normalize MAC addresses across various router platforms?
  • could a similar idea apply to capturing bridge tables for MAC-to-physical-port?

I hope I got your curiosity up with this lightning flash through the idea of querying and caching arp tables. Let me hear from you in the comment section, and I will do my best to answer any questions you have.

I found the hub useful, but I would like more information on

See results


    0 of 8192 characters used
    Post Comment

    No comments yet.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)