# Regular report script for Mikrotik routers.

# This script prepares a report and sends it by email with a config file for backup
# Begun 30 March 2020, Denis de Castro Biomedical Consulting, BMCP@pm.me

# Email should be already set up in Tools>Email

# Please put the email address to send your report to here:

:local addressto "you@domain.tld" 

# Report version 36, 28 June 2023 (and please copy your version number into the next line)

:local reportVersion "36"

# Please enter the name of this script from System>Scripts

:local scriptName "weekly-report"

# to export this file to use on other devices, you can use '/system script export file=currentScripts' in the Terminal

# changes
# v. 36. Add MAC lookup at macvendors.com.
# v. 35. Do not attach rsc config. file--now sent separately. 
# v. 34. For Caruso a CRS109 only. Now rejoined 30 June 23.
# v. 33. Add IPv6 information
# v. 32. Add listing of active ethernet ports
# v. 31. Changes in reporting last restart date etc.
# v. 30. Add 'from' date for traffic volumes and reset of all counters on ethernet ports
# v. 29. Add information about traffic volumes
# v. 28. Add 'Unknown hostname' for some items in Static Leases

################## Functions #######################

# replace characters in a string by Alice Vixie 
:global replaceChar do={
  :for i from=0 to=([:len $1] - 1) do={
    :local char [:pick $1 $i]
    :if ($char = $2) do={
      :set $char $3
    }
    :set $output ($output . $char)
  }
  :return $output
}

# Get traffic on interface by Denis de Castro
# Sample call to function: ':local newValue [ $getTraffic ether1 driver-tx-byte ]'
# Note, there must be a global declaration inside the calling function to call a global!!!
:local getTraffic do={
  :global replaceChar;
  :local trafficRaw [/interface ethernet get $1 $2 ];
  :local trafficStr ([:tostr $trafficRaw]);
  :local trafficRpl [ $replaceChar [ $trafficStr ] " " "" ];
  :return $trafficRpl;
}

# Values into kiB, MiB or GiB by Denis de Castro
# Sample call to function: ':local numberWithUnits [ $toUnits $thisNumber ]'
:global toUnits do={
  :if ($1 < 1024) do={
    :return "$1 B"
    } else={
    :local stepdown ($1/1024)
    :if ($stepdown < 1024) do={
      :return "$stepdown kiB"
      } else={
      :local step2down ($stepdown/1024)
      :if ($step2down < 1024) do={
        :return "$step2down MiB"
        } else={
        :local step3down ($step2down/1024)
        :if ($step3down < 1024) do={
          :return "$step3down GiB"
          } else={
          :local step4down ($step3down/1024)
          :return "$step4down TiB"
} } } } }

#get vendor info. for a MAC address by Denis de Castro June 2023
#checks if vendor is already known, if not get vendor name online
#uses macvendors.com for the lookup
#thanks to rextended https://forum.mikrotik.com/viewtopic.php?f=9&t=178435&p=879152#p879152
#sample call to this function: ':local newValue [ $getVendor thisMACAddress ]'
:local getVendor do={
    :local OUI ([:pick $1 0 2].[:pick $1 3 5].[:pick $1 6 8]);                                     #calculate the OUI
    [parse ":global $OUI"]; 
    :if ([:len [[:parse ":global $OUI; :return \$$OUI"]]] >0 ) do={                                #if vendor already known
      :return ([[:parse ":global $OUI; :return \$$OUI"]]);
    } else={                                                                                    
      :local onlineSource "https://api.macvendors.com/";                                           #otherwise look vendor up
      :do {
        :local vendorData ([/tool fetch url=( $onlineSource . $1 ) output=user as-value]);
        :delay 150ms;
        :local vendorOutput ( $vendorData->"data" );      
        [:parse "global $OUI;:set $OUI \"$vendorOutput\""];                                        #otherwise add to env.
        :return $vendorOutput;
      } on-error={ 
        [:parse "global $OUI;:set $OUI \"unknown\""];                                              #error if no response
        :return "unobtainable";
  } } }   

############### end of functions ####################

