FortiSIEM // Incident generation for offline Devices or Agents

[Lukas_Scholz]

Hello everyone,

We’re currently trying to improve our visibility into offline devices in FortiSIEM, specifically in cases where agents or devices silently stop sending logs.

So far, we’ve tested a few approaches for alerting:

• Rules using Followed By / Not Followed By to detect when events stop coming in.

• A rule that triggers if fewer than 50 logs are received in 24 hours (since our agents usually send ~2 status logs per hour).

Unfortunately, these attempts haven’t worked as expected. We’re also not sure what the default “No Log Rule” actually triggers on, as it seems to be an internal event and generates too many false positives.

Has anyone else run into the need for better alerting/reporting on devices that stop sending logs (due to agent failures, collector silent failures, etc.)? If so, how did you solve it?

For reference, here’s one of the report definitions we’ve built in an attempt to make this more visible on dashboards:

WITH last_seen_per_ip AS (
SELECT
reptDevIpAddr,
MAX(phRecvTime) AS lastSeenTime
FROM fsiem.events
WHERE phRecvTime > (now() - 8640000)
AND eventParsedOk = 1
AND reptDevName NOT LIKE 'HOST-%'
GROUP BY reptDevIpAddr
HAVING MAX(phRecvTime) < (now() - 604800)
),
last_event_with_name AS (
SELECT
e.reptDevIpAddr,
e.reptDevName,
e.phRecvTime
FROM fsiem.events e
JOIN last_seen_per_ip l
ON e.reptDevIpAddr = l.reptDevIpAddr AND e.phRecvTime = l.lastSeenTime
WHERE e.eventParsedOk = 1
)
SELECT
reptDevName AS `Last Known Hostname`,
reptDevIpAddr AS `IP Address`,
phRecvTime AS `Last Reporting Time`
FROM last_event_with_name
ORDER BY `Last Reporting Time` DESC
LIMIT 500;

This helps us see devices that haven’t reported in the last 7 days, but we’re still struggling to generate reliable alerts that don’t overwhelm us with false positives.

If anyone has suggestions, templates, or lessons learned around this topic, we’d really appreciate your input!

Best Regards,
Lukas Scholz
FortiSIEM

[cdurkin_FTNT]

Hi Lukas

I can share some queries I have tested around event limits, for reporting purposes.

Just to add, a non SQL nested query can also be used here to catch devices not reporting in particular time periods, see

https://community.fortinet.com/t5/FortiSIEM-Discussions/How-do-i-get-devices-not-sending-logs-in-las...

Just to note, these are experimental from my own testing.

# Advanced SQL Queries

Please find 4 different queries here, that all make use of a Lookup Table.

# Lookup Table Definition

- Name: eventTypeTracker

- Description: Tracks Devices and Critical Event Types and Limits

Table Schema:

Key	Name	Type
YES	reportingIP	STRING
YES	eventType	STRING
NO	limit	LONG

Then in the Lookup Table define your devices and eventType to track and evaluation period limit
ie:

reportingIP	eventType	limit
10.10.10.44	AO-WUA-UserFile-MyCriticalLog	55
10.10.10.20	Win-Security-4624	10000

(Only the first 2 queries make use of the eventType column, you can define the table without that column if you only intend to test queries 3 and 4)

# Advanced SQL 1 - Reporting IP and Event Type Limit

- This should return entries in the Lookup Table that have not met the defined event specific limit.

WITH
    now() AS current_time,
    subtractHours(current_time, 1) AS start_time

SELECT
    d.reportingIP,
    d.eventType,
    d.limit,
    coalesce(e.received_count, 0) AS received_count
FROM fsiem.eventTypeTracker_1 AS d
LEFT JOIN (
    SELECT
        reptDevIpAddrV4 AS reportingIP,
        eventType,
        count() AS received_count
    FROM fsiem.events
    WHERE phRecvTime >= start_time AND phRecvTime < current_time
    GROUP BY reptDevIpAddrV4, eventType
) AS e
ON d.reportingIP = e.reportingIP AND d.eventType = e.eventType
WHERE
    (d.limit = 0 AND coalesce(e.received_count, 0) = 0)
    OR
    (d.limit > 0 AND coalesce(e.received_count, 0) < d.limit)

Query1

# Advanced SQL 2 - Reporting IP and Event Type Limit

- This should return entries in the Lookup Table that have not met the defined event specific limit, but only evaluates on Week Days and in Business Hours and also displays the evaluation time period.

WITH
    60 AS interval_minutes,  -- Evaluation Interval, ie: Last 15, 30, 60 mins etc
    now() AS current_utc,
    now() - INTERVAL interval_minutes MINUTE AS start_window_utc,
    toDayOfWeek(toTimeZone(current_utc, 'America/New_York')) AS day_of_week -- Timezone

