Query filter based on tags

Query filter based on tags

Following are different ways to query for tags assigned using SQL.

Check for key

WHERE Array_position(Map_keys(pe.upt_asset_tags), '<key>') > 0 

    e.g.

    WHERE  Array_position(Map_keys(pe.upt_asset_tags), 'local_asset') > 0


WHERE CAST(JSON_EXTRACT(CAST(upt_asset_tags AS JSON),'$.KEY'))= 'VALUE' LIMIT 1;

    e.g.

    WHERE CAST(JSON_EXTRACT(CAST(upt_asset_tags AS JSON),'$.aws_account'))= 'dev';


Assets with value

WHERE element_at(upt_asset_tags, '<key>') = '<value>' 


Assets with Tag

Note :  upt_assets presents tags in json array format

FROM   upt_assets  WHERE  Json_array_contains(tags, 'asset-group=development')



    • Related Articles

    • Auto tagging using global query

      This article provides details on how to create auto tag rules using a global query. Currently, auto tagging using a global query can be achieved using API only. GUI based solution will be available in release 77. Global query based auto tag rules can ...
    • SQL Query Best Practices

      This document gives some best practices for writing high quality, performance optimized SQL queries. TABLE OF CONTENTS Mandatory Practices Tips and Tricks Mandatory Practices Avoid SELECT * when feasible. Uptycs uses Presto, which is a columnar DB, ...
    • Steps to manually install/uninstall Osquery on Ubuntu based Linux

      Overview This document outlines the procedure on how to install/uninstall osquery on Ubuntu-based Linux. Procedure Installation Download the installer. Copy the Ubuntu installer (osquery-<version>-deb) to the endpoint (to a directory such as /tmp) cp ...
    • Tips for designing Investigate query parameters

      Query parameters allow users to create variables to be used in an individual query and can be referenced multiple times within the query. This feature facilitates writing queries that often refer to changing values the user would otherwise have to ...
    • Guidelines for SQL Alerts Rules / Events Rules

      Consider time it takes for events / data shows up in the back-end. On Uptycs portal, data should be visible on the backend in less than 6 minutes.               Note : 6 min delay is already coded in for :from and :to variables.           ...