# Collect information
:delay 1;
:local reportBody "";
:local date [/system clock get date];
:local day [ :pick $date 4 6 ];
:local month [ :pick $date 0 3 ];
:local year [ :pick $date 7 11 ];
:local timezone [/system clock get time-zone-name];
:local runcount [/system script get [/system script find name=$scriptName] run-count];  
:local deviceName [/system identity get name];
:local deviceTime [/system clock get time];
:local hwModel [/system routerboard get model];
# note re division technique, see https://forum.mikrotik.com/viewtopic.php?t=11439
:local totalmem [/system resource get total-memory];
:local totaldiskspace [/system resource get total-hdd-space];
:local mempercent ([/system resource get free-memory] * 100 / $totalmem);
:local diskpercent ([/system resource get free-hdd-space] * 100 / $totaldiskspace);
:local totalmemMiB ($totalmem/1048576);
:local totaldiskspaceMiB ($totaldiskspace/1048576);
:local cpuLoad ([/system resource get cpu-load]);
:local upTime ([/system resource get uptime]);
:local rosVersion [/system package get system version];
:local currentFirmware [/system routerboard get current-firmware];
:local upgradeFirmware [/system routerboard get upgrade-firmware];
# obtaining external IP address; see technique with 'fetch' under 'return value to a variable' at https://wiki.mikrotik.com/wiki/Manual:Tools/Fetch m, using parameter 'as-value' and returning 'data'
# this post gave me a working command: https://forum.mikrotik.com/viewtopic.php?t=133725#p727043
# see previous posts in that thread, 'Fetch - How to access data variable?' for information on the 'data' entity
:local remoteIP ([/tool fetch url="https://api.ipify.org/" output=user as-value]->"data");
:local upstreamGW [/ip route get [find dst-address="0.0.0.0/0"] gateway];
:local ipv6WAN [/ipv6 dhcp-client get 0 interface];              #assumes only one WAN if
:local rawPrefix [/ipv6 dhcp-client get [find interface=$ipv6WAN] prefix];
:local ipv6Prefix [:pick $rawPrefix 0 [:find $rawPrefix ","]];             #strip off TTL
:global gwAddress "";
:global upstreamAddress "";
:local prefixDelegator [/ipv6 dhcp-client get 0 dhcp-server-v6];
:local WANaddressIF;
:local WANInfo1; 
:local WANInfo2; 
:local WANInfo3;
:local outVolume;
:local inVolume; 
:local configFile "current_$hwModel_configuration.rsc";
:local updateFlag;
:global lastReportDate;
:local lastReportDay [ :pick $lastReportDate 4 6 ];
:local lastReportMonth [ :pick $lastReportDate 0 3 ];
:local lastReportYear [ :pick $lastReportDate 7 11 ];
:global lastStartDate;
:local lastStartDay [ :pick $lastStartDate 4 6 ];
:local lastStartMonth [ :pick $lastStartDate 0 3 ];
:local lastStartYear [ :pick $lastStartDate 7 11 ];
:global lastTrafficCounterResetDate;
:local lastTrafficCounterResetDay [ :pick $lastTrafficCounterResetDate 4 6 ];
:local lastTrafficCounterResetMonth [ :pick $lastTrafficCounterResetDate 0 3 ];
:local lastTrafficCounterResetYear [ :pick $lastTrafficCounterResetDate 7 11 ];

# Start compiling report 
:set $reportBody ($reportBody."Router report for $deviceName, a Mikrotik $hwModel\n")
:set $reportBody ($reportBody."Sent on $day $month $year at $deviceTime $timezone time\n")

# Last report date, date of last device startup and date of last traffic report
# if no previous lastReportDate, set to today, otherwise show lastReportDate
:if ([:len $lastReportDate] = 0) do={
:set $reportBody ($reportBody . "There is no record of this report being sent previously.\n\n")
} else={
:local lastday [ :pick $lastReportDate 4 6 ];
:local lastmonth [ :pick $lastReportDate 0 3 ];
:local lastyear [ :pick $lastReportDate 7 11 ];
:set $reportBody ($reportBody."The last report was sent on $lastday $lastmonth $lastyear.\n\n");
}
:set $lastReportDate $date;
:local lastStartupDate ($lastStartDay . " " . $lastStartMonth ." " . $lastStartYear);
:local reportingTrafficVolumesSinceDate ($lastTrafficCounterResetDay . " " . \
$lastTrafficCounterResetMonth ." " . $lastTrafficCounterResetYear);
:set $lastTrafficCounterResetDate $date; 

