Advanced Search Examples

This section offers some simple examples to demonstrate the basic SQL building blocks. These examples are intended to be followed in sequential order.

• Example 1 - Query Top 10 Reporting Devices by Event Count in Last 1 Hour
• Example 2 - Add the Reporting Vendor and Model of the Reporting Devices and the Last Time that They Reported an Event
• Example 3 - Show the Reporting IP and the Top Event Types for these Devices and only Include External Events
• Example 4 - Show the External Events from a Specific Reporting Device with a Specific IP
• Example 5 - Show Information from a Specific CMBD Group
• Example 6 - Show Reporting Devices Reporting External Events Today that were not Reporting External Events Yesterday
• Example 7 - Only Return Top 5 Event Types for Each Reporting Device

Example 1 - Query Top 10 Reporting Devices by Event Count in Last 1 Hour

Lets start with this query: Top 10 Reporting Devices by Event Count in last 1 hour.

SELECT
    reptDevName AS `Reporting Device`,
    COUNT(*) AS `Total Events`
FROM fsiem.events
WHERE phRecvTime > (now() - 3600) AND eventParsedOk=1
GROUP BY reptDevName
ORDER BY COUNT(*) DESC
LIMIT 10

Example 2 - Add the Reporting Vendor and Model of the Reporting Devices and the Last Time that They Reported an Event

Suppose you want to see the reporting vendor and model of the reporting devices and the last time that they reported an event. Then run the following query:

SELECT
    reptDevName AS `Reporting Device`,
    reptVendor AS Vendor,reptModel AS Model,
    COUNT(*) AS `Total Events`,
    MAX(phRecvTime) AS `Last Reporting Time`
FROM fsiem.events
WHERE phRecvTime > (now() - 3600) AND eventParsedOk=1
GROUP BY
    reptDevName,
    reptVendor,reptModel
ORDER BY COUNT(*) DESC
LIMIT 10

Example 3 - Show the Reporting IP and the Top Event Types for these Devices and only Include External Events

Suppose you want to see the Reporting IP and the Top Event Types for these devices and only include external events. Then run the following query:

SELECT
    reptDevName AS `Reporting Device`,
    reptDevIpAddrV4 AS `Reporting IP`,eventType AS `Event Type`,
    reptVendor AS Vendor,
    reptModel AS Model,
    COUNT(*) AS `Total Events`,
    MAX(phRecvTime) AS `Last Reporting Time`
FROM fsiem.events
WHERE (phRecvTime > (now() - 3600)) AND eventParsedOk=1  AND (phEventCategory IN (0, 4))
GROUP BY
    reptDevName,
    reptDevIpAddrV4,eventType,
    reptVendor,
    reptModel
ORDER BY COUNT(*) DESC
LIMIT 10

Example 4 - Show the External Events from a Specific Reporting Device with a Specific IP

Suppose you want to choose a specific reporting device named WIN-ABC with Reporting IP as 10.1.1.1. Then run the following query:

SELECT
    reptDevName AS `Reporting Device`,
    reptDevIpAddrV4 AS `Reporting IP`,
    eventType AS `Event Type`,
    reptVendor AS Vendor,
    reptModel AS Model,
    COUNT(*) AS `Total Events`,
    MAX(phRecvTime) AS `Last Reporting Time`
FROM fsiem.events
WHERE (phRecvTime > (now() - 3600)) AND eventParsedOk=1 AND (phEventCategory IN (0, 4)) AND ((reptDevName = 'WIN-ABC') OR (reptDevIpAddrV4 = '10.1.1.1'))
GROUP BY
    reptDevName,
    reptDevIpAddrV4,
    eventType,
    reptVendor,
    reptModel,
ORDER BY COUNT(*) DESC
LIMIT 10

Example 5 - Show Information from a Specific CMBD Group

Instead of focusing on a single device, suppose you want to focus on devices in CMDB Group: Devices > Server > Windows. Then use the CMDB Group Converter to generate the query.

