OpenVPN query

OpenVPN query

The following query should identify all assets with OpenVPN installed and last time it was used

SELECT p.upt_hostname AS asset_name, MAX(p.upt_time) AS openvpn_last_accessed
FROM socket_events se
JOIN processes p on se.pid = p.pid
WHERE se.pid > 0 and se.path like '%openvpn%'
GROUP BY p.upt_hostname ORDER BY p.upt_hostname

    • Related Articles

    • Windows Disk Usage query

      The article includes query to fetch the Windows Disk usage: Query SELCET DISTINCT                 upt_hostname,                 upt_time,                 device_id,                 size,                 free_space,                 size - free_space ...

    • 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 ...

    • High CPU Utilization Alert Query

      Query WITH cpu_incremental AS ( SELECT upt_asset_id, upt_hostname, upt_time, cpu_idle - LAG(cpu_idle) OVER (PARTITION BY upt_asset_id ORDER BY upt_time) AS cpu_idle, cpu_total - LAG(cpu_total) OVER (PARTITION BY upt_asset_id ORDER BY upt_time) AS ...

    • Disk Usage Query (Linux and Darwin)

      This article includes the query used to find out the disk usage of a mount point for a particular host: Query SELECT m.upt_asset_id,  m.upt_time,  m.path,  Round(( ( m.blocks - m.blocks_available ) * m.blocks_size * 10e-10 ), 2)  AS  used_gigs  FROM ...

    • Query to find the Process Tree

      Query WITH pstree AS (SELECT 0 AS LEVEL, pid, name, parent, Cast(pid AS TEXT) AS ppid, name AS pparent FROM processes WHERE parent = 0 UNION ALL SELECT LEVEL + 1, t.pid, t.name, t.parent, pstree.ppid || ', ' || Cast(t.pid AS TEXT), pstree.pparent || ...