Full query example for monitoring shadow IT & app usage across google, microsoft, and an IdP such as Okta

shadowIT.sql

WITH allAzureLogs AS (
    SELECT
        PARSE_JSON(msftauditlog:raw) as parsedEvent
    FROM material_staging.public.trapdoorevent_msftauditlog_
),
interactiveSignInLogs AS (
SELECT
    recordDetails.value:properties:userPrincipalName as srcUser,
    recordDetails.value:properties:appDisplayName as appName,
    recordDetails.value:properties:appId as appId,
    recordDetails.value:operationName as operationName,
    recordDetails.value:category as logCategory,
    recordDetails.value:properties:authenticationProcessingDetails as authDetails,
    -- If you want to check out the full parsed event
    parsedEvent
FROM allAzureLogs a,
    lateral flatten(input => parse_json(a.parsedEvent:val:records)) recordDetails
    WHERE logCategory ilike 'NonInteractiveUserSignInLogs'
),
userOAuthAndScopes AS (
  SELECT
    srcUser,
    appName,
    appId,
    operationName,
    PARSE_JSON(authDetail.value:value) as scope
  FROM interactiveSignInLogs i,
      lateral flatten(input => parse_json(i.authDetails)) authDetail
  WHERE authDetail.value:key ilike '%Oauth Scope%'
),
msftSummary AS (
    SELECT
      COUNT(DISTINCT srcUser) as userCount,
      ARRAY_AGG(DISTINCT srcUser) as users,
      ARRAY_AGG(DISTINCT s.value) as scopeGrants,
      appName
    FROM userOAuthAndScopes, lateral flatten(input => scope) s
    GROUP BY appName
    ORDER BY userCount DESC
),
GoogleStats as (
    SELECT
        acctEmail,
        uAcctId,
        googletokens
    FROM
        material_staging.public.acct
),
GoogleuserTokens as (
    SELECT
        acctEmail,
        uAcctId,
        tokendetails.value:displayText as appName,
        tokendetails.value:scopes as scopes,
        tokendetails.value:clientId as clientId,
        tokendetails.value:nativeApp as nativeApp,
        tokendetails.value:anonymous as anonymous
    FROM
        GoogleStats g,
        lateral flatten(input => parse_json(g.googletokens:items)) tokendetails
),
googleSummary AS (
SELECT
    COUNT(DISTINCT uacctid) as userCount,
    array_unique_agg(acctEmail) as users,
    array_union_agg(scopes) as scopeGrants,
    appName,
    -- Other summary fields
    nativeApp,
    anonymous
FROM
    googleusertokens
    GROUP BY appName, nativeApp, anonymous
    ORDER BY userCount desc
),
allOAuthData AS (
    SELECT
        COALESCE (m.appName, g.appName) as appName,
        -- Need to coalesce since some of these will be null in the join
        COALESCE(m.usercount, 0) + COALESCE(g.usercount, 0) as usercount,
        -- If desired, feel free to use the full user list and scope stats
        m.users as msftUsers,
        g.users as googleUsers,
        array_cat(m.users, g.users) as oauthusers,
        m.scopeGrants as msftScopes,
        g.scopeGrants as googleScopes,
        array_cat(m.scopegrants, g.scopegrants) as scopeGrants
    FROM googleSummary g
    FULL OUTER JOIN msftSummary m ON g.appName = m.appName
    ORDER by userCount desc
)
SELECT
        COALESCE (o.appName, d.appName) as appName,
        -- Need to coalesce since some of these will be null in the join
        COALESCE(ARRAY_SIZE(d.acctEmails), 0) + COALESCE(o.usercount, 0) as totalUserCount,
        ARRAY_SIZE(d.acctEmails) as DirectSignupUserCount,
        d.acctEmails as directSignUpUsers,
        o.usercount as OAuthUserCount,
        array_cat(ifnull(o.msftusers, array_construct()), ifnull(o.googleusers, array_construct())) as oauthUsers,
        array_cat(ifnull(o.msftscopes, array_construct()), ifnull(o.googlescopes, array_construct())) as scopeGrants,
        o.msftUsers,
        o.googleusers,
        msftScopes,
        googleScopes
FROM alloauthdata o
FULL OUTER JOIN appusage.public.DIRECTSIGNUPUSERS d on d.appName = o.appname
ORDER BY totalUserCount desc