SELECT
    d.reportingIP AS "Reporting IP",
    d.eventType AS "Event Type",
    d.limit AS "Period Limit",
    coalesce(e.received_count, 0) AS "Period Events Received",
    formatDateTime(toTimeZone(start_window_utc, 'America/New_York'), '%R') || '–' ||
    formatDateTime(toTimeZone(current_utc, 'America/New_York'), '%R') AS "Evaluation Interval" -- Timezone
FROM fsiem.eventTypeTracker_1 AS d
LEFT JOIN (
    SELECT
        reptDevIpAddrV4 AS reportingIP,
        eventType,
        count() AS received_count
    FROM fsiem.events
    WHERE
        phRecvTime >= start_window_utc
        AND phRecvTime <= current_utc
        AND toHour(toTimeZone(phRecvTime, 'America/New_York')) BETWEEN 8 AND 17 -- Timezone and Business Hours
    GROUP BY reptDevIpAddrV4, eventType
) AS e
ON d.reportingIP = e.reportingIP AND d.eventType = e.eventType
WHERE
    day_of_week BETWEEN 1 AND 5  -- Monday to Friday
    AND (
        (d.limit = 0 AND coalesce(e.received_count, 0) = 0)
        OR
        (d.limit > 0 AND coalesce(e.received_count, 0) < d.limit)
    )

Query 2

# Advanced SQL 3 - Reporting IP  Limit Only

- Same as Advanced SQL 1, but only evaluates the Reporting IP for Week Days and Business Hours and displays the evaluation interval.

WITH
    60 AS interval_minutes,  -- Evaluation Interval, ie: Last 15, 30, 60 mins etc
    now() AS current_utc,
    now() - INTERVAL interval_minutes MINUTE AS start_window_utc,
    toDayOfWeek(toTimeZone(current_utc, 'America/New_York')) AS day_of_week -- Timezone

SELECT
    d.reportingIP AS "Reporting IP",
    d.limit AS "Period Limit",
    coalesce(e.received_count, 0) AS "Period Events Received",
    formatDateTime(toTimeZone(start_window_utc, 'America/New_York'), '%R') || '–' ||
    formatDateTime(toTimeZone(current_utc, 'America/New_York'), '%R') AS "Evaluation Interval" -- Timezone
FROM fsiem.eventTypeTracker_1 AS d
LEFT JOIN (
    SELECT
        reptDevIpAddrV4 AS reportingIP,
        count() AS received_count
    FROM fsiem.events
    WHERE
        phRecvTime >= start_window_utc
        AND phRecvTime <= current_utc
        AND toHour(toTimeZone(phRecvTime, 'America/New_York')) BETWEEN 8 AND 17 -- Timezone and Business Hours
    GROUP BY reptDevIpAddrV4
) AS e
ON d.reportingIP = e.reportingIP
WHERE
    day_of_week BETWEEN 1 AND 5  -- Monday to Friday
    AND (
        (d.limit = 0 AND coalesce(e.received_count, 0) = 0)
        OR
        (d.limit > 0 AND coalesce(e.received_count, 0) < d.limit)
    )

Query 3

# Advanced SQL 4 - Reporting IP  Limit Only

- Same as Advanced SQL 1, but only evaluates the Reporting IP for the Last 24 Hours and displays the evaluation interval.

WITH
    now() AS current_utc,
    current_utc - INTERVAL 1 DAY AS start_window_utc,
    toTimeZone(current_utc, 'America/New_York') AS current_et,
    toTimeZone(start_window_utc, 'America/New_York') AS start_et,
    lpad(toString(toHour(start_et)), 2, '0') || ':' || lpad(toString(toMinute(start_et)), 2, '0') || ' ' ||
        formatDateTime(start_et, '%b %d %Y') AS eval_start_str,
    lpad(toString(toHour(current_et)), 2, '0') || ':' || lpad(toString(toMinute(current_et)), 2, '0') || ' ' ||
        formatDateTime(current_et, '%b %d %Y') AS eval_end_str,
    toDayOfWeek(current_et) AS day_of_week

SELECT
    d.reportingIP AS "Reporting IP",
    d.limit AS "Daily Limit",
    coalesce(e.received_count, 0) AS "24h Events Received",
    eval_start_str || ' - ' || eval_end_str AS "Evaluation Interval"
FROM fsiem.eventTypeTracker_1 AS d
LEFT JOIN (
    SELECT
        reptDevIpAddrV4 AS reportingIP,
        count() AS received_count
    FROM fsiem.events
    WHERE
        phRecvTime >= start_window_utc
        AND phRecvTime <= current_utc
    GROUP BY reptDevIpAddrV4
) AS e
ON d.reportingIP = e.reportingIP
WHERE
    (
        (d.limit = 0 AND coalesce(e.received_count, 0) = 0)
        OR
        (d.limit > 0 AND coalesce(e.received_count, 0) < d.limit)
    )

 Query 4

