Contents
Exporting events directly from the database
You can retrieve events directly from the Kaspersky Security Center database without having to use the Kaspersky Security Center 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 Kaspersky Security Center 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 Kaspersky Security Center 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 Kaspersky Security Center 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 download and use the klsql2 utility, and how to execute an SQL query by using this utility. When you execute an SQL query by means of the klsql2 utility, you do not have to provide database name and access parameters, because the query addresses Kaspersky Security Center public views directly.
To use the klsql2 utility:
- Locate the klsql2 utility in the installation folder of Kaspersky Security Center. The default installation path is <Disk>:\Program Files (x86)\Kaspersky Lab\Kaspersky Security Center. Do not use klsql2 utility versions intended for older Kaspersky Security Center versions.
- Create the src.sql file in any text editor and place the file in the same folder with the utility.
- 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 execute the SQL query from the src.sql file and save the results to the result.xml file:
klsql2 -i src.sql -o result.xml - Open the newly created result.xml file to view the SQL query results.
You can edit the src.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 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 Kaspersky Security Center database name
It can be helpful to know a database name if you need, for example, send an SQL query and connect to the database from your SQL script editor.
To view the name of the Kaspersky Security Center database:
- In the Kaspersky Security Center console tree, open the context menu of the Administration Server folder and select Properties.
- In the Administration Server properties window, in the Sections pane select Advanced and then Details of current database.
- In the Details of current database section, note the following database properties (see figure below):
- Instance name
- Database name
- Space allocated for the Kaspersky Security Center database
- Size of data in the entire database
- Number of events stored in the database
Section with information about the current Administration Server database
- Click the OK button to close the Administration Server properties window.
Use the database name to address the database in your SQL queries.