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 ;