# technical info
:set $reportBody ($reportBody."Uptime (days hours:minutes:seconds): $upTime\n")
:set $reportBody ($reportBody."Date of last restart: $lastStartupDate\n")
:set $reportBody ($reportBody."CPU load: $cpuLoad\n")
:set $reportBody ($reportBody."Available RAM: $mempercent% of $totalmemMiB MiB\n")
:if ($diskpercent < 25) do={:set reportBody ($reportBody . "Alert! ")}
:set $reportBody ($reportBody."Available diskspace: $diskpercent% of $totaldiskspaceMiB MiB\n")

# Update report: system
/system package update check-for-updates without-paging
:delay 3s;
:global InstalledVersion [ / system package update get installed-version ]
:global LatestVersion [ / system package update get latest-version ]
:if ($InstalledVersion != $LatestVersion) do={
:set updateFlag " (Upgrade)";
:log info "New firmware is available";
:set $reportBody ($reportBody . "\nRouterOS version: $InstalledVersion (Version $LatestVersion is \
available to upgrade at Webfig/System/Packages/Check for Updates.  After this upgrade of the system \
software use Webfig/System/Reboot to apply it).\n")
} else={
:set $reportBody ($reportBody . "\nRouterOS version: v.$InstalledVersion (up-to-date)\n")
}

# Update report: bootloader firmware
:if ( $currentFirmware < $upgradeFirmware ) do={
:log info "New firmware is available";
:set updateFlag " (Upgrade)";
:set $reportBody ($reportBody . "Bootloader firmware; v.$currentFirmware (A new version, v.$upgradeFirmware, is \
available at Webfig/System/RouterBOARD. After this upgrade of the firmare use Webfig/System/Reboot to apply it).\n\n")
} else={
:set $reportBody ($reportBody . "Bootloader firmware: v.$currentFirmware (up-to-date)\n\n")
}

# Neighbours
:if ([:len [/ip neighbor find]] > 0) do={
 :set $reportBody ($reportBody . "- - - - - - - - - - - - - Other routers:\n\n");
 :foreach line in=[/ip neighbor find] do={
  :set $reportBody ($reportBody . [/ip neighbor get $line identity]);
  :set $reportBody ($reportBody . ", a Mikrotik " . [/ip neighbor get $line board]);
  :set $reportBody ($reportBody . " at address " . [/ip neighbor get $line address]);
# because multiple interfaces may be listed for one device;
  :local getifs [/ip neighbor get $line interface];
  :local extraifs "";
  :local ifword " on interface ";
  :if ([:len $getifs] > 1) do={
   :set ifword " on interfaces ";
   :for j from=1 to=([:len $getifs] - 1) do={
    :set $extraifs ($extraifs . " and " . [:pick $getifs $j]);
    }
   }
 :set $reportBody ($reportBody . $ifword) 
 :set $reportBody ($reportBody . [:pick $getifs 0] . $extraifs);
 :set $reportBody ($reportBody . " (MAC address " . [/ip neighbor get $line mac-address] . ").\n\n")
 }
 } else={
:set $reportBody ($reportBody . "None\n\n");
}

#Internet
:set $reportBody ($reportBody . "- - - - - - - - - - - - - Internet:\n\n");
:set $reportBody ($reportBody . "IPv4 address of network seen from internet: " . $remoteIP . "\n");
:set $reportBody ($reportBody . "Default route: $upstreamGW\n");
:set $reportBody ($reportBody . "IPv6 address prefix $ipv6Prefix received from $prefixDelegator\n\n");

