CSV Imports and Exports

Introduction

The CSV Import feature of Grid Manager provides one way to perform “bulk” updates which create, modify, or delete many DNS and/or DHCP objects at once. Not every imaginable use case is supported, but most common tasks are fairly straightforward.

Caution: it is not possible to “undo” or roll back a CSV import after it is executed!

When in doubt, we highly recommend using development IPAM for proof-of-concept testing.

Note that you can also perform “bulk” updates programmatically using the IPAM API; the advantages and disadvantages of each approach vary depending on the nature of the task at hand.

Creating CSV Data Files for Import

For your convenience, Technology Services has created ready-made template data files for a number of common tasks, which can be used for their intended purpose just by reading and following the instructions on this page.

If you wish to adapt any of the templates for other purposes, or to create your own data files from scratch, please refer to CSV Import Reference in the vendor documentation.

Performing a CSV Import

Once you have created a CSV data file, use the following steps to import your data file into Grid Manager:

  1. Click “CSV Import” on the Toolbar panel to bring up the CSV Import Wizard.
  2. Choose the desired import type:
    • Add
      Adds a new Grid Manager object based on the data in each row (will fail if an object with the same required field values already exists).

    • Override
      Uses the required field values in each row to select an existing Grid Manager object (will fail if no suitable object exists), then overwrites any other attributes of that object specified in the remaining columns.
    • Merge
      Uses the required field values in each row to select an existing Grid Manager object (will fail if no suitable object exists), then fills in any attributes specified in the remaining columns for which the object does not already have a value.
    • Delete
      Uses the required field values in each row to identify and delete an existing Grid Manager object.
    • Custom
      See vendor documentation.
    • Replace
      See vendor documentation; use with caution.
  3. Click “Next”.
  4. Click the “Choose…” button and choose a CSV file to upload.  The file name should appear in the text box.
  5. Optionally select how Grid Manager should behave “On error”.

    Note: no matter which behavior you select, any rows before the first error row will always take effect.
  6. Click “Next”.
  7. The first few rows of your data file should appear in the preview area (separated into columns); make sure it looks reasonable.
  8. Click “Import” to begin the operation.

    Note: if you click “Save & Close” at any point prior to clicking “Import” in this step, your job will be saved but not executed.  See Managing Existing CSV Jobs (below) to find it again.

  9. The “CSV Import Progress” window will appear, showing the current status of your job (probably “Import pending”).
  10. You may wait here for your job to complete; when it does (typically within about 30 seconds), the current status will change to “Import successful” or “Import unsuccessful”.
    Alternatively, you may click “Close & Run in Background” to dismiss the dialog box and work on something else while you wait; see Managing Existing CSV Jobs (below) to find it again.
  11. Once the job has completed, check the “Rows with errors” counter to see if any errors were generated.
    • If so, click the “Download Errors” button to retrieve another CSV file containing the error rows (prepended with their corresponding error messages).
    • Remember: any rows from your original CSV file that did not produce errors will still have been imported!
  12. Click “Close” to dismiss the dialog.

Managing Existing CSV Jobs

Click “CSV Job Manager” on the Toolbar panel to display a table containing your CSV jobs submitted within the last 30 days.

Use the hamburger menu icon in each table row and click “Edit” to open the Edit CSV Import Job dialog box, which includes an “Import” button to execute the job.

Grid Manager allows you to Edit a job which has already been executed and click Import to execute that same job again without re-uploading the file, but if you do so, the information about the previous run (timestamps, error file, etc) will no longer be available in CSV Job Manager.  It is usually preferable to start a new import job instead.

You can also use the hamburger menu to Delete a job which has not yet been executed, or Download the Error File from a job which terminated unsuccessfully.

Templates

The following templates are available to help you perform common tasks via CSV Import.

Each template contains at least one header row which can be used exactly as provided, followed by one or more sample object rows which should be modified (and more rows added if necessary) to reflect the actual data you wish to import. The header row beginning with e.g. “HEADER-Foobar” tells Grid Manager how to interpret the columns of the corresponding object rows beginning with e.g. “Foobar” (which must be a valid object type).

