Tag vs Rawtag from database table IA_DEVICES in IDSP

Products

• Symantec Identity Security Platform - IDSP (formerly VIP Authentication Hub)

Issue/Introduction

Running IDSP, what are the differences between TAG and RAWTAG fields from the database table IA_DEVICES?

Resolution

Here are the definitions:

TAG:

•   Hashed device raw tag used for all database operations;
•   Previously it was only internal, now it is exposed in APIs to retrieve device details for the users.

RAWTAG:

•   Original tag value is stored in a browser cookie;
•   Added to the database to connect flows between devices -> events or in similar ways;
•   For example: based on this raw tag we get the last used event location and other details.

TAG (IA_EVENT):

• This is the tag that is generated for every new device transaction.
• There may not be a corresponding transaction yet in IA_DEVICES.
• The same value is stored in RAW_TAG in IA_DEVICES when we do a post-risk evaluation with this tag and mark the device as a known device.

To identify shared devices at the tenant and application level, and the usage of those devices, use the following SQL:

1. App, user (with login), and list of all known devices per user:
   
   For the Oracle database:
   
   SELECT ev.APPLICATION AS app_name,
         u.USER_LOGIN AS user_login,
         e.ENT_NAME AS user_id,
         e.ENT_ID,
         COUNT(d.DEVICE_ID) AS device_count,
         LISTAGG(d.DEVICE_ID, ',') WITHIN GROUP (ORDER BY d.DEVICE_ID) AS device_ids,
         LISTAGG(d.RAWTAG, ',') WITHIN GROUP (ORDER BY d.DEVICE_ID) AS device_raw_tags
   FROM (
      SELECT DISTINCT APPLICATION, ENT_ID
      FROM IA_EVENT
      WHERE APPLICATION IS NOT NULL AND TRIM(APPLICATION) IS NOT NULL
   ) ev
   JOIN IA_ENTITY e ON e.ENT_ID = ev.ENT_ID
   LEFT JOIN T_USER u ON u.USER_ID = e.ENT_NAME
   LEFT JOIN IA_DEVICES d ON d.ENT_ID = e.ENT_ID AND d.DELETED = 0
   GROUP BY ev.APPLICATION, u.USER_LOGIN, e.ENT_NAME, e.ENT_ID
   ORDER BY ev.APPLICATION, u.USER_LOGIN;
   
   
2. Tenant, user (with login), and list of known devices (only users with devices):
   
     SELECT e.ENTP_ID AS tenant_id,
            u.USER_LOGIN AS user_login,
            e.ENT_NAME AS user_id,
            e.ENT_ID,
            COUNT(d.DEVICE_ID) AS device_count,
            GROUP_CONCAT(d.DEVICE_ID ORDER BY d.DEVICE_ID) AS device_ids,
            GROUP_CONCAT(d.RAWTAG ORDER BY d.DEVICE_ID) AS device_raw_tags
     FROM IA_ENTITY e
     LEFT JOIN T_USER u ON u.USER_ID = e.ENT_NAME
     INNER JOIN IA_DEVICES d ON d.ENT_ID = e.ENT_ID AND d.DELETED = 0
     GROUP BY e.ENTP_ID, u.USER_LOGIN, e.ENT_NAME, e.ENT_ID
     ORDER BY e.ENTP_ID, u.USER_LOGIN;
   
   
3. Tenant, user (with login), and list of all known devices per user:
   
     SELECT e.ENTP_ID AS tenant_id,
            u.USER_LOGIN AS user_login,
            e.ENT_NAME AS user_id,
            e.ENT_ID,
            COUNT(d.DEVICE_ID) AS device_count,
            GROUP_CONCAT(d.DEVICE_ID ORDER BY d.DEVICE_ID) AS device_ids,
            GROUP_CONCAT(d.RAWTAG ORDER BY d.DEVICE_ID) AS device_raw_tags,
            GROUP_CONCAT(d.TAG ORDER BY d.DEVICE_ID) AS device_hashed_tags
     FROM IA_ENTITY e
     LEFT JOIN T_USER u ON u.USER_ID = e.ENT_NAME
     LEFT JOIN IA_DEVICES d ON d.ENT_ID = e.ENT_ID AND d.DELETED = 0
     GROUP BY e.ENTP_ID, u.USER_LOGIN, e.ENT_NAME, e.ENT_ID
     ORDER BY e.ENTP_ID, u.USER_LOGIN;
   
   
4. Device count per tenant (aggregate across all users)
   
     SELECT e.ENTP_ID AS tenant_id,
            COUNT(DISTINCT e.ENT_ID) AS user_count,
            COUNT(d.DEVICE_ID) AS device_count
     FROM IA_ENTITY e
     LEFT JOIN IA_DEVICES d ON d.ENT_ID = e.ENT_ID AND d.DELETED = 0
     GROUP BY e.ENTP_ID
     ORDER BY device_count DESC;