# WAN 
# Only shows WAN links over ethernet ports, not through SFP or wifi interfaces
# & only shows links by fixed IP address or PPPoE with VLAN, not by DHCP address
:set $reportBody ($reportBody . "- - - - - - - - - - - - - Upstream link(s):\n\n");
:foreach line in=[/interface list member find where list=WAN] do={
  :local thisWANport [/interface list member get $line interface];
  :if ([:len [/interface ethernet find where name=$thisWANport]] > 0) do={
    :local ethernetWANline [/interface ethernet find where name=$thisWANport];
    :local ethernetWANport [/interface ethernet get $ethernetWANline name];
    :local thishostrate ([/interface ethernet monitor $ethernetWANport once as-value]->"rate");
    :set $WANaddressIF $ethernetWANport;
    # maybe WAN address is by VLAN
    :if ([:len [/interface vlan find where interface=$thisWANport]] > 0) do={
        :local vlanLine [/interface vlan find where interface=$thisWANport];
        :local vlanWAN [/interface vlan get $vlanLine name];
        # if so find corresponding PPPoE client
        :local PPPoEclientLine [/interface pppoe-client find where interface=$vlanWAN];
        :set $WANaddressIF [/interface pppoe-client get $PPPoEclientLine name];
    }
    # get the IP address
    :if ([:len [/ip address find where interface=$WANaddressIF]] > 0) do={
       :set $gwAddress [/ip address get [find interface=$WANaddressIF] address];
       :set $gwAddress [:pick $gwAddress 0 [:find $gwAddress "/"]];
       :set $upstreamAddress [/ip address get [find interface=$WANaddressIF] network];
       } else={
       :set $gwAddress "(not available) ";
       :set $upstreamAddress "(not available) ";
    }
  # report WAN
  :set $WANInfo1 ("Port " . $ethernetWANport . " with address " . $gwAddress);
  :set $WANInfo2 (" is connected to upstream address " . $upstreamAddress);
  :set $WANInfo3 (" with a link-rate of " . $thishostrate . ". ");
  :set $reportBody ($reportBody . $WANInfo1 . $WANInfo2 . $WANInfo3);
  :set $outVolume  [ $toUnits [ $getTraffic $ethernetWANport driver-tx-byte]]
  :set $inVolume   [ $toUnits [ $getTraffic $ethernetWANport driver-rx-byte]]
  :set $reportBody ($reportBody . "Traffic since " . $reportingTrafficVolumesSinceDate . ":");
  :set $reportBody ($reportBody . " out, " . $outVolume);
  :set $reportBody ($reportBody . "; in, " . $inVolume . ".\n");
  }
}

# LANs
:set $reportBody ($reportBody . "\n- - - - - - - - - - - - - Downstream links:");

# IPv4 subnets
:set reportBody ($reportBody . "\n\nLocal IPv4 subnets:\n")
:local line;
:local shortAddr;
:local thisIPAddr;
:local thisIface;
:local thisNetwork;
:local prefixLength;
:local addressPool;
:local thePoolRange;
:local PoolInfo;
:foreach line in=[/ip address find] do={
    :set $thisIface [/ip address get $line interface];
    :set $thisIPAddr [/ip address get $line address];
    :set $shortAddr [:pick $thisIPAddr 0 [:find $thisIPAddr "/"]];
    :set $prefixLength [:pick $thisIPAddr [:find $thisIPAddr "/"] 99];
    :set $thisNetwork [/ip address get $line network];
# get the DHCP address-pool and its range
        :if ([:len [/ip dhcp-server find where interface=$thisIface]] >0) do={
            :set $addressPool [/ip dhcp-server get [find interface=$thisIface] address-pool];
            :set $thePoolRange [/ip pool get [find name=$addressPool] ranges];
            :set $PoolInfo (", DHCP address pool, " . $thePoolRange)
        } else={
            :set $PoolInfo (", no DHCP server");
        }
    :set $reportBody ($reportBody . "on " . $thisIface . ": " . $thisNetwork . " ");
    :set $reportBody ($reportBody . $prefixLength . " (gateway, " . $shortAddr . $PoolInfo . ")\n");
}
# IPv6 subnets
:set reportBody ($reportBody . "\nLocal IPv6 subnets:\n")
:set reportBody ($reportBody . " interface               subnet\n")
:foreach line in=[/ipv6 pool used find] do={
 :set reportBody ($reportBody . [/ipv6 pool used get $line info] . " - " . [/ipv6 pool used get $line prefix] . "\n") 
}