Some templates include columns whose values are described as optional; you may safely choose to leave these columns blank when filling in your object rows.

  • Create new DNS-only Host Records
    Type: Add
    Importing this spreadsheet will create a new Host Record for the FQDN in each row, with the specified IPv4 and/or IPv6 address(es) and Host Aliases. You must specify at least one address. The “aliases”, “EA-Property Tag” (Property Tag extensible attribute) and “comment” values are optional. Host records created using this template will not be enabled for DHCP.
  • Create new DHCP-enabled Host Records
    Type: Add
    This template expands on the previous one to create new Host Records whose IP addresses are also enabled for DHCP. Each new Host record to be created needs multiple CSV rows:
    • one “HostRecord” row specifying the FQDN and all of its associated IPv4 and/or IPv6 addresses (as well as optional “aliases”, “EA-Property Tag”, and “comment” values)
    • zero or more “HostAddress” rows to enable the DHCP checkbox and specify the associated MAC address for each individual IPv4 address within the Host
    • zero or more “IPv6HostAddress” rows to enable the DHCP checkbox and specify the associated DUID for each individual IPv6 address within the Host
  • Enable DHCP for Existing Host Addresses
    Type: Override
    For each row, “parent” and “address” must match the FQDN and an IPv4 or IPv6 address of an existing Host record in IPAM. Importing this spreadsheet will enable the DHCP checkbox for that IP address and overwrite its associated MAC address or DUID (as described in Adding DHCP to a Host record).
    Hint: you can obtain names and IP addresses of existing Host records on a network (to use as a starting point) by opening the Network in IPAM view and exporting visible data.
  • Add IP Addresses to Existing Host Records
    Type: Add
    For each row, “parent” must match the FQDN of an existing Host record in IPAM. Importing this spreadsheet will assign an additional IP address to that Host record. Optionally, you may choose to enable DHCP for newly added IP addresses.
  • Create Stand-alone DNS Records
    Type: Add
    Use this template to create new Stand-alone DNS Records of various types. Each type has its own header row; please omit any unused header rows from your data file. All “comment” values are optional.

    Keep in mind that Host Records are often (but not always) preferable to stand-alone A and AAAA records.

  • Importing this spreadsheet will create a new DHCP Fixed Address with the IP address and MAC address or DUID specified in each row. The “EA-Property Tag” (Property Tag extensible attribute) and “comment” values are optional.

  • Add MAC Addresses to a MAC Address Filter
    Type: Add
    Importing this spreadsheet will add the MAC address in each row to the MAC Address Filter whose name appears in “parent”. The “registered_user”, “EA-Property Tag” (Property Tag extensible attribute), and “comment” values are optional.
  • Modify Existing Stand-alone DNS Records
    Type: Override
    For each row, the required fields (indicated with asterisks) must match an existing Stand-alone DNS Record in IPAM.  Importing this spreadsheet will modify that record according to the values specified in the remaining columns.  To modify a required field such as the address of an A record, specify the old value in “address” and the new value in “_new_address” (leave “_new_address” blank if you don’t want to change the address). Each type has its own header row; please omit any unused header rows from your data file.

Exporting data from Grid Manager

Many tables in Grid Manager can be exported as a downloadable CSV in two different ways, using the drop-down menu for the Export (up arrow) button:

screenshot

  • Choose “Export visible data” to download a CSV containing precisely the columns and values that are actually visible within the current table (i.e. as shown on the screen, except that all pages of the table are included).
  • Choose “Export data in Infoblox CSV Import Format” to download a full CSV representation of the Grid Manager objects that appear in the current table, including all of their configuration attributes.

Other tables in Grid Manager provide only a single Export button with no drop-down menu; this is equivalent to “Export visible data” as described above.

You can restrict which rows (objects) are included in the CSV by applying a Filter to the table beforehand.

Managing DHCP Leases

