SQL agenti

Funkce SQL agent slouží k načítání dat z libovolné tabulky v SQL databázi. Typicky se jedná o tabulky s protokolem uživatelských událostí z různých aplikací, využívajících jako úložiště dat SQL databázi.

Pro připojení k SQL databázi je nutné splnit následující požadavky:

1. tabulka obsahuje atribut typu timestamp ve formátu YYYY-MM-DD hh:mm:ss nebo YYYY-MM-DD hh:mm:ss.fff,
2. korektně nastavena časová zóna na serveru,
3. povolena komunikace mezi Logmanager serverem a SQL serverem (specifické TCP a UDP porty),
4. uživatel s oprávněním ke čtení požadované tabulky v databázi s povoleným přístupem z IP adresy serveru Logmanager.
5. Velikost dat ve sloupcích je limitována vlastnostmi databázových konektorů: Oracle má limit 4000 bajtů, MySQL má limit 65,535 bajtů a Microsoft SQL má limit 8000 znaků.

Atribut timestamp lze automaticky přepočítávat na čas v UTC.

• PostgreSQL server
• MySQL server (Maria DB)
• Oracle Database server
• Microsoft SQL server

Informace o podporovaných jednotlivých verzích najdete v tabulkách níže.

PostgreSQL server:

Další informace o jednotlivých verzích PostgreSQL najdete zde: https://www.postgresql.org/support/versioning/.

MariaDB:

Další informace o MariaDB najdete zde: https://en.wikipedia.org/wiki/MariaDB.

Microsoft SQL server

Další informace o jednotlivých verzích Microsoft SQL Server najdete zde: https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Server.

MSSQL konektor podporuje připojení pomocí instancí, je možné je zadat do field Host ve formátu IP addresa\instance.

Pro podporu instancí Microsoft SQL serveru musí být spuštěna služba SQL Server Browser.

SQL agent se připojí na SQL Server Browser službu na UDP portu 1434 na konkrétním serveru, zjistí dostupná jména instancí a jejich portů. Pokud se najde shoda jména instance, proběhne připojení na konkrétní port. Pokud není shoda nalezena, proběhne pokus o připojení na standardní port TCP 1433.

Oracle Database server:

Oracle konektor podporuje připojení pomocí Instance/Service name, je nutné je zadat do políčka Host ve formátu IP address/instance.

Příklad připojení: db_instance.example.com oracle server instance provozována na hostu db.example.com:

field Host: db.example.com/db_instance.example.com

field Database: db_instance

Pro zjištění jména instance spustťe na Oracle serveru příkaz:

lsnrctl status

Databáze Oracle má svoje specifické chování pro velikosti písmen v názvech tabulek a sloupců v tabulkách. Při zadávání nového agenta je potřeba dodržovat velikost písmen tak, jak jsou uloženy v Oracle databázi.

Jak správně zadat údaje do SQL agenta:

• jméno a heslo pro připojení do databáze nesmí obsahovat UTF-8 znaky
• název tabulky nesmí obsahovat UTF-8 znaky

1. Přihlaste se na Logmanager server.
2. Přejděte na Zdroje ‣ SQL a klikněte na přidejte nového agenta.

   

3. Ve formuláři zadejte následující údaje:
   • Název: libovolné jméno SQL agenta
   • Typ: typ databázového serveru
   • Hostitel: IP adresa nebo DNS jméno, případně IP adresa\instance (pouze pro Microsoft SQL a Oracle Database)
   • Port: port, na kterém běží SQL server
   • Uživatelské jméno: uživatel na SQL serveru
     • pro Microsoft SQL: „SQL Server authentication login“ uživatelské jméno
   • Heslo: heslo na SQL server
   • Databáze: jméno databáze
   • Schéma: jméno schématu (pouze pro Microsoft SQL)
   • Tabulka: jméno databázové tabulky
   • Znaková sada: charset databázové tabulky
   • Interval: Časový interval definující, jak často si Logmanager bude z SQL serveru stahovat nová data. Výchozí hodnota je 30 sekund. Nastavit vlastní interval lze v rozmezí 10 - 600 sekund.
   • Sloupec s časovou značkou: jméno atributu typu timestamp nebo datetime v databázové tabulce ve formátu YYYY-MM-DD hh:mm:ss nebo YYYY-MM-DD hh:mm:ss.fff
   • Časová značka v UTC: určuje, zda je čas v tabulce v UTC (povoleno nebo zakázáno)
   • Sloupce: seznam atributů, které se budou načítat do Logmanageru
   • Značky: uživatelské značky (Značky)

     

4. Klikněte na tlačítko Vytvořit.

Data z SQL serveru se začnou automaticky načítat na server Logmanager.

Pro editaci existujícího agenta lze kliknout na ikonku tužky, tím se spustí úprava agenta. Při úpravě lze změnit všechny zadávané údaje.

Po provedení úprav lze změny uložit kliknutím na tlačítko uložit, pro zachování původního stavu lze kliknout na tlačítko zrušit.