To create this CMDB group, click on CMDB Group Converter, and in the CMDB Group Converter window, take the following steps.

1. Set Attribute as Reporting IP.
2. Set Operator as IN.
3. Set Value as Group: Windows by taking the following steps.
   1. Click Value and click Select from CMDB.
   2. From Folders, navigate to Devices > Server > Windows, and ensure Windows is selected.
   3. Click >> to create group, then OK.
   4. Click Convert & Copy.

SELECT
    reptDevName AS `Reporting Device`,
    reptDevIpAddrV4 AS `Reporting IP`,
    eventType AS `Event Type`,
    reptVendor AS Vendor,
    reptModel AS Model,
    COUNT(*) AS `Total Events`,
    MAX(phRecvTime) AS `Last Reporting Time`
FROM fsiem.events
WHERE (phRecvTime > (now() - 3600)) AND eventParsedOk=1 AND (phEventCategory IN (0, 4)) AND (dictHas('fsiem.dict_cmdb_group', (phCustId, toString(fsiem.events.reptDevIpAddr), 'PH_SYS_DEVICE_WINDOWS_SERVER')) OR dictHas('fsiem.dict_cmdb_group', (toUInt32(0), toString(fsiem.events.reptDevIpAddr), 'PH_SYS_DEVICE_WINDOWS_SERVER')) OR dictHas('fsiem.dict_cmdb_group', (phCustId, toString(fsiem.events.reptDevIpAddr), 'PH_SYS_DEVICE_WINDOWS_SERVER:IntfIP')) OR dictHas('fsiem.dict_cmdb_group', (toUInt32(0), toString(fsiem.events.reptDevIpAddr), 'PH_SYS_DEVICE_WINDOWS_SERVER:IntfIP')))
GROUP BY
    reptDevName,
    reptDevIpAddrV4,
    eventType,
    reptVendor,
    reptModel
ORDER BY COUNT(*) DESC
LIMIT 10

Example 6 - Show Reporting Devices Reporting External Events Today that were not Reporting External Events Yesterday

Suppose you want to see the reporting devices that are reporting external events today but were not reporting yesterday. This uses the SQL SubQuery capability. Then run the following query:

WITH reporting_devices_last_1_day AS
    (
        SELECT DISTINCT reptDevName
        FROM fsiem.events
        WHERE (phRecvTime >= toStartOfDay(now() - (1 * 86400))) AND eventParsedOk=1 AND (phRecvTime < toStartOfDay(now())) AND (phEventCategory IN (0, 4))
    )
SELECT DISTINCT
    reptDevName AS `Reporting Device`,
    reptDevIpAddrV4 AS `Reporting IP`,
    reptVendor AS Vendor,
    reptModel AS Model
FROM fsiem.events
WHERE (phRecvTime >= toStartOfDay(now())) AND eventParsedOk=1 AND (phEventCategory IN (0, 4)) AND (reptDevName NOT IN (
    SELECT reptDevName
    FROM reporting_devices_last_1_day
))
ORDER BY reptDevName ASC

Example 7 - Only Return Top 5 Event Types for Each Reporting Device

Lets go back to Example 3, but only return Top 5 event types for each reporting device. This is achieved via the SQL Window function (For more information, see https://clickhouse.com/docs/en/sql-reference/window-functions and https://www.postgresql.org/docs/current/tutorial-window.html).

WITH subquery AS
    (
        SELECT
            reptDevName,
            eventType,
            count() AS count,
            rank() OVER (PARTITION BY reptDevName ORDER BY count DESC) AS rank
        FROM fsiem.events
        WHERE phRecvTime > (now() - 3600) AND eventParsedOk=1
        GROUP BY ALL
    )
SELECT
    reptDevName AS `Reporting Device`,
    eventType AS `Event Type`,
    count
FROM subquery
WHERE rank <= 5