Contents
Exporting events directly from the database
You can retrieve events directly from the Open Single Management Platform database without having to use the Open Single Management Platform interface. You can either query the public views directly and retrieve the event data, or create your own views on the basis of existing public views and address them to get the data you need.
Public views
For your convenience, a set of public views is provided in the Open Single Management Platform database. You can find the description of these public views in the klakdb.chm document.
The v_akpub_ev_event public view contains a set of fields that represent the event parameters in the database. In the klakdb.chm document you can also find information on public views corresponding to other Open Single Management Platform entities, for example, devices, applications, or users. You can use this information in your queries.
This section contains instructions for executing an SQL query by means of the klsql2 utility and a query example.
To create SQL queries or database views, you can also use any other program for working with databases. Information on how to view the parameters for connecting to the Open Single Management Platform database, such as instance name and database name, is given in the corresponding section.
Executing an SQL query by using the klsql2 utility
This article describes how to use the klsql2 utility, and execute an SQL query by using this utility. Use klsql2 utility version that is included in your Open Single Management Platform version installed.
To use the klsql2 utility:
- Go to the directory where Administration Server is installed. The default installation path is /opt/kaspersky/ksc64/sbin.
- In this directory, create a blank file with the .sql extension.
- Open the created .sql file in any text editor.
- In the .sql file, type the SQL query that you want, and then save the file.
- On the device with Administration Server installed, in the command line, type the following command to execute the SQL query from the .sql file and save the results to the result.xml file:
sudo ./klsql2 -i src.sql -u <
username
> -p <
password
> -o result.xml
where
<
username
>
and<
password
>
are credentials of the user account that has access to the database. - If required, enter the login and password of the user account that has access to the database.
- Open the newly created result.xml files to view the SQL query results.
You can edit the .sql file and create any SQL query to the public views. Then, from the command line, execute your query and save the results to a file.
Example of an SQL query in the klsql2 utility
This section shows an example of an SQL query, executed by means of the klsql2 utility.
The following examples illustrate retrieval of the events that occurred on devices during the last seven days, and display of the events ordered by the time they occur, the most recent events are displayed first.
Example for Microsoft SQL Server: SELECT e.nId, /* event identifier */ e.tmRiseTime, /* time, when the event occurred */ e.strEventType, /* internal name of the event type */ e.wstrEventTypeDisplayName, /* displayed name of the event */ e.wstrDescription, /* displayed description of the event */ e.wstrGroupName, /* name of the group, where the device is located */ h.wstrDisplayName, /* displayed name of the device, on which the event occurred */ CAST(((h.nIp / 16777216) & 255) AS varchar(4)) + '.' + CAST(((h.nIp / 65536) & 255) AS varchar(4)) + '.' + CAST(((h.nIp / 256) & 255) AS varchar(4)) + '.' + CAST(((h.nIp) & 255) AS varchar(4)) as strIp /* IP address of the device, on which the event occurred */ FROM v_akpub_ev_event e INNER JOIN v_akpub_host h ON h.nId=e.nHostId WHERE e.tmRiseTime>=DATEADD(Day, -7, GETUTCDATE()) ORDER BY e.tmRiseTime DESC |
Example for PostgreSQL: SELECT "e"."nId", /* event identifier */ "e"."tmRiseTime", /* time, when the event occurred */ "e"."strEventType", /* internal name of the event type */ "e"."wstrEventTypeDisplayName", /* displayed name of the event */ "e"."wstrDescription", /* displayed description of the event */ "e"."wstrGroupName", /* displayed description of the event */ "h"."wstrDisplayName", /* displayed name of the device, on which the event occurred */ ( CAST((("h"."nIp" / 16777216 )& 255 ) AS VARCHAR(4)) || '.' || CAST((("h"."nIp" / 65536 )& 255 ) AS VARCHAR(4)) || '.' || CAST((("h"."nIp" / 256 )& 255 ) AS VARCHAR(4)) || '.' || CAST((("h"."nIp" )& 255 ) AS VARCHAR(4)) ) AS "strIp" /* IP address of the device, on which the event occurred */ FROM "v_akpub_ev_event" AS "e" INNER JOIN "v_akpub_host" AS "h" ON "h"."nId" = "e"."nHostId" WHERE "e"."tmRiseTime" >= NOW() AT TIME ZONE 'utc' + make_interval(days => CAST(-7 AS INT)) ORDER BY "e"."tmRiseTime" DESC ; |
Example for MySQL or MariaDB: SELECT `e`.`nId`, /* event identifier */ `e`.`tmRiseTime`, /* time, when the event occurred */ `e`.`strEventType`, /* internal name of the event type */ `e`.`wstrEventTypeDisplayName`, /* displayed name of the event */ `e`.`wstrDescription`, /* displayed description of the event */ `e`.`wstrGroupName`, /* device group name */ `h`.`wstrDisplayName`, /* displayed name of the device, on which the event occurred */ CONCAT( LEFT(CAST(((`h`.`nIp` DIV 1677721) & 255) AS CHAR), 4), '.', LEFT(CAST(((`h`.`nIp` DIV 65536) & 255) AS CHAR), 4), '.', LEFT(CAST(((`h`.`nIp` DIV 256) & 255) AS CHAR), 4), '.', LEFT(CAST(((`h`.`nIp`) & 255) AS CHAR), 4) ) AS `strIp` /* IP address of the device, on which the event occurred */ FROM `v_akpub_ev_event` AS `e` INNER JOIN `v_akpub_host` AS `h` ON `h`.`nId` = `e`.`nHostId` WHERE `e`.`tmRiseTime` >= ADDDATE( UTC_TIMESTAMP( ) , INTERVAL -7 DAY) ORDER BY `e`.`tmRiseTime` DESC ; |
Viewing the Open Single Management Platform database name
If you want to access Open Single Management Platform database by means of the MySQL, or MariaDB database management tools, you must know the name of the database in order to connect to it from your SQL script editor.
To view the name of the Open Single Management Platform database:
- In the main menu, click the settings icon (
) next to the name of the required Administration Server.
The Administration Server properties window opens.
- On the General tab, select the Details of current database section.
The database name is specified in the Database name field. Use the database name to address the database in your SQL queries.