QUESTION

I have set up a log source in Panther with logs coming in from separate systems that are each identified with a unique ID. I'd like to be able to detect when we stop receiving records for any of these IDs for a specific period of time. How can I accomplish that?

ANSWER

To create such a workflow, there are two options:

• You could use an S3 lookup table that you manage, which will hold the IDs. Then a scheduled query will join to that table and perform the activity check.

• You could create a scheduled query that compares historical IDs to today. Let's assume that the IDs are expected to appear once a day. A query could gather all the IDs that occurred over the last week and alert for any IDs that were not seen in the last 24 hours. This could be done in a single query.
  
  Below you can find an example query that implements the above logic. It checks for all the unique IDs over the last week and returns the ones that have not been seen in the last 24 hours:

  WITH mock_data as (SELECT *
  FROM
    (
      Values
          ( current_timestamp - interval '36 hours', 'alice', '1'), -- alice logged in yesterday
          ( current_timestamp - interval '36 hours', 'bob', '2'), -- bob logged in yesterday
          ( current_timestamp - interval '36 hours', 'charlie', '3'), -- charlie logged in yesterday
          ( current_timestamp - interval '12 hours', 'alice', '1') -- alice logged in today, bob and charlie have not
    ) AS TempTableName(p_event_time, firstname, id))
  
  -- the above is all setup, for the real query replace "mock_data" with the actual table name in the query below
  select id AS missing_id, any_value(firstname) firstname
  from mock_data where 
  p_occurs_since('7 days') AND
  missing_id not in (
    select distinct id
    from mock_data where 
    p_occurs_since('1 days') 
  )
  group by missing_id, firstname