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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- powershell 2 0 one cmdlet at a time
- import excel — import and export excel files
- powershell for projectwise how does it work
- pnp powershell admin scripts export list data
- importing and exporting net2 user data
- how to export outlook distribution list to excel
- powershell cheat sheet import export convert
- ad using powershell nmap and excel for inventory
Related searches
- using excel for statistical calculations
- using excel for timesheet
- word and excel for free
- using excel for statistics class
- word and excel for imac
- excel formulas for inventory management
- using excel for inventory management
- get word and excel for free
- excel tool inventory template
- using powershell to open csv
- using excel for statistics
- using excel for financial analysis