VBA Excel Pobieranie danych z różnych plików

autor: http://www.hackwars.pl/vba-excel-pobieranie-danych-roznych-plikow/

ThisWorkbook.Path – Właściwość Path skoroszytu Excela określa pełną ścieżkę katalogu, w którym znajduje się dany plik – dzięki czemu możemy sobie odpuścić ścieżkę F:\Problem_solving, i dzięki czemu gdzie byśmy nie przenieśli katalogu głównego (Problem_solving) makro nadal będzie działać.
Workbooks.Open(Filename:=plik) – metoda odpowiedzialna za otwarcie konkretnego pliku
wb.Saved = True – zapisywanie otwartych plików – dzięki czemu przed zamknięciem nie będzie excel wyświetlał powiadomień z zapytaniem czy zapisać.
wb.Close – metoda Close – zamyka plik

 

KOD DLA POTOMNOŚCI 

Sub problem_solving()
Application.ShowWindowsInTaskbar = False ‚ wyłaczamy aktualizacje taskbara
Application.ScreenUpdating = False ‚ wylaczamy aktualizacje tego co sie dzieje na ekranie podczas pracy makra
Dim kom1 As Range ‚ zestawienie
Dim kom2 As Range ‚ filestocheck
Dim newkom1 As Range ‚ arkusz1 – dla plików z których pobieramy dane

For Each kom1 In Sheets(„zestawienie”).Range(„a4:a15”) ‚ zakres w arkuszu zestawienie w ktorym znajduja sie nazwy dzialow

‚tworzymy i zerujemy sobie zmienne dla kazdego miesiaca
styczen = 0
luty = 0
marzec = 0
kwiecien = 0
maj = 0
czerwiec = 0
lipiec = 0
sierpien = 0
wrzesien = 0
pazdziernik = 0
listopad = 0
grudzien = 0
starsze = 0
rest = 0

For Each kom2 In Sheets(„FilesToCheck”).Range(„A4:a15”) ‚ zakres w arkuszu FilesToCheck w ktorym znajduja się nazwy dzialow
brak_pliku = False
If (kom1.Value = kom2.Value) Then ‚jesli dzial z zestawienia bedzie sie rownal dzialowi z filestochec to wykonaj to:
lokalizacja = kom2.Offset(0, 1).Value
nazwa_pliku = kom2.Offset(0, 2).Value
kol = kom2.Offset(0, 3).Value ‚ kolumna która należy przeszukiwac w plikach do sprawdzenia
wier = kom2.Offset(0, 4).Value ‚ wiersz od ktorego należy rozpocząć przeszukiwania w plikach do sprawdzenia
plik = ThisWorkbook.Path & „\” & lokalizacja & „\” & nazwa_pliku ‚ sciezka do konkretnego pliku

If Dir(plik) <> „” Then ‚ sprawdzamy czy plik jest pusty / jesli nie jest to wchodzimy do środka
Set wb = Workbooks.Open(Filename:=plik) ‚ ustawiamy zmienna wb na dany skoroszyt

wb.Worksheets(„Arkusz1”).Range(kol & wier).End(xlDown).Select ‚ szukamy ostatniego wiersza do przeszukiwania
ostatni_wier = ActiveCell.Row + 1 ‚ ustawiamy ostatni wolny wiersz

For Each newkom1 In wb.Worksheets(„Arkusz1”).Range(kol & wier & „:” & kol & ostatni_wier) ‚ sprawdzamy zakres danych w plikach z których chcemy zebrać dane

If (newkom1.Value = „S”) Then
rok = Year(newkom1.Offset(0, -2).Value)
miesiac = Month(newkom1.Offset(0, -2).Value)

If (rok < Year(Now)) Then

starsze = starsze + 1

Else ‚(rok < Year(Now))

Select Case miesiac ‚ instrukcja warunkowa case dzięki której mozemy podliczyć otwarte projekty w zależności od miesiąca
Case 1: styczen = styczen + 1
Case 2: luty = luty + 1
Case 3: marzec = marzec + 1
Case 4: kwiecien = kwiecien + 1
Case 5: maj = maj + 1
Case 6: czerwiec = czerwiec + 1
Case 7: lipiec = lipiec + 1
Case 8: sierpien = sierpien + 1
Case 9: wrzesien = wrzesien + 1
Case 10: pazdziernik = pazdziernik + 1
Case 11: listopad = listopad + 1
Case 12: grudzien = grudzien + 1
Case Else: rest = rest + 1
End Select

End If ‚ else (rok < Year(Now))

End If ‚(newkom1.Value = „S”)

Next ‚ end newkom1

wb.Saved = True ‚ zapisujemy plik
wb.Close ‚ zamykamy

Else
brak_pliku = True
Exit For

End If ‚ Dir(plik) <> „”

End If ‚(kom1.Value = kom2.Value)