SQL agent lze smazat pomocí ikonky křížku. Tím se otevře dialog pro smazání.

Pro potvrzení je zobrazen název agenta, který se odebírá. Pro odebrání se použije tlačítko ano, pro zrušení se použije tlačítko ne.

Při prvním spuštění SQL agent postupuje podle následujícího schematu:

1. přihlásí se do databázového serveru poskytnutými přístupovými údaji,
2. zjistí časovou zónu nastavenou na databázi,
3. vypočítá hodnotu “čas od” - zjistí aktuální čas,
4. spustí query na zjistění počtu záznamů v nastavené databázové tabulce s časovým intervalem 1 minuta podle hodnoty “čas od”,
5. pokud je počet záznamů menší než 100 000:
   • spustí query pro získání dat na nastavenou databázovou tabulku s časovým intervalem 1 minuta v aktuálním čase,
   • pokračuje na bod 7.
6. pokud je počet záznamů větší než 100 000:
   • pokračuje od bodu 4, pouze použije poloviční časový interval než v předchozí iteraci.
7. získaná data uloží do systému Logmanager,
8. čeká 1 sekundu,
9. uloží si čas posledního zpracovaného řádku,
10. pokračuje od bodu 4 a použije nový časový interval posunutý o 1 sekundu,
11. čeká X sekund a poté pokračuje od bodu 4 s časovým intervalem rozšířeným o X sekund. Kde X je vámi nastavený interval v konfiguraci daného SQL konektoru.

Při opětovném spuštění SQL agent postupuje podle následujícího schematu:

1. přihlásí se do databázového serveru poskytnutými přístupovými údaji,
2. zjistí časovou zónu nastavenou na databázi,
3. vypočítá hodnotu “čas od” - zjistí čas posledního uloženého řádku z databázové tabulky,
4. pokračuje od bodu 4 v předchozím odstavci.

Agent načítá data z databázového serveru od nejstarších po nejnovější. Pokud agent delší dobu nekomunikoval s databázovým serverem (například z důvodu nedostupnosti databázového serveru), začne načítat data od času poslední úspěšné komunikace. Podle zatížení Vašeho serveru může trvat delší dobu, než se zpracuje fronta všech nezpracovaných řádků z databázového serveru. Proto se aktuální data z SQL agenta mohou zobrazovat se zpožděním.

V této kapitole se dozvíte jakým způsobem si vytvořit jednoduché základní pravidlo pro parsování sql.

V Logmanager v záložce Parser ‣ Parsovací pravidla vytvořte nové parsovací pravidlo.

SQL agent posílá do Logmanager data ve formátu JSON. Pro názornost v tomto návodu si do textového pole Test message nakopírujte například tuto zprávu:

{
  "username": "Michael",
  "SRC_IP": "192.0.2.0",
  "Query": "SELECT * FROM `user`"
}

Vytvořte si pomocnou proměnnou „item“ do které pomocí bloku update_dictionary a bloku decode_JSON expandujete všechna pole:

V tuto chvíli jsou všechny klíče pojmenované tak, jak je zařízení poslalo. Je nutné aby názvy těchto klíčů obsahovaly pouze malá písmena. K tomu poslouží bloky rename_to a to_lowercase.

Cyklem foreach se projdou všechny klíče a pokud splní podmínku (název klíče obsahuje velká písmena), provede se přejmenování tohoto klíče:

Typická úprava došlých dat se provádí u políčka „username“, kde se předpokládá, že jméno uživatele bude malými písmeny. Pomocí bloku in_dictionary_set se přepíše původní hodnota pole na malá písmena (blok to_lowercase):

Nyní jsou všechna pole datového typu string, pokud je potřeba změnit datový typ, je k tomu určen blok retype_to. Do další pomocné proměnné si nadefinujte seznam klíčů, u kterých se bude provádět změna datového typu. Tento seznam potom nechte projít cyklem a pokud se daný klíč vyskytuje, proveďte retype na požadovaný datový typ:

Nakonec celou proměnnou „item“ vložte do hlavního slovníku message_data pomocí bloku update_dictionary. A jako poslední vložte blok return_msg, který se postará o uložení výsledné zprávy do Logmanager, kde ji uvidíte jako objekt msg:

Výsledný pohled na celý parser:

Parser v okně Test output nyní generuje takovéto hodnoty:

msg:Object
  query:"SELECT * FROM `user`"
  src_ip:"192.0.2.0"
    src_ip@ip:Object
      city:"Unknown"
      country_code:"Un"
      country_name:"Unknown"
      is_link_local:false
      is_multicast:false
      is_reserved:false
      ptr:"192.0.2.0"
      value:"192.0.2.0"
      version:4
  username:"michael"
raw:"{"username": "Michael", "SRC_IP":"192.0.2.0","Query":"SELECT * FROM `user`"}"

Parser lze stáhnout ve formátu XML zde SQL parser

Komplexní návod na tvorbu parseru je k vidění v kapitole Návod na vytvoření parseru. chapter.