This page contains instructions for viewing and clearing DHCP Leases.

Introduction

A DHCP Lease represents a DHCP server’s knowledge that a particular IP address has been allocated to a particular client for a specified period of time.

Note: because of the way DHCP Failover works, each dynamically allocated IP address actually has two corresponding Lease objects in Grid Manager – one for each DHCP server in the failover pair.

Grid Manager may also display “Static” Lease objects for IP addresses that have been assigned to a client via DHCP Fixed Address (or DHCP-enabled Host); note that these behave differently from regular Leases because fixed addresses are timeless.

Displaying Leases

There are three different ways to display Leases in Grid Manager. In all cases, the Lease objects on the servers are updated in real time; use the Refresh icon at the bottom of the window to see the latest data.

Viewing Leases in a single DHCP Range

You can view all free and assigned Leases in a particular DHCP Range by opening the Range object:

  1. In DHCP View, Open the DHCP Network to which the DHCP Range belongs (see Getting Started with IPAM).
  2. Open the Range by clicking on its address range displayed in the IP Address column.

    You can add the MAC Address column to this table.

Any “Static” Leases for IP addresses within the DHCP Range will also be included.

To see the details of an assigned Lease, select it and choose “Lease Details” from the toolbar panel on the right.

Viewing Leases in a Network

You can see the status of each IP address on a Network, including whether it is currently assigned as a Lease, by opening the Network in IPAM View (see Getting Started with IPAM).

Known Issue: in IPAM view, “Static” Leases are only displayed if the IP in question happens to fall within a DHCP Range.

The Lease State and MAC Address are visible in columns of the table (if you have selected List display).

To see the full details of an assigned Lease:

  1. Open the IP address (in List display), or select it on the graphical map (in IP Map display).
  2. Select the Lease from the Related Objects table.
  3. Choose “Lease Details” from the toolbar panel on the right.

Viewing all Leases in all of your Networks

You can view all Lease objects on all of your Networks at once (including “Static” Leases) by navigating to Data Management > DHCP > Leases > Current Leases using the rows of tabs at the top of the screen.

screenshot

This is by far the slowest method of examining Leases, because Grid Manager has to check every Lease object in its database against your individual user permissions in order to determine which ones should be displayed.  Performance can be improved by using a Filter to narrow the scope of your interest; for example,

  • IP Address belongs to “192.168.0.0/26” (pictured in screenshot above) for a CIDR match, or
  • IP Address begins with “192.168.0.” for a plain string match.

Known Issue: when you first click on the Current Leases tab, you may need to wait up to 2 minutes for the unrestricted search to time out before you can successfully apply a Filter.

Note that free addresses which do not have Lease objects yet (because they have never been assigned to a client) will not be displayed in this view.

To see the details of an assigned Lease, select it and click the Lease Details (paper) icon above the table.

Clearing Leases

The DHCP protocol specification does not empower the server to revoke a client’s lease once it has been issued.

However, you can force the DHCP servers to forget that a lease has been assigned (thereby making it available for assignment to other clients) by selecting it and clicking “Clear Lease” from the toolbar (in IPAM view, click the drop-down arrow next to “Clear” and then choose “Clear Lease”).

Use extreme caution when clearing leases, as it may cause IP address conflicts on your network!

Even after a lease has been cleared in Grid Manager, the client is within its rights to continue using the allocated IP address until the original expiration time.

In general, you should clear a lease only if you are absolutely certain that the client has been removed from the network.

Note that “Static” Leases cannot be cleared this way, but will be automatically removed by Grid Manager if you modify your DHCP configuration in a way that would permit the IP in question to be allocated to a different client.

Abandoned Leases

An Abandoned lease marks an IP address which is configured for DHCP dynamic allocation but has been abandoned by the DHCP server as unfit for use, due to one of the following occurrences:

  • After receiving a DHCPDISCOVER, the server selected this IP address to be offered, but received a response to its ping check.
  • After sending a DHCPACK for this IP, the server received a DHCPDECLINE message from the client indicating that the IP address is already in use (typically because the client issued an ARP request for the IP address and received a response, as described in RFC 2131 section 4.4.1).