Next
‚ zapisujemy dane w arkuszu zestawienie w odpowiednich komórkach (offset – przypisuje do odpowiedniego miesiaca – jest to przesunięcie względem komórki która przeszukujemy)
kom1.Offset(0, 1).Value = styczen
kom1.Offset(0, 2).Value = luty
kom1.Offset(0, 3).Value = marzec
kom1.Offset(0, 4).Value = kwiecien
kom1.Offset(0, 5).Value = maj
kom1.Offset(0, 6).Value = czerwiec
kom1.Offset(0, 7).Value = lipiec
kom1.Offset(0, 8).Value = sierpien
kom1.Offset(0, 9).Value = wrzesien
kom1.Offset(0, 10).Value = pazdziernik
kom1.Offset(0, 11).Value = listopad
kom1.Offset(0, 12).Value = grudzien
kom1.Offset(0, 13).Value = starsze
kom1.Offset(0, 15).Value = rest

kom1.Offset(0, 14).Value = brak_pliku
Next

Application.ScreenUpdating = True
Application.ShowWindowsInTaskbar = ShwWndsTask

End Sub

VBA Opisy post poleceń

autor http://www.hackwars.pl/vba-excel-automatyczne-tworzenie-arkuszy-o-okreslonej-nazwie/

 

Worksheets.Count – zwraca liczbę arkuszy w skoroszycie
Sheets.Add(After:=Sheets(Worksheets.Count)) – dodaje nowy arkusz po bieżącym
WS.Name = MonthName(i, True) – zmienia nazwę ustawionego skoroszytu na skrót nazwy kolejnego miesiąca (gdybyśmy chcieli użyć nazwy całego miesiąca zmieniamy wartość True na False)

 

Opis

tym wpisie przedstawiam jak automatycznie utworzyć 12 arkuszy, których nazwa jest skrótem każdego miesiąca.

Na początku utwórzmy nowy plik Excela z obsługą makr.
Jeśli w pliku znajduje się więcej niż jeden arkusz należy usunąć nadwyżkę:)

 

Public Sub DodajArkusze()
‚deklarowanie zmiennych
Dim i As Integer
Dim WS As Worksheet

i = 1

‚rozpoczęcie pętli
For i = 1 To 12

If i > Worksheets.Count Then
‚jeśli i jest większa od liczby arkuszy w skoroszycie (pliku) to
‚ustawia zmienną WS na nowo dodany arkusz
Set WS = Sheets.Add(After:=Sheets(Worksheets.Count))

Else
‚ustawia zmienna WS na pierwszy arkusz z pętli
‚tu pętla zagląda tylko za pierwszym razem
Set WS = Sheets(Worksheets.Count)

End If
‚zmienia nazwę aktualnie ustawionego arkusza na skrót nazwy miesiąca
WS.Name = MonthName(i, True)

‚zwiększa wartość zmiennej i o 1
Next i
End Sub

Przenoszenie domyślnych katalogów w SQL Server (!!!nie testowane !!!)

Originale TUTAJ Moze  się kiedyś przyda!!!

Instalacja SQL Server do domyślnych katalogach mogą być trudne do maintan przede wszystkim dlatego, Microsoft zmienia domyślną ścieżkę do katalogu danych w każdej wersji głównej. Na przykład domyślne ścieżki do plików bazy danych dla instancji domyślnej w SQL 2005-2008 R2 są:

SQL 2005 -% Program Files% \ Microsoft SQL Server \ MSSQL.1 \ MSSQL \ Data
SQL 2008 -% Program Files% \ Microsoft SQL Server \ MSSQL10.MSSQLSERVER \ MSSQL \ Data
SQL 2008 R2 -% Program Files% \ DATA Microsoft SQL Server \ MSSQL10_50.MSSQLSERVER \ MSSQL \

Wykonywanie przywracania bazy danych z jednego serwera na inny z różnymi wersjami może dodać złożoności z poleceniem restore ponieważ ścieżka docelowa nie jest spójna. Również po aktualizacji w miejscu SQL Server pliki bazy danych nie zostały przeniesione z poprzedniej domyślnej ścieżce do nowego więc serwer może mieć MDF i LDF pliki w katalogu z łudząco nazwie ścieżki.

Korzystanie z katalogu Standardowy
Mam standaryzowane moje instalacje użyć tej samej ścieżki dla wszystkich lokalizacjach MDF i LDF plików. Upraszcza to odbudowała skryptów i zmniejsza złożoność podczas migracji, ponieważ wszystkie pliki danych na wszystkich serwerach są w tej samej ścieżce. I zawsze moje pliki dziennika na osobnym dysku z plików danych są więc w tych samych ścieżek na różnych dyskach, na przykład:

Logs – y:\mssql\%instanceName%\data
Data – z:\mssql\%instanceName%\data

 

Po zainstalowaniu i wszystkich plikach mdf i ldf znajdują się w nowych, ulepszonych lokalizacjach standardowych, znalazłem trzy dodatkowe katalogi, które chciałem ujednolicić i przejść do domyślnej ścieżki:

database engine default backup directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\BACKUP
sql agent working directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS
sql agent log directory
%SQL Install Directory%\MSSQL10_50.MSSQLSERVER\MSSQL\LOG

Konfiguracja nie jest dostępna za pośrednictwem interfejsu GUI SSMS lub podczas instalacji. Jedynym sposobem ich zmiany są bezpośrednio w rejestrze lub za pomocą skryptu t-sql, który zmienia rejestr.
Oto skrypt, który zmieni je na dowolną ścieżkę wybranej ścieżki i ma świadomość, że poprawny klucz rejestru jest aktualizowany dla dowolnej instancji. Uwaga – ten skrypt używa procedury rozszerzonej xp_instance _regwrite,

w przyszłości może nie działać .

 

USE [msdb]
GO
 
-- change the @instancename to whatever named instance this is for, the rest will sort itself out ----------------------
DECLARE @instancename nvarchar(255) = N'mssqlserver' -- use 'mssqlserver' for the default instance
 
DECLARE @mssqlpath nvarchar(255) = N'z:\mssql\' + @instancename
DECLARE @dbengine_defaultbackup_path nvarchar(255) = @mssqlpath + N'\backup'
DECLARE @sqlagent_errorlogfile nvarchar(255) = @mssqlpath + N'\log\SQLAGENT.OUT'
DECLARE @sqlagent_workingdirectory_path nvarchar(255) = @mssqlpath + N'\jobs'
 
--SELECT @dbengine_defaultbackup_path, @sqlagent_workingdirectory_path, @sqlagent_errorlogfile
 
-- update the database engine's default backup directory path ---------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'
 , N'BackupDirectory'
 , N'REG_SZ'
 , @dbengine_defaultbackup_path
 
-- update the SQL Agent working directory -----------------------------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
 , N'WorkingDirectory'
 , N'REG_SZ'
 , @sqlagent_workingdirectory_path
 
-- update the SQL Agent log file location -----------------------------------------------------------------------------
EXECUTE msdb.dbo.sp_set_sqlagent_properties @errorlog_file = @sqlagent_errorlogfile
 
-- update the setup's SQL Data Root path ------------------------------------------------------------------------------
EXECUTE [master].dbo.xp_instance_regwrite 
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
 , N'SQLDataRoot'
 , N'REG_SZ'
 , @mssqlpath

Zmodyfikuj skrypt, aby używać dowolnej ścieżki domyślnej. Usługi SQL Server i SQL Agent będą musiały zostać ponownie uruchomione, aby rozpocząć korzystanie z nowych lokalizacji plików. Gdy nowe lokalizacje są w użyciu, możesz usunąć stare katalogi.

Aktualizacja

Po próbie uaktualnienia jednego z moich serwerów do programu SQL Server 2012 stwierdziłem, że instalator miałby błąd podczas instalowania silnika bazy danych. Bałagan był:

“The Database Engine system data directory in the registry is not valid.”

Znalazłem klucz HKLM\software\microsoft\microsoft sql server\%instance_name%\setup\SQLDataRoot  .Odszukuje  ścieżkę dostępu do baz danych systemu. Ten klucz został utworzony podczas instalacji 2008 R2 i pliki zostały następnie przeniesione, więc to, co zostało utworzone w czasie instalacji, nie jest poprawne.

Jeśli zmodyfikujesz ten klucz na ścieżce skonfigurowanej w pliku @mssqlpath ze skryptu powyżej instalatora 2012, działa prawidłowo. Zmieniłem również skrypt, ostatnia sekcja wprowadza odpowiednią zmianę w celu uniknięcia tego wyjątku w przyszłości.

PDF oryginału

 

Mikrotik-Bandwidth-Test-Server(s) Public

Dzięki planetcoop, teraz mamy dwa serwery publiczne BTEST MikroTik możemy przetestować, łącze

oba serwery działają BTEST MikroTik Chr.

oba są na połączeniach internetowych 10-Gig. To jednak nie oznacza, jesteś w stanie przetestować pełną 10-gig.

oto informacje:
 

planetcoop btest server:
Host: btest.planetcoop.com
ipv4: 50.235.23.218
ipv6: 2001:559:8062::20
user and pass: btest
traffic graph of this btest server: https://btest.planetcoop.com:10443/graphs/iface/ether1/

 

TomjNorthIdaho btest server:
ipv4: 207.32.195.2
user and pass: btest
traffic graph of this btest server: http://207.32.195.2/graphs/iface/ether1/

Mikrotik 2 wan skrypt

autorBartłomiej Dabiński

Konfiguracja

Spróbujemy teraz ustawić MikroTika do pracy z dwoma łączami w wariancie failover. Zbudujemy do tego celu topologię sieciową jak na rysunku:

https://sekurak.pl/wp-content/uploads/2015/07/mikrotik3.png

 

