AD - Using Powershell, Nmap and Excel for Inventory

[Pages:8]AD - Using Powershell, Nmap and Excel for Inventory

First, you want to load Windows Powershell ISE, Be sure to Run ISE as Administrator.

This command is spread across two lines to fit on the screen, it queries Active Directory (AD) for all computers Registered in AD, and I've chosen to limit the return values to Seven Fields. I've also chosen to Export this to a .CSV file for importing into an Excel Spreadsheet.

Highlight the selection and click F8 Or this button to run the command.

Once the command is finished, you can load It into Excel, and sort your data. I sort by Enabled first, to move those that are Disabled to another sheet, then I sort by Operating System to separate Server Operating Systems from Workstations.

Once I've moved those out of the way, I sort the Remaining computers by the IPv4Address column.

Then, you can run the NMAP command in Powershell (with the right command-line options) To retrieve the MAC Address for each IP Addressed Device on the network.

I choose a step by step process to extract the IP Addresses from the Nmap listing once loaded Into Excel.

Using the Find function to locate the beginning Of the IP Address in the text string, then using The Mid function to extract the remainder of the IP Address substring.

I was able to use only one step to extract the MAC Addresses, because they are All the same length.

Since we have either the IP Address, or the IP Address with a ")" immediately following. The IF statement will return a truncated Substring (using the LEFT function) or the String without modifications.

Next, we define two ranges holding the Data we want to filter out of all those #value! Entries..... Ranges take a selection That we choose and give them a reference Name. I've done this for both the columns With IP Addresses and Mac Addresses.

Using the INDEX function in concert With the ROW function, we can Filter out the good info from the Garbage. Note the multiplier on the ROW command, this gives us the Good data from every 3rd row.

Once the information is filtered and Lined up, we simply copy paste, using The paste BY VALUE option, which Copies the actual values and not the Functions.

Now that we have our data values lined up, we Want to integrate this listing with our other data. Since both data tables contain an IP Address, we Can use this to line up the MAC Addresses with The corresponding devices.

First, we put the common value on the left side, Then we create another range, including both Columns.

Now, we use the VLOOKUP function to locate the Matching IP Address from our lookup range, and Place the correct Mac Address in the correct row.

This function is in box I2 (eye 2), and it uses The value in E2 to find The correct MAC Address in the lookup Range.

Note: I happen to have moved the lookup range before taking screen shots....

After you are done with your lookup Table, you can hide the columns for Presentation purposes.

Later, of course, highlight around The selection that's hidden to Unhide your data/formulas. Note: If you've hidden the A Column, Select the left most column to unhide.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download