Abandoned leases do not expire but remain Abandoned indefinitely, at least until the supply of Free/Backup leases becomes exhausted.  When a DHCPDISCOVER is received and there is no Free/Backup lease available to fulfill it, DHCP will automatically attempt to reclaim one Abandoned address and offer it to the new client (after performing the usual ping check).  However, if the reclaimed address is still in use, it will be marked Abandoned again and no DHCPOFFER will be sent, meaning the client will fail to obtain a lease (even though there might be other Abandoned addresses which aren’t actually still in use – in that case, future DHCPDISCOVER attempts by the same or other clients will eventually fare better).

What to do about Abandoned leases:

  • Try to avoid them in the first place by making sure that clients on your network are correctly configured to use DHCP and, if your network includes any manually-configured devices, making sure that DHCP is not configured to hand out those IP addresses.
  • If you do accumulate a significant number of Abandoned leases, identify any addresses which are not actually still in use on the network, and manually Clear them from Grid Manager.

    Hint: to see only the Abandoned addresses within a particular network, open the Network in IPAM view (as in Viewing Leases in a Network above), and apply Filter: Lease State equals Abandoned

MAC Address Filters

This page contains instructions for creating and managing MAC address filters for DHCP.

Introduction

A MAC Address Filter is a Grid Manager object which keeps track of a set of MAC addresses.

MAC Address Filters are typically used to restrict access to IPv4 DHCP Ranges. Once you have configured your MAC Address Filter the way you want it using the instructions below, see Restricting Access to a Range to apply it to a DHCP Range.

Creating a new MAC Address Filter

If you need a new MAC Address Filter, please email hostmgr to request one. Provide the following details:

  • a desired name for the new MAC Address Filter object.

    There is a single global namespace for all Filters, so try to choose something reasonable that incorporates the department or group for which this filter is relevant. If in doubt, explain what you plan to use it for, and we will help you choose a suitable name.
  • the name(s) of one or more network models (from Contacts Database) that should confer “ownership” rights to this MAC Address Filter. Any user with permissions on any of the named network models will automatically be given permission to manage this MAC Address Filter as well (updated nightly).

    These network model names will be stored in Grid Manager as values of an Extensible Attribute called “Owned By Network”. Modifying or removing this attribute may result in loss of permissions on the MAC Address Filter.

Managing MAC addresses in a MAC Address Filter

To open a MAC Address Filter:

  1. Navigate to Data Management > DHCP > Filters using the rows of tabs at the top of the screen.
  2. Open the desired MAC Address Filter by clicking on its name in the table.

This will display a table containing all MAC addresses currently present in the MAC Address Filter.

If your table contains many rows, see Using the Table Controls in Getting Started with IPAM.

Adding a MAC address

After opening a MAC Address Filter as described above,

  1. Click the Add (+) icon above the table.
  2. Enter the desired MAC address.
  3. Optionally, you may set an expiration time at which this MAC address will be automatically removed from the MAC Address Filter.
  4. Click “Next” at the bottom of the dialog window.
  5. Optionally, you may enter a username in the “Register as User” field to associate with this MAC address (for your own tracking purposes).  If you don’t want to do this, just leave it blank.

  6. Click “Save & Close”.

Removing a MAC address

After opening a MAC Address Filter as described above,

  1. Select the checkbox for the MAC address you wish to remove (making sure no other checkboxes are selected).
  2. Click the Delete (trash can) icon above the table.
  3. If you’re sure, click “Yes” when the confirmation dialog appears.

IPAM Training Quick Reference

go.illinois.edu/ipamtraining (this page)

  1. Open https://dev.ipam.illinois.edu
  2. Double-check that the login screen says “DEVELOPMENT GRID”!
  3. Click “SSO Login” or see go.illinois.edu/ipamlogin for detailed login instructions
    (If you don’t have any Contacts Database permissions, contact hostmgr to request demo access to dev.ipam)
  4. Follow along with the live training instructions or the self-guided tutorial in Getting Started with IPAM

