Automatic Internet Performance Logger for Windows

Automatic Internet Speed Grapher.md

Automatic Internet Performance Logger for Windows

This collection of utilites is used to create an automatic health check for Internet connection statistics. In its most basic form, it just logs the results to a CSV file, but it can be expanded to also create an automatically expanding graph in Excel if that is desired.

---

Programs Used

• Required
  • Windows CMD
  • Windows Task Scheduler
  • SpeedTest by Ookla CLI utility
• Optional
  • Windows Event Viewer to log the start and stop of the script
  • Nircmd for running silently in the background
  • Excel for creating graphs of the CSV data

---

Instructions

1. Download the required programs, and the optional ones if you intend to follow this guide in its entirety.

2. Create a auto_speedtest.bat script for running the test. Ensure that the location of the log folder is already created.
   The eventcreate commands create a log of run in Windows Event Viewer. This is optional, but can help to confirm that the program is running properly. The content of the file should be like the following:

   eventcreate /T Information /ID <desired_id> /L Application ^
       /SO SpeedTest-Start /D "Started SpeedTest"
   
   "<directory_of_speedtest>\speedtest.exe" --format=csv ^
       >> "<desired_log_location>\logs\speedtest\speedtestresults.csv"
   
   eventcreate /T Information /ID <different_desired_id> /L Application ^
       /SO SpeedTest-Stop /D "Completed SpeedTest"

3. Create the log file at the location you specified, and add the following Header line as the first line of the file ensuring that there is a newline at the end of the file:

   "server name","server id","latency","jitter","packet loss","download","upload","download bytes","upload bytes","share url"
   

4. Open 'Windows Task Scheduler' to create a new repeating task.

   1. Create a 'New Task'.
   2. Ensure to tick 'Run with highest privilages' if you wish to avail of the 'Event Viewer' functionality mentioned above.
   3. Switch to the 'Triggers' tab and create as many or as few triggers as desired for the check.
   4. Switch to the 'Actions' tab and create a new Action.

      1. If you installed the nircmd utility, you should set the 'Program/script' input to point to that, then set the 'Add arguments' input to:

         exec hide <location_of_script>\auto_speedtest.bat

      2. If you did not install the nircmd utility, then set the 'Program/script' input to the location of the script created in [Step 2].

   5. Set the 'Start in' input to the folder containing the script.

5. At this point if you do not want an Excel graph, the "program" is now complete.

6. Create a new Excel file.

   1. In the Banner, navigate to 'Data' and select 'From Text/CSV'.
   2. Find the CSV file created by the program and import it.
   3. Select any part of the data, and select 'Data > Queries & Connections > Properties' from the Banner.
   4. From the window that opens, select the 'Query Properties' button.
   5. Make sure that the following boxes are ticked:
      1. 'Enable background refresh'
      2. 'Refresh every X minutes'
      3. 'Refresh data when opening the file'
      4. 'Refresh this connection on Refresh All'
   6. In another Sheet, create an empty graph.
   7. Select the graph, then select 'Chart Filters > Select Data'.
   8. Add a new Series.

      1. Set the name to be the name of the column

         =<name_of_sheet>!$<column>$<row>
         
         # Example
         =Sheet2!$F$1
         

      2. Set the values to be the values of the column, minus the header

         =<name_of_document>__<sheet_num>[<column_name>]
         
         # Example
         =myWorkbook__2[download]
         

7. Test the "program" by manually running the task in 'Task Scheduler'. It should run the program, dump the results to the CSV file, and the graph(s) should update when you press the 'Data > Queries & Connections > Refresh All' button in the Banner in Excel.