# Hosts on bridged interfaces, listed by interface 18 June 22 DdeC
:set $reportBody ($reportBody . "\nInterfaces:");  
:foreach line in=[/interface ethernet find] do={                                                                            #from the table interface>ethernet...
   :set $ifName [/interface ethernet get $line name];                                                                       #get interface names
   :set $origIfName [/interface ethernet get $line default-name];                                                           #& get 'default' (basic, eg 'ether1') interface names
   :set $reportBody ($reportBody . "\n$origIfName - ");                                                                     #use default name for display here
   :if ([:len [/interface bridge host find where on-interface=$ifName !local]] = 0) do={                                    #hosts at this interface on bridges?--use nondefault name
      :set $reportBody ($reportBody . "not a bridged interface with an active connection");                                 #notify (port not bridged or no connection to bridged port)
      } else={                                                                                                              #if interface shows in a bridge..
      :set $prefix "";                                                                                                      #prepare for multiple matches
      :foreach MACline in=[/interface bridge host find where on-interface=$ifName !local] do={                              #two criteria for find-'local' & the interface
         :local currentMAConBridge [/interface bridge host get $MACline mac-address ];                                      #get MAC addr from a bridge entry
         :if ([:len [ip dhcp-server lease find where mac-address=$currentMAConBridge]] = 0) do={                            #if this MAC addr not in DHCP server leases get vendor&                                                                                                                                                                                                                
           :set $reportBody ($reportBody . $prefix . "cannot find a leased address for device with MAC address ". $currentMAConBridge);  
            } else={                                                                                                        #the MAC addr is in the list of leases...
            :if ([:len [ip dhcp-server lease get [find where mac-address=$currentMAConBridge] host-name ]] > 0 ) do={       #if the host is named... 
                  :local hostName [ip dhcp-server lease get [find where mac-address=$currentMAConBridge] host-name ];       #get the host name
                  :set $reportBody ($reportBody . $prefix . $hostName . " ");
                  } else={
                  :set $reportBody ($reportBody . $prefix . "un-named device (MAC adddress " . $currentMAConBridge . ") ");  #otherwise call it unknown, show the MAC, look for vendor
                  :local lookupResult [ $getVendor $currentMAConBridge ];                                                   #function call: [ ] brackets, function name, args $1...                                                        
                  :local MACAnnounce ("Device vendor " . $lookupResult . ".");
                  :set $reportBody ($reportBody . $MACAnnounce);                                                            #report the vendor info
                 }
            :if ([:len [ip dhcp-server lease get [find where mac-address=$currentMAConBridge] address ]] > 0 ) do={         #if there's an IP address in the lease table
               :set $hostAddr [ip dhcp-server lease get [find where mac-address=$currentMAConBridge] address ];             #report the IP address
               } else={ 
               :set $hostAddr "unknown IP address";                                                                         #otherwise report it's not known
            }
            :set $reportBody ($reportBody . "at address " . $hostAddr);                                                     #report the address
            :set $prefix "\n             also, ";                                                                           #indicate multiple hosts per port with 'also'
         }
      }
   }
}

