hi, in this video i show you how to monitor ip devices using microsoft excel
Here is my spreadsheet file with some ip devices to monitor
This file will help me monitor devices like router, computer and tell me whether they are online or offline
If the ip device is online, what is its response time?
Microsoft Excel's menu doesn't have Developer by default so I'll add it
Next I open the Visual Basic window and add a new Module for this workbook
Then I add the commands to create the necessary functions for the network monitoring tool
I prepared two functions to monitor ip devices
I put this file in the description of the video
A function called ping to check if the target ip address is online or offline
The second function will tell me the response time of the target ip address
To use the above two functions I save the spreadsheet file with the extension xlsm
This is my spreadsheet file and you will see its extension xlsm
Next steps I will use this file
This new file will allow me to use the newly added functions to monitor ip devices
So how to use those functions to monitor my target ip devices?
First is to check if ip address 10.11.32.1 is online or offline
I will use IF function and Ping function to check the status of ip address 10.11.32.1
If this ip address is online it will return UP, otherwise it will return DOWN
Same for other ip addresses and i see that only ip address 10.11.32.191 is offline
I want to know the response time of each ip address when it is online
So I use the IF command and the response time function to display the response time of each ip address
If that ip address is offline, its response time is not displayed
Next I will highlight the column named status
If an ip address is offline, its status will be red
As for ip addresses that are online, their status will be green
So if there is an ip address that is offline, it will be easier for me to recognize because it has a different color
The current spreadsheet file will not automatically check the status of the addresses
so want to check manually I use the key combination Crtl Shift Alt F9
But I want this tool to automatically check the status of the devices every minute
So I created a new table with the list of names and ip addresses of the devices
Next I delete the original list of names and ip addresses
Instead of entering data manually I will get the data from the right table
I will create a new table and get information like name and ip address from the right table
The data will be mapped from the right table to the left table
Next I will set it so that every minute the data will be refreshed and this way the status of each ip address will be checked every minute
You can change the refresh time depending on your wish
As you can see every minute ip addresses are updated status once and response time is also updated every minute
Thank you for watching don't forget to like and subscribe