SQL query to fetch all the details of assets

SQL query to fetch all the details of assets

SQL query to fetch the instance id, tags ,last activity ,OS, OS version ,Last enrolled ,OSQ version, Gateway IP ,Interface Name ,IP ,asset group

select ua.host_name as Hostname,
       ua.id as InstanceID,
       ua.tags as TAGS,
       ua.last_activity_at as LastActivity,
       ua.os as OperatingSystem,
       ua.os_version as OSVersion,
       ua.last_enrolled_at as LastEnrolled,
       ua.osquery_version as OSQVersion,
       ua.gateway as "GatewayIP",
       uai.name as InterfaceName,
       uai.ip as InterfaceIP ,
        (select name from upt_asset_groups  where  id=ua.upt_asset_group_id) as asset_group
from upt_assets ua,
        upt_asset_interfaces uai
where ua.host_name = uai.upt_hostname and ua.live=true

    • Related Articles

    • Query to get Installed date of apps on apps table

      Following query can be used to get the installed date of apps The installed date of apps on 'apps' table will be available from 5.10.x Uptycs Osquery release. select path, datetime(date_added_time,'unixepoch') as app_installed_time from apps Here ...

    • Sql query to find the asset-activity within the given time stamps

      please find the below query for finding the list of commands executed along with the user and cmd line and key attributes you can add multiple tables based on your needs ,in this query added only 5 tables which are mostly used you can also modify the ...

    • SQL Query to get list of S3 buckets exposed to internet

      WITH nested_acl_grants as ( select *, contains( transform( CAST(acl_grants AS ARRAY < JSON >), a -> json_extract_scalar(a, '$.Grantee.URI') in ( 'http://acs.amazonaws.com/groups/global/AllUsers', ...

    • Queries in Uptycs Global Investigation Page from Detection and Alerts table

      Leveraging SQL queries within the Global Investigation page allows users to extract precise insights regarding detections. Below, we've compiled a set of SQL queries tailored to fulfill various investigative needs: 1. Query for Total Assets by ...

    • Adding external or additional data into alerts and detections

      There might be a need to add extra data into alerts and detections ( e.g. External TIcket ID ). To do this in UI: Open Alerts or Detections page. Select the alert, click on the "Manage" icon and add the data into the "Additional Details" field. To ...