Find Realtime queries executed in the last 24 hours

Find Realtime queries executed in the last 24 hours



This article includes the query to find all the real time queries that were executed in the last 24 hours



Query

WITH queries

     AS (SELECT Json_extract_scalar(logs.api_body, '$.query')   AS query,

                Json_extract_scalar(logs.api_body, '$.type')    AS TYPE,

                Json_extract_scalar(logs.api_body, '$.filters') AS filters,

                u.name,

                logs.api_requested_time

         FROM   upt_api_audit_logs logs

                inner join upt_users u

                        ON u.id = logs.created_by

         WHERE  logs.api_name LIKE '%queryJobs'

                AND logs.api_method = 'POST'

                AND logs.api_requested_time >

                    Date_add('hour', -24, current_timestamp

                    )

                AND logs.upt_day > Cast(Date_format(localtimestamp -

                                                    interval '48' hour,

                                        '%Y%m%d')

                                        AS INTEGER))

SELECT *

FROM   queries

WHERE  TYPE = 'realtime'

ORDER  BY api_requested_time DESC 






    • Related Articles

    • Query to find Processes delta between last 2 weeks

      Query WITH last_week_processes_cmds AS ( SELECT DISTINCT upt_asset_id, name, cmdline, path FROM processes WHERE upt_day >= Cast(Date_format(current_date - interval '14' day, '%Y%m%d') AS INTEGER) AND upt_day < Cast(Date_format(current_date - interval ...

    • Windows Forensic Analysis queries

      This article includes a  list of queries to conduct forensic analysis on Windows systems.  These are categorized into sections as -      processes,      services,      file system activities,     user login/session activities,     network traffic.   ...

    • Query to find the Average CPU Load for the last 1 Hr.

      Query SELECT upt_asset_id, Avg((100 - (Cast(cpu_idle AS DOUBLE)/Cast(cpu_total AS DOUBLE))*100)) AS avg_busy_percent , Avg((Cast(cpu_idle AS DOUBLE)/Cast(cpu_total AS DOUBLE))*100) AS idle_percent FROM upt_metrics WHERE upt_time >= localtimestamp - ...

    • Top resource consuming queries for an asset

      Top 10 wall_time consuming queries SQL Query SELECT name, query, wall_time, user_time, system_time, executions FROM osquery_schedule WHERE upt_asset_id = '<asset_id>' AND upt_day = <upt_day> ORDER BY wall_time DESC LIMIT 10; Top 10 Overall CPU time ...

    • Query to find Processes Running

      Processes running between certain times Query SELECT * FROM windowed_processes p WHERE p.upt_add_time >= <TIMESTAMP> AND p.upt_add_time <= <TIMESTAMP>; Example All processes running between 2018-07-18 2:00 to 2018-08-18 3:00 SELECT * FROM ...