Dummy Objects

Dummy objects (in development grid only) that everybody has permissions on, for training purposes:

  • sandbox.illinois.edu
  • 192.168.0.0/26 (sandbox-net)
  • 2001:db8::/64 (sandbox-net)
  • sandbox-macfilter

Sample dig commands

  • dig @dev.ipam.illinois.edu hostname.sandbox.illinois.edu IN A
    (variations: “IN AAAA”, “IN CNAME”, “IN any”, etc)
  • dig @dev.ipam.illinois.edu -x 192.168.0.5

IPAM Service Documentation

The service documentation at go.illinois.edu/ipam contains step by step instructions for common tasks.

DHCP Standards

Lease Time Guidelines

The default DHCP lease time is 1 day, which is well suited for nets with low turnover (i.e. the population of client machines is fairly stable and doesn’t change much).

Network administrators may choose to configure a different lease time on individual IPv4 DHCP Ranges (Dynamic Pools), subject to the following guidelines which are designed to keep overall load on the DHCP servers from growing unsustainably:

Lease time Guideline
1 day or longer Recommended for most networks
between 4 hours and 1 day No problem, use freely where desired for IPv4 nets with higher host turnover
between 1 hour and 4 hours Acceptable, but please exercise good judgment and use only where necessary
less than 1 hour Requires approval from service manager

Because IPv6 subnets are so spacious, there is generally no reason to reduce the DHCPv6 lease time parameters; IPv6 nets with higher host turnover which require Stateful autoconfiguration can simply be configured with larger DHCP Ranges in order to avoid running out of leases.

Range Size Guidelines

IPv4 DHCP Ranges (Dynamic Pools) should be sized so that the number of unassigned IP addresses in the Range (after all desired clients have obtained their leases, and not counting any addresses within Exclusion Ranges) will be at least 1-5% of the total, with a minimum of at least 2 unassigned IP addresses (even for very small Ranges) in order to properly utilize the redundancy provided by DHCP Failover.

Total Addresses in Range Minimum Unassigned Addresses
3-40 at least 2 addresses
41-200 at least 2-10 addresses (1-5% of total)
201+ at least 1-5% of total

Note: Email Alert thresholds should also be adjusted accordingly (to higher than 95%) if the expected number of unassigned IP addresses is less than 5% of the total.

Service Defaults

The following DHCP settings serve as defaults; they are configured at the top level of the Grid hierarchy and automatically inherited by all Networks. An individual Network, DHCP Range, DHCP Fixed Address, or DHCP-enabled Host can override one of these defaults by specifying its own value for the setting.

Client options

For DHCPv4:

code name value
6 domain-name-servers 130.126.2.131
42 ntp-servers 130.126.24.24, 130.126.24.44, 130.126.24.53
252 wpad-url ” ” (a blank space)

Proactively distributing a blank space for option 252 forestalls the Windows client behavior of continually sending DHCPINFORM requests to the DHCP server in an attempt to “automatically detect proxy settings”. This option value can of course be overridden with a real WPAD URL on individual subnets where automatic proxy configuration is desired.

For DHCPv6:

code name value
23

name-servers

2620:0:e00:a::1

Service settings

For DHCPv4:

  • Lease Time: 1 day (see Lease Time Guidelines above)
  • Authoritative: true
  • Ping Check: 1 request, with 1 second timeout

    This setting cannot be overridden for individual Networks or Ranges.

  • Enable DDNS Updates: false

    ddns-updates false prevents the DHCP server from attempting to do dynamic DNS updates on behalf of clients that choose to send a Client FQDN value in their DHCPREQUEST. This setting is not one of the options transmitted to clients, and will not affect their ability to perform their own dynamic DNS updates in Active Directory (or any other DDNS server).

For DHCPv6:

  • Valid Lifetime: 2 days
  • Preferred Lifetime: 1 day

  • Enable DDNS Updates: false