Dysponujemy dwoma łączami: do ISP1 (dostęp główny) i do ISP2 (dostęp zapasowy). Łącze główne działa na adresie IP otrzymanym z serwera DHCP, łącze zapasowe zrealizowane jest w technice PPPoE. Są to dwie najczęściej stosowane przez operatorów techniki. Nasz przykład jest adekwatny także do dość typowej sytuacji, gdy jako łącza zapasowego używamy połączenia PPP zestawionego za pośrednictwem modemu 3G/4G podłączonego bezpośrednio do płyty RouterBoard. Tutaj są dostępne listy wspieranych modemów 3G i 4G.

Do routera podłączony jest komputer użytkownika przy użyciu adresacji statycznej. Adres IP użytkownika to 10.0.0.2/24, jego brama to 10.0.0.1 (MikroTik).

Zacznijmy od skonfigurowania klientów DHCP oraz PPPoE oraz zaadresowania interfejsu dla sieci lokalnej.

Dodanie klienta DHCP:

[admin@MikroTik] > ip dhcp-client add interface=ether1 disabled=no

Zweryfikujemy jego działanie:

[admin@MikroTik] > ip dhcp-client print
Flags: X - disabled, I - invalid
#&nbsp;&nbsp; INTERFACE &nbsp;&nbsp;&nbsp;USE-PEER-DNS ADD-DEFAULT-ROUTE STATUS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ADDRESS
0&nbsp;&nbsp; ether1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; yes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; yes&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; bound&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 192.168.39.102/24

 

Jak widać, klient DHCP działa poprawnie i otrzymaliśmy adres 192.168.39.102/24.

Dodanie klienta PPPoE (login: user, hasło: password):

[admin@MikroTik] > interface pppoe-client add interface=ether2 user=user password=password disabled=no

Weryfikacja

[admin@MikroTik] > interface pppoe-client print
Flags: X - disabled, R - running
0 R name="pppoe-out1" max-mtu=1480 max-mru=1480 mrru=disabled interface=ether2 user="user" password="password" profile=default keepalive-timeout=60 service-name="" ac-name="" add-default-route=no dial-on-demand=no use-peer-dns=no allow=pap,chap,mschap1,mschap2[admin@MikroTik] > interface pppoe-client monitor 0
status: connected
uptime: 44s
active-links: 1
encoding:
service-name: service1
ac-name: host
ac-mac: 02:DF:C4:6B:9E:94
mtu: 1480
mru: 1480
local-address: 172.16.0.2
remote-address: 172.16.0.1

Klient PPPoE również działa poprawnie, otrzymaliśmy adres 172.16.0.2.

Ustawienie adresu statycznego dla ether3:

[admin@MikroTik] > ip address add address=10.0.0.1/24 interface=ether3

Weryfikacja wpisów IP

[admin@MikroTik] > ip address print
Flags: X - disabled, I - invalid, D - dynamic
#   ADDRESS           NETWORK         INTERFACE
0 D 192.168.39.102/24 192.168.39.0   ether1
1 D 172.16.0.2/32     172.16.0.1     pppoe-out1
2   10.0.0.1/24       10.0.0.0       ether3

Aby w sieci lokalnej był dostęp do Internetu, potrzebujemy jeszcze skonfigurować NAT dla obu łącz:

[admin@MikroTik] > ip firewall nat add chain=srcnat out-interface=ether1 action=masquerade
[admin@MikroTik] > ip firewall nat add chain=srcnat out-interface=pppoe-out1 action=masquerade

Na komputerze ustawiamy statycznie adresację 10.0.0.2/24 i bramę 10.0.0.1. Sprawdźmy połączenie z Internetem, pingując do publicznego serwera DNS Googla:

1. Sposób prosty

W tej chwili mamy dostęp do Internetu przez łącze ISP1, ponieważ MikroTik domyślnie dodaje domyślną trasę routingu dla klienta DHCP, a nie dodaje dla klienta PPPoE (zachowanie to dla obu klientów można zmienić poprzez atrybut add-default-route).

Potrzebujemy włączyć sprawdzanie dostępności bramy domyślnej od ISP1. Mamy tu dwie opcje do wyboru: ping lub ARP. Jeśli brama operatora odpowiada na zapytania ICMP, to zalecana jest opcja ping, ponieważ zapytania ping są przesyłane unicastowo. Przy opcji ARP MikroTik będzie wysyłał pod adres broadcastowy pakiet ARP request.

Ponieważ domyślnie dla dodanej trasy nie ma zaznaczonej opcji check-gateway, a tras dodanych dynamicznie nie możemy edytować bezpośrednio, to skorzystamy z funkcji filtrowania tras routingu:

[admin@MikroTik] > routing filter add action=passthrough chain=dynamic-in disabled=no set-check-gateway=ping

Reguła ta będzie ustawiać opcję check-gateway także dla innych dynamicznych tras w tablicy routingu (z wyjątkiem tras bezpośrednio podłączonych i dodanych przez protokoły routingu). W niektórych sytuacjach takie rozwiązanie może nam nie odpowiadać, więc ograniczmy zakres reguły filtrującej trasy routingu:

