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 creating 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.
Creating an SQL query using the klsql2 utility
This article describes how to use the klsql2 utility, and create 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 Kaspersky Next XDR Expert Administration Server is installed. The default installation path is /opt/kaspersky/ksc64/sbin.
- In this directory, create src.sql blank file.
- Open the src.sql file in any text editor.
- In the src.sql file, type the SQL query that you want, and then save the file.
- On the device with Kaspersky Security Center Administration Server installed, in the command line, type the following command to run the SQL query from the src.sql file and save the results to the result.xml file:
sudo ./klsql2 -i src.sql -u <username> -p <password> -o result.xmlwhere
<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 file to view the query results.
You can edit the src.sql file and create any 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, created by means of the klsql2 utility.
The following example illustrates 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: 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 |
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.