# Dynamic Leases
:set $reportBody ($reportBody . "\n\nDHCP server leases - dynamic:\n");
:if ([:len [/ip dhcp-server lease find where dynamic=yes]] = 0) do={                                                        #look for dynamic leases in IPv4 DHCP server
  :set $reportBody ($reportBody . "None\n");
  } else={
  :foreach item in=[/ip dhcp-server lease find where dynamic=yes]  do={                                                     #after forum.mikrotik.com/viewtopic.php?t=97367
    :if ([:len [/ip  dhcp-server lease get $item host-name]] = 0) do={                              
        :set $reportBody ($reportBody . "Unknown device");
     } else={
        :set $reportBody ($reportBody . [/ip dhcp-server lease get $item host-name]);                                       #get & show the host name if possible for each lease
     }
    :set $reportBody ($reportBody . " at " .                              \
    [/ip dhcp-server lease get $item address] .  " (MAC address " .       \
    [/ip dhcp-server lease get $item mac-address] . ")");                                                                   #and show the MAC address                        
     :local thishostactivemacaddress [/ip dhcp-server lease get $item active-mac-address];                                  #get the 'active' MAC address
     :if ([:len [/interface bridge host find where mac-address=$thishostactivemacaddress]] > 0) do={       
         :local thishostport [/interface bridge host get [find where mac-address=$thishostactivemacaddress ] on-interface];
         :set $reportBody ($reportBody . " via port " . $thishostport . ".");                                               #and from bridge>hosts get corresponding port if poss.
         :local thishostrate ([/interface ethernet monitor $thishostport once as-value]->"rate");                           #get link rate (forum.mikrotik.com/viewtopic.php?t=94583)
         :set $reportBody ($reportBody . " Max. link-rate: " . $thishostrate . ".");                                        #note it's max, host may be bridged to lower rate downstream
         :local outVol [ $toUnits [ $getTraffic $thishostport driver-tx-byte]];
         :local inVol [ $toUnits [ $getTraffic $thishostport driver-rx-byte]];
         :set $reportBody ($reportBody . " Traffic since " . $reportingTrafficVolumesSinceDate . ":");
         :set $reportBody ($reportBody . " out, " . $outVol);
         :set $reportBody ($reportBody . "; in, " . $inVol . ".\n");
         } else={
        :set $reportBody ($reportBody . ". Currently not connected.\n");
     }
  }
}

# Static Leases
:set $reportBody ($reportBody . "\nDHCP server leases - static:\n");
:if ([:len [/ip dhcp-server lease find where dynamic=no]] = 0) do={
  :set $reportBody ($reportBody . "None\n\n");
  } else={
  :foreach item in=[/ip dhcp-server lease find where dynamic=no]  do={
    :if ([:len [/ip  dhcp-server lease get $item host-name]] = 0) do={
      :set $reportBody ($reportBody . "Unknown device");
      } else={
      :set $reportBody ($reportBody . [/ip dhcp-server lease get $item host-name]);
    }
    :set $reportBody ($reportBody . " at " .                               \
    [/ip dhcp-server lease get $item address] .  " (MAC address " .        \
    [/ip dhcp-server lease get $item mac-address] . ")");
     :local thishostactivemacaddress [/ip dhcp-server lease get $item active-mac-address]; 
     :if ([:len [/interface bridge host find where mac-address=$thishostactivemacaddress]] > 0) do={       
         :local thishostport [/interface bridge host get [find where mac-address=$thishostactivemacaddress ] on-interface];
         :set $reportBody ($reportBody . " via port " . $thishostport . "."); 
         :local thishostrate ([/interface ethernet monitor $thishostport once as-value]->"rate");  
         :set $reportBody ($reportBody . " Max. link-rate: " . $thishostrate . "."); 
         :local outVol [ $toUnits [ $getTraffic $thishostport driver-tx-byte]];
         :local inVol [ $toUnits [ $getTraffic $thishostport driver-rx-byte]];
         :set $reportBody ($reportBody . " Traffic since " . $reportingTrafficVolumesSinceDate . ":");
         :set $reportBody ($reportBody . " out, " . $outVol);
         :set $reportBody ($reportBody . "; in, " . $inVol . ".\n");
         } else={
        :set $reportBody ($reportBody . ". Currently not connected.\n");
     }
  }
}

# Reset counters on ethernet ports
/interface ethernet reset-counters [/interface ethernet find] 

# Scripts
:set $reportBody ($reportBody . "\n- - - - - - - - - - - - - Scripts in System>Scripts:\n\n");
:foreach scriptId in=[/system script find] do={
    :local scriptSource [/system script get $scriptId source];
    :local scriptSourceLength [:len $scriptSource];
    :set $scriptSourceLength [:tostr "$scriptSourceLength"];
    :local scriptName [/system script get $scriptId name];
    :set $reportBody ($reportBody . "'$scriptName' ($scriptSourceLength bytes)\n");
}

# Report version and number
:set $reportBody ($reportBody."\nThis is report version $reportVersion, number $runcount.\n");

:local emailSubject ("Weekly report for " . $deviceName . $updateFlag);
/tool e-mail send subject=$emailSubject to=$addressto body=$reportBody 
:delay 4

# Make log entry:
:log info "***Regular report $runcount sent***"