[admin@MikroTik] > ip dhcp-client set 0 default-route-distance=254

[admin@MikroTik] > routing filter set 0 distance=254 set-distance=1

Zmieniamy dystans administracyjny (na 254) dla trasy tworzonej przez klienta DHCP, aby użyć tego parametru jako wyróżnika. Dla wcześniej utworzonej reguły filtrowania trasy routingu dodajemy warunek, aby przechwytywał tylko trasy z dystansem 254 i zmieniał go na pierwotną wartość, czyli 1.

Jeżeli główne łącze jest zestawione przy użyciu techniki PPPoE lub adresacji statycznej, to powyższe kombinacje nie są potrzebne. Wystarczy wtedy dodać statyczną trasę domyślną, ustawiając jej opcję check-gateway w analogiczny sposób, w jaki teraz dodamy trasę zapasową przez łącze PPPoE:

[admin@MikroTik] > ip route add gateway=pppoe-out1 distance=2

Dystans administracyjny (distance) dla trasy zapasowej ustawiliśmy na 2, czyli wyższy niż dla trasy głównej (1). Jeśli router ma w tablicy routingu dwie trasy do tej samej sieci docelowej (np. dwie trasy domyślne), to aktywna będzie trasa z niższym dystansem administracyjnym. Trasa zapasowa (z wyższym dystansem) stanie się aktywna dopiero wtedy, gdy trasa główna stanie się niedostępna.

W tej chwili mamy już działający, prosty mechanizm typu failover. Jego główna wada polega na tym, że odpytywana jest tylko adres IP bramy, czego konsekwencje zostały wcześniej opisane.

Kolejna wada, to że nie mamy wpływu na czułość/szybkość wykrycia usterki. Przy opcji check-gateway MikroTik wysyła zapytanie co 10 sekund, a po dwóch kolejnych brakach odpowiedzi określi trasę jako nieosiągalną. Do przywrócenia aktywnego stanu trasy wystarczy jedna odpowiedź, zatem mechanizm będzie podatny na flapowanie łącza.

Dalej, nie mamy możliwości ustawić maksymalnego dopuszczalnego opóźnienia.

Pewną wadą jest też to, że MT zmienia łącza w sposób cichy – bez żadnego powiadomienia ani śladu w historii. Możemy włączyć debugowanie:

[admin@MikroTik] > system logging add topics=route,debug,!calc

Niestety, zapisywane logi są mało precyzyjne i mało czytelne.

2. Sposób zaawansowany

Wykorzystując możliwości, jakie daje użycie skryptów na MikroTiku, możemy zbudować znacznie lepszy mechanizm, który skutecznie adresuje wyżej wspomniane braki. Poniższe podejście ma ograniczenie dotyczące sytuacji, gdy jedno z łącz (tak jak w naszym przykładzie) opiera się na DHCP. Potrzebujemy dodać trasy domyślne dla łącz do kilku tablic routingu, a MT nie ma funkcji, która by to pozwoliła zrobić w przypadku klienta DHCP (i nie pomogą też reguły filtrowania tras routingu). Przyjmiemy w tym momencie założenie, że brama domyślna, jaką otrzymujemy od operatora przez DHCP, jest zawsze taka sama (często tak jest) i możemy ją ustawić wpisem statycznym. (Jeśli takiego założenia nie możemy poczynić, to problem da się rozwiązać prostym skryptem, który będzie aktualizował trasy statyczne, jeśli nowa trasa otrzymana z DHCP będzie inna.)

Zasada mechanizmu jest taka, że monitorowana będzie grupa adresów IP należących do hostów publicznie dostępnych w Internecie (w konfiguracji skryptu uzupełniamy listę adresów). Aby łącze zostało zakwalifikowane jako uszkodzone, to dla każdego z adresów z listy muszą wystąpić minimalne określone straty pakietów. Domyślnie dla każdego adresu musi zginąć min. 2/10 pakietów. Dzięki takiemu zachowaniu mechanizm jest odporny na sytuację, gdy jeden z monitorowanych hostów przestanie udzielać (z jakiejkolwiek przyczyny) odpowiedzi na zapytania ICMP. Dopóki przynajmniej jeden host z listy będzie działał prawidłowo, nasz mechanizm nie będzie generował fałszywych przełączeń na łącze zapasowe.

Przejście na łącze główne nastąpi dopiero wtedy, jeśli otrzymamy określoną liczbę odpowiedzi (domyślnie 10/10) od przynajmniej jednego z hostów. Jest to warunek zapobiegający częstym przełączeniom w przypadku dużych strat pakietów na łączu. Tutaj także wystarczy nam jeden działający monitorowany host.

