Monitorizare rețea prin intermediul Excel cu transmiterea alertei pe Gmail/Telegram

În acest articol o să vorbim despre cum putem monitoriza foarte simplu doar cu ajutorul unei funcții o rețea mică prin intermediul Microsoft Excel. Mai ales că întro rețea simplă nu orice router are SNMP. Deci în imaginea de mai jos vedeți cum arată interfața grafică a aplicației de monitorizare. Aplicatia funcționează în felul următor: monitorizează un nod după PING, înregistrează în timp real timpul de răspuns si înregistrează la fel și cînd a cazut ultima oară nodul respectiv cu posibilitatea de a trimite alerte în mod continuu pe gmail/telegram pînă cînd nodul nu este UP.

Mai jos va prezint codul VBA (Visual Basic for Aplication) ce trebuie integrat în Excel.

Function ping(strip)
Dim objshell, boolcode
Set objshell = CreateObject("Wscript.Shell")
boolcode = objshell.Run("ping -n 1 -w 1000 " & strip, 0, True)
If boolcode = 0 Then
    ping = True
Else
    ping = False
End If
End Function

Sub PingSystem()
Dim strip As String
Dim strTextBody As String
For introw = 3 To ActiveSheet.Cells(65536, 3).End(xlUp).Row
    strip = ActiveSheet.Cells(introw, 3).Value
    If ping(strip) = True Then
        ActiveSheet.Cells(introw, 4).Interior.ColorIndex = 0
        ActiveSheet.Cells(introw, 4).Font.Color = RGB(0, 0, 0)
        ActiveSheet.Cells(introw, 4).Value = "UP"
        ActiveSheet.Cells(introw, 5).Value = rsp_time(strip)
        Sheets("DATA").Cells(introw, 1).Value = 0
        ActiveSheet.Cells(introw, 4).Font.Color = RGB(0, 200, 0)
    Else
        ActiveSheet.Cells(introw, 4).Interior.ColorIndex = 0
        ActiveSheet.Cells(introw, 4).Font.Color = RGB(200, 0, 0)
        ActiveSheet.Cells(introw, 4).Value = "DOWN"
        ActiveSheet.Cells(introw, 6).Value = Now()
        Sheets("DATA").Cells(introw, 1).Value = 1
        strTextBody = ActiveSheet.Cells(introw, 2).Value & "" & ActiveSheet.Cells(introw, 3).Value & "" & ActiveSheet.Cells(introw, 6).Value
        SendGmail (strTextBody)
    End If
Next
Application.OnTime DateAdd("s", 2, Now), "PingSystem"
End Sub

Function rsp_time(target)
Set wmi = GetObject("winmgmts://./root/cimv2")

qry = "SELECT * FROM Win32_PingStatus WHERE address='" & target & "'"
For Each pingStatus In wmi.ExecQuery(qry)
    rsp_time = pingStatus.ResponseTime
Next
End Function

Function SendGmail(strTextBody)

    'creating a CDO object
    Dim Mail As CDO.Message
    Set Mail = New CDO.Message
    
    'Enable SSL Authentication
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    
    'Make SMTP authentication Enabled=true (1)
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    
    'Set the SMTP server and port Details
    'Get these details from the Settings Page of your Gmail Account
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
    "smtp.gmail.com"
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    
    'Set your credentials of your Gmail Account
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
    "emailul_vostru@gmail.com"
    Mail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
    "**********"
    
    'Update the configuration fields
    Mail.Configuration.Fields.Update
    
    'Set All Email Properties
    With Mail
        .Subject = "Alerta_PING"
        .From = "emailul_vostru@gmail.com"
        .To = "emailul_vostru@gmail.com"
        .TextBody = "Ping FAIL :" & strTextBody
        
    End With
    'to send the mail
    Mail.Send
    
End Function

Deasemenea fișierul excel trebuie sa contină două taburi, primul tab puteți să îl denumiți așa cum doriți voi dar al doilea trebuie să îl denumiți DATA, exact ca în imaginea de mai jos.

Deasemenea în setările Excel la „Macros Security” ce se gasește în meniul „Developer” trebuie să bifați „Enable all Macros”. Dacă meniul „Developer” la voi lipsește, nu vă speriați, mergeți în „Options” și din meniul „Customize Ribbon” bifați meniul „Developer”, astfel meniul va aparea în bara de sus a aplicației Excel.

Pentru a putea trimite notificări pe gmail va trebui să instalăm o referință (bibliotecă) în VBA (Visual Basic for Aplication). Această bibliotecă se numește „Microsoft CDO for Windows” și poate fi adaugată foarte ușor din meniul „Tools” >> „Preferences”, o bifați și continuați cu butonul „Ok” așa ca în imaginea de mai jos.

Un punct important e faptul că trebuie să aveți pornit în setările de securitate a gmail opțiunea „less secure app access”, dacă este OFF, setați această opțiune pe ON exact ca în imaginea de mai jos.

Acum revenim puțin cu mici explicații asupra codului ca să vedeți unde trebuie să faceți modificări în așa fel ca mesajele de alertă să sosească pe gmailul vostru exact ca în imaginea de mai jos.

Așadar, modificaînd parametrul marcat în imaginea de mai jos veți schimba timpul în care programul va verifica nodurile voastre după PING.

Dupa cum vedeți „40” indică că programul va executa ping o data la 40 de secunde, în codul plasat sus este setat ca programul sa execute ping o dată la 2 secunde, voi puteți seta 1 secundă, 2, 3 deja după dorința și necesitățile voastre.

Modificați și setați în cod emailul vostru așa ca în imaginile de mai jos. În casuța cu stelute setați parola casuței postale. La „From” și „To” la fel emailul vostru.

După ce ați setat toate cele indicate, felicitări, aveți un sistem simplu de monitorizare a rețelei după ping. Suplimentar vă voi prezenta și cîțiva pași în imagini după care puteți seta un gmail_bot pentru a vă retransmite mesajele de alertă și pe telegram. Acest bot va face redirecționarea mesajelor de alertă de pe gmail pe telegram. Pentru asta urmați toți pașii de mai jos prezentați în imagini.

Dupa cum vedeți în ultima imagine am primit pe telegram o Alerta_PING.

Pentru ca să vă ușurez munca mai jos vă pun la dispoziție și fișierul cu programul plus codul VBA în excel, vouă nu vă rămîne decît să vă setați doar adresa poștală pe care veți primi alarmele în cazul în care nodurile din rețea vor cădea. Prin intermediul linkului de mai jos puteți descarca programul.

https://serveradmin.md/fisier/ping-monitor-gmail.xlsm

NOTĂ: Puteți continua în partea de jos tabelul și puteți adăuga cîte noduri doriți, pe fiecare rînd cîte un nod.

Baftă!