AD - Using Powershell, Nmap and Excel for Inventory

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.

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

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

Google Online Preview   Download