MikroTik nie pozwala na bezpośrednie określenie czasu timeout dla polecenia ping, natomiast maksymalne opóźnienie ustawiamy, wykorzystując parametr interwału pingowania. Domyślnie w skrypcie jest on ustawiony na 300 ms. Jeśli MikroTik w momencie wysyłania kolejnego zapytania ICMP nie ma odpowiedzi na poprzednie zapytanie, to jest to traktowane jako strata.

Mechanizm jest uogólniony na dowolną liczbę łącz. Każde z łącz jest niezależnie testowane i wyłączane w przypadku wykrycia awarii, co jest dobrym punktem wyjścia do wdrożenia load balancingu. W naszym przypadku (czysty failover) monitorowanie łącza zapasowego może wydawać się zbędne, jednak warto je prowadzić, aby uniknąć sytuacji, gdy o tym, że łącza zapasowe (od dawna) nie działa, dowiadujemy się dopiero w momencie awarii głównego.

Parametry mechanizmu są konfigurowalne, więc możemy go dostosować do naszych wymagań, ustalając właściwy kompromis np. pomiędzy szybkością wykrycia usterki a ryzykiem narażenia się na problemy w przypadku flapowania głównego łącza.

W przypadku przełączeń, skrypt zapisuje odpowiednią informację w logach, co pozwala na szybkie wykrycie usterki przez administratora, a także prześledzenie historii.

Pomimo że poniższy skrypt nie wykorzystuje żadnych zaawansowanych funkcji, zalecane jest staranne przetestowanie jego działania w przypadku innej wersji systemu RouterOS niż v6.27 oraz każdorazowo po aktualizacji systemu.

MikroTik w swej historii wielokrotnie dokonywał zmian w składni (czasem bardzo drobnych), zwykle nie wspominając o tym w changelogu. W takiej okoliczności skrypt się nie wykona (ze względu na błąd składni). W razie wystąpienia błędu, skrypt powinien zapisać w logach „wan monitoring: blad wykonania skryptu”

Wróćmy teraz do stanu podstawowej konfiguracji routera, tzn. sprzed części „Sposób prosty”. Ponieważ wszystkie trasy będziemy dodawać statycznie, to wyłączymy dodawanie trasy domyślnej przez klienta DHCP. Zanim jednak to zrobimy, sprawdźmy, jaka jest ta brama, aby wiedzieć, co potem dodać:

[admin@MikroTik] > ip route print
Flags: X - disabled, A - active, D - dynamic,
C - connect, S - static, r - rip, b - bgp, o - ospf, m - mme,
B - blackhole, U - unreachable, P - prohibit
#     DST-ADDRESS       PREF-SRC       GATEWAY           DISTANCE
0 ADS 0.0.0.0/0                          192.168.39.1             1
1 ADC 10.0.0.0/24       10.0.0.1       ether3                   0
2 ADC 172.16.0.1/32     172.16.0.2     pppoe-out1               0
3 ADC 192.168.39.0/24   192.168.39.102 ether1                  0

Jak widzimy, brama domyślna na łączu głównym to 192.168.39.1. Wyłączmy teraz jej automatyczne dodawanie przez klienta DHCP:

[admin@MikroTik] > ip dhcp-client set 0 add-default-route=no

Dodajemy teraz trasy statyczne. Oprócz domyślnej tablicy routingu, używamy osobnych tablic dla każdego z łącz, które będą służyć do testowania danego łącza. W tablicy głównej do tras dodajemy komentarze. Jest ważne, aby komentarze były dokładnie w formacie „wan monitoring: laczeX” (gdzie X to nr łącza odpowiadający numerowi na liście lacza_interfejsy w skrypcie), ponieważ komentarz ten jest wykorzystywany przez skrypt do wykonywania operacji na trasie.

[admin@MikroTik] > ip route add gateway=192.168.39.1 comment="wan_monitoring: lacze1"
[admin@MikroTik] > ip route add gateway=pppoe-out1 distance=2 comment="wan_monitoring: lacze2"
[admin@MikroTik] > ip route add gateway=192.168.39.1 routing-mark=lacze1
[admin@MikroTik] > ip route add type=blackhole routing-mark=lacze1 distance=2
[admin@MikroTik] > ip route add gateway= pppoe-out1 routing-mark=lacze2
[admin@MikroTik] > ip route add type=blackhole routing-mark=lacze2 distance=2

W tablicach do testowania poszczególnych łącz dodane są też wpisy typu blackhole ze słabszym dystansem. Dzięki tym trasom, w razie niedostępności bramy domyślnej dla wybranego łącza, ruch nie będzie przetwarzany przez główną tablicę routingu (i nie zostanie przekierowany na inne łącze).

Skrypt należy dodać do harmonogramu (System > Scheduler), zalecany interwał powinien być większy o min. 10 s.[2] od iloczynu maksymalnego tolerowanego opóźnienia (ping_interval), liczby łącz (lacza_interfejsy), liczby pingów do sprawdzania danego hosta (ile_pingow) i liczby hostów do monitorowania (adresy_monit). W naszym przykładzie iloczyn będzie miał wartość 300ms*2*10*4 = 24s, zatem zalecana wartość interwału dla harmonogramu to ok. 35 sekund.