These can also be added to dashboards.

[Lukas_Scholz]

@cdurkin_FTNT 
Hey, thank you very much for this Information. I am Interested in the 3. and 4. Query. My Issue is that I want this report for all devices in the CMDB. When using the Nested Query Report you showed me it looks like it is working. Issue is that I want those Reports for a Dashboard. When Using a CMDB Report and the Dashboard I always get displayed the information for the last 1 hour. Changing the Nested Query Time in the Dashboard away from 1 Hour does not change anything. This might be a bug in the 7.3.2 tho.  I like the Idea of the Lookup Table Queries. Issue here is that I would have to add all IPs manually. For Special Reports / Thresholds for single devices this is amazing but not applicable to report on all existing Devices, or am I missing something here? Never really worked with Lookup Tables yet.

Best Regards,
Lukas Scholz.

[Lukas_Scholz]

Also any thoughs on how to trigger Rules/Incidents based on this?

[cdurkin_FTNT]

Sure I have something here, I will try and write up later today.. 

As for the Rule/Incidents .. it is possible to turn any SQL Query into a Rule (in 7.4), but unfortunately there is outstanding issue where for these particular queries the incident will trigger but not display the full details of the device not reporting.

[cdurkin_FTNT · Accepted solution]

Lukas, this version references every device in the CMDB via lookup table.

# CMDB Report
1) Create a CMDB Report to return all Devices in CMDB.

For Step 2: Notice how optionally you can filter Device IPs you do not want to include .. (or reference the CMDB directly), such as Supervisor, Collector etc...

Output will look like this is run.

# Lookup Table
2) Create a Lookup Table and define the schema as follows. (exactly)

Name: allCMDBDevices

Columns:
KEY | reportingIP | STRING
KEY | reportingDevice | STRING

3) Click Import and choose the CMDB Report created earlier.

And Map the report as follows

Lookup Table Column | CMDB Report
reportingIP -> Device IP
reportingDevice -> Device Name

4) Enable and set a schedule to import and keep the contents upto date.

5) Click Run Now

6) Your Lookup Table should now be populated.

# Advanced Search Query
Use the Following SQL Query ...

  WITH
    now() AS current_utc,
    current_utc - INTERVAL 1 DAY AS start_window_utc,
    toTimeZone(current_utc, 'America/New_York') AS current_et,
    toTimeZone(start_window_utc, 'America/New_York') AS start_et,
    lpad(toString(toHour(start_et)), 2, '0') || ':' || lpad(toString(toMinute(start_et)), 2, '0') || ' ' ||
        formatDateTime(start_et, '%b %d %Y') AS eval_start_str,
    lpad(toString(toHour(current_et)), 2, '0') || ':' || lpad(toString(toMinute(current_et)), 2, '0') || ' ' ||
        formatDateTime(current_et, '%b %d %Y') AS eval_end_str,
    toDayOfWeek(current_et) AS day_of_week

SELECT
    d.reportingIP AS "Reporting IP",
    d.reportingDevice AS "Reporting Device",
    coalesce(e.received_count, 0) AS "24h Events Received",
    eval_start_str || ' - ' || eval_end_str AS "Evaluation Interval"
FROM fsiem.allCMDBDevices_1 AS d
LEFT JOIN (
    SELECT
        reptDevIpAddrV4 AS reportingIP,
        reptDevName AS reportingDevice,
        count() AS received_count
    FROM fsiem.events
    WHERE
        phRecvTime >= start_window_utc
        AND phRecvTime <= current_utc
    GROUP BY reptDevIpAddrV4, reptDevName
) AS e
ON d.reportingIP = e.reportingIP AND d.reportingDevice = e.reportingDevice
WHERE
    (
        (e.received_count = 0)

    )

To return all devices that have not reported data in the last 24 hours.

(change the INTERVAL as necessary in the query to increase/decrease the time)

If you want to double check its working you can edit this line..

(e.received_count = 0)
to
(e.received_count != 0)

And see devices that have sent more than zero events in the last 24 hours.

I hope that helps.

[adem_netsys]

Hi @cdurkin_FTNT 

When I test it this way, it says the table cannot be found. How can we get around this? I can see it in the lookup table.

[cdurkin_FTNT]

did you create the lookup table EXACTLY as shown above? .. ie same name, case etc..

[adem_netsys]

Yes, did way but The error ‘Table not found in the database’ is being displayed.

[Lukas_Scholz]

Hey,
Thank you very much for this extensive help.
I will test out the Queries. In the mean time I found out how to set the treshold for the Internal Log Delay Events thus having a Incident as well. Important Logs By Use case | FortiSIEM 7.3.0 | Fortinet Document Library