:do {
   # ---------------------------- tu ustaw parametry ----------------------------
   # interfejsy na ktorych dzialaja lacza: {"lacze1";"lacze2"; ...}
   :local "lacza_interfejsy" {"ether1";"pppoe-out1"}
   # lista adresow ktore beda pingowane do weryfikacji kazdego z lacz
   :local "adresy_monit" {"176.31.167.200";"213.180.141.140";"194.204.159.1";"85.232.232.65"}
   # maksymalne tolerowane opoznienie
   :local "ping_interval" "300ms"
   # liczba wysylanych pingow w celu sprawdzenia danego adresu
   :local "ile_pingow" 10
   # co najmniej ile odpowiedzi musi wrocic aby uznac ze adres nadal dziala
   # jesli wroci mniej to nastepuje zmiana stanu up > down
   # wartosc nie moze byc wieksza niz ile_pingow
   :local "ile_pingow_up" 7
   # co najmniej ile pingow musi wrocic aby uznac ze adres zmienil stan down > up
   # wartosc powinna byc wieksza niz ile_pingow_updown
   # wartosc nie moze byc wieksza niz ile_pingow
   :local "ile_pingow_downup" 10
   # --------------------------------------------------------------------------
   :global "wan_status"
   :global aktywne
   :local "ile_lacz" [:len $"lacza_interfejsy"]
   :local "ile_adresow" [:len $"adresy_monit"]
   :local zmiany
   :local "log_tresc" ""
   :if ([:typeof $"wan_status"] = "nothing") do={
       :log debug "inicjalizacja"
       :set aktywne 1
       :for i from=1 to=$"ile_lacz" do={
           :set "wan_status" ($"wan_status","up")
           /ip route disable [find comment=("wan_monitoring: lacze" . $i)]
           :if ([/ip route get [find comment=("wan_monitoring: lacze" . $i)] distance] < [/ip route get [find comment=("wan_monitoring: lacze" . $aktywne)] distance]) do={
               :set aktywne $i
           }
       }
       /ip route enable [find comment=("wan_monitoring: lacze" . $aktywne)]
   }
   :for i from=1 to=$"ile_lacz" do={
       :set zmiany ($zmiany,"bez_zmian")
       :local "tab_rut_test" ("lacze" . $i)
       :local "ile_dziala" 0
       :if (($"wan_status" -> ($i - 1)) = "down") do={
           :for j from=1 to=$"ile_adresow" do={
               :if ([/ping ($"adresy_monit" -> ($j - 1)) interval=$"ping_interval" routing-table=$"tab_rut_test" count=$"ile_pingow"] >= $"ile_pingow_downup") do={
                   :set "ile_dziala" ($"ile_dziala" + 1)
               }
           }
           :if ($"ile_dziala" > 0) do={
               :set ($zmiany -> ($i - 1)) "downup"
           }
       } else={
           :for j from=1 to=$"ile_adresow" do={
               :if ([/ping ($"adresy_monit" -> ($j - 1)) interval=$"ping_interval" routing-table=$"tab_rut_test" count=$"ile_pingow"] >= $"ile_pingow_up") do={
                   :set "ile_dziala" ($"ile_dziala" + 1)
               }
           }
           :if ($"ile_dziala" = 0) do={
               :set ($zmiany -> ($i - 1)) "updown"
           }
       }
   }
   :for i from=1 to=$"ile_lacz" do={
       :if (($zmiany -> ($i - 1)) = "updown") do={
           :set ($"wan_status" -> ($i - 1)) "down"
           :if ($"log_tresc" != "") do={
               :set $"log_tresc" ($"log_tresc" . ", ")
           }
           :set $"log_tresc" ($"log_tresc" . "lacze" . $i . " down")
           :if ($aktywne = $i) do={
               /ip route disable [find comment=("wan_monitoring: lacze" . $i)]
               # czyszczenie wpisow connection tracking
               :if ([/interface get ($"lacza_interfejsy" -> ($i - 1)) disabled] = no) do={
                   /interface disable ($"lacza_interfejsy" -> ($i - 1))
                   /interface enable ($"lacza_interfejsy" -> ($i - 1))
               }
               # czyszczenie wpisow connection tracking - koniec
               :local distance 255
               :for j from=1 to=$"ile_lacz" do={
                   :if (($"wan_status" -> ($j - 1)) = "up" && [/ip route get [find comment=("wan_monitoring: lacze" . $j)] distance] < distance) do={
                       :set aktywne $j
                       :set distance [/ip route get [find comment=("wan_monitoring: lacze" . $j)] distance]
                   }
               }
              :if ($distance < 255) do={
                   /ip route enable [find comment=("wan_monitoring: lacze" . $aktywne)]
               } else={
                       :set aktywne 0
               }
           }
       }
       :if (($zmiany -> ($i - 1)) = "downup") do={
           :set ($"wan_status" -> ($i - 1)) "up"
           :if ($"log_tresc" != "") do={
               :set $"log_tresc" ($"log_tresc" . ", ")
           }
           :set $"log_tresc" ($"log_tresc" . "lacze" . $i . " up")
         :if ($aktywne = 0) do={
               :set aktywne $i
               /ip route enable [find comment=("wan_monitoring: lacze" . $i)]
           } else={
               :if ([/ip route get [find comment=("wan_monitoring: lacze" . $i)] distance] < [/ip route get [find comment=("wan_monitoring: lacze" . $aktywne)] distance]) do={
                   /ip route disable [find comment=("wan_monitoring: lacze" . $aktywne)]
                   # czyszczenie wpisow connection tracking
                   :if ([/interface get ($"lacza_interfejsy" -> ($aktywne - 1)) disabled] = no) do={
                       /interface disable ($"lacza_interfejsy" -> ($aktywne - 1))
                       /interface enable ($"lacza_interfejsy" -> ($aktywne - 1))
                   }
                   # czyszczenie wpisow connection tracking - koniec
                   :set aktywne $i
                   /ip route enable [find comment=("wan_monitoring: lacze" . $i)]
               }
           }
       }
   }
   if ($"log_tresc" != "") do={
       :log error ("wan_monitoring: " . $"log_tresc")
   }
} on-error={
   :log error "wan_monitoring: blad wykonania skryptu"
}

3. Problem z tablicą Connection Tracking

Na koniec zwróćmy uwagę na fragmenty skryptu następujący po komentarzu „czyszczenie wpisow connection tracking”. O co tutaj chodzi? Historia zaczyna się od tego, że aby udostępnić Internet w sieci lokalnej (z adresacją prywatną), to musimy wykonać NAT wielu adresów lokalnych na jeden adres zewnętrzny przydzielony przez operatora. Ponieważ taki NAT nadpisuje adres źródłowy pakietu, to pakiet nie zawiera dalej już informacji jednoznacznie identyfikującej rzeczywistego nadawcy w naszej sieci. Zatem, aby wiedzieć do kogo skierować dany pakiet przychodzący z Internetu, router z NAT-em utrzymuje tablicę połączeń. W momencie gdy z sieci lokalnej nawiązywane jest nowe połączenie[3], to jest ono rejestrowane w tablicy razem z parametrami opisującymi adres źródłowy (oryginalny i po translacji) i docelowy, protokół i numery portów. Dzięki tym informacjom router przekazuje pakiety przychodzące z Internetu do właściwych hostów, wykonując odwrotną translację.

Sedno problemu tkwi w tym, że podczas zmiany domyślnych tras routingu wpisy w tabeli śledzenia połączeń pozostają bez zmian. Gdy klient wysyła kolejne pakiety będące częścią raz zarejestrowanego połączenia, to router na potrzeby NAT-u korzysta z wcześniej dodanego wpisu tablicy połączeń, nie zwracając uwagi, że przypisany do połączenia adres zewnętrzny uległ zmianie. Zatem router, pomimo że, wysyłając pakiety już na nowe (zapasowe) łącze, będzie wykonywał translację na adres zewnętrzny przydzielony do łącza głównego. Taka komunikacja oczywiście się nie powiedzie.

Dla większości komunikacji problem będzie znikomy, bo nawiązanie nowego połączenia TCP spowoduje dodanie nowego wpisu (z właściwym adresem zewnętrznym) do tablicy. Problem będzie istotny dla protokołów opartych np. o UDP (np. SIP), bo każdy nowy pakiet wysłany przez klienta będzie odświeżał błędny wpis w tablicy śledzenia połączeń. Wtedy czas bezczynności nigdy nie wygaśnie i usługa będzie niedostępna dopóki klient nie wstrzyma się na dłuższy czas od transmisji lub do momentu, gdy zdesperowany administrator zrestartuje router, czyszcząc przy tym tablicę śledzenia połączeń. Problem ten dotyczy także „sposobu prostego”, opisanego wyżej.

Optymalnym rozwiązaniem byłoby przefiltrowanie tablicy połączeń w momencie zmiany łącza i skasowanie nieaktualnych wpisów. Niestety – pomimo że system RouterOS teoretycznie daje taką możliwość, to operacja często kończy się błędem action failed (6). Zgłoszenia błędu na oficjalnym forum nie spotkały ze się specjalnym zainteresowaniem producenta i problem pozostaje nierozwiązany.

W tej sytuacji, skutecznym i prostym (aczkolwiek niezbyt eleganckim) obejściem tego problemu jest wymuszone wyczyszczenie części wpisów z tablicy połączeń poprzez zrestartowanie interfejsu, którym ruch wychodził przed zmianą łącza.

— Bartłomiej Dabiński

 

 

 

Oryginał TU

WordPress Themes