WITH
raw_plist AS (
  SELECT *, '/Users/' || SPLIT(path,'/',1) AS directory FROM kolide_plist WHERE path LIKE '/Users/%/Library/Caches/com.apple.appstoreagent/SoftwareMap'),
pointers AS (
  SELECT parent, directory,
    MAX(CASE WHEN key = '_storeExternalVersionID' THEN value END) AS _storeExternalVersionID,
    MAX(CASE WHEN key = '_deviceIdentifierForVendor' THEN value END) AS _deviceIdentifierForVendor,
    MAX(CASE WHEN key = '_storeCohort' THEN value END) AS _storeCohort,
    MAX(CASE WHEN key = '_bundlePath' THEN value END) AS _bundlePath,
    MAX(CASE WHEN key = '_installed' THEN value END) AS _installed,
    MAX(CASE WHEN key = '_vendorName' THEN value END) AS _vendorName,
    MAX(CASE WHEN key = '$class' THEN value END) AS class,
    MAX(CASE WHEN key = '_storefront' THEN value END) AS _storefront,
    MAX(CASE WHEN key = '_localizedName' THEN value END) AS _localizedName,
    MAX(CASE WHEN key = '_bundleID' THEN value END) AS _bundleID,
    MAX(CASE WHEN key = '_shortVersionString' THEN value END) AS _shortVersionString,
    MAX(CASE WHEN key = '_profileValidated' THEN value END) AS _profileValidated,
    MAX(CASE WHEN key = '_storeItemID' THEN value END) AS _storeItemID,
    MAX(CASE WHEN key = '_receiptType' THEN value END) AS _receiptType,
    MAX(CASE WHEN key = '_bundleVersion' THEN value END) AS _bundleVersion
  FROM raw_plist
  GROUP BY parent, path),
pointer_resolve AS (
  SELECT rp.directory AS rp_directory, p.directory AS p_directory,
    MAX(CASE WHEN _storeExternalVersionID = key AND rp.parent = '$objects' THEN value END) AS store_external_version_id,
    MAX(CASE WHEN _deviceIdentifierForVendor = key AND rp.parent = '$objects' THEN value END) AS device_id_for_vendor,
    MAX(CASE WHEN _storeCohort = key AND rp.parent = '$objects' THEN value END) AS store_cohort,
    MAX(CASE WHEN _bundlePath = key AND rp.parent = '$objects' THEN value END) AS bundle_path,
    MAX(_installed) AS installed,
    MAX(CASE WHEN _vendorName = key AND rp.parent = '$objects' THEN value END) AS vendor_name,
    MAX(class) AS class,
    MAX(CASE WHEN _storefront = key AND rp.parent = '$objects' THEN value END) AS store_front,
    MAX(CASE WHEN _localizedName = key AND rp.parent = '$objects' THEN value END) AS localized_name,
    MAX(CASE WHEN _bundleID = key AND rp.parent = '$objects' THEN value END) AS bundle_id,
    MAX(CASE WHEN _shortVersionString = key AND rp.parent = '$objects' THEN value END) AS short_version_string,
    MAX(_profileValidated) AS profile_validated,
    MAX(CASE WHEN _storeItemID = key AND rp.parent = '$objects' THEN value END) AS store_item_id,
    MAX(CASE WHEN _receiptType = key AND rp.parent = '$objects' THEN value END) AS receipt_type,
    MAX(CASE WHEN _bundleVersion = key AND rp.parent = '$objects' THEN value END) AS bundle_version
  FROM pointers p, raw_plist rp
  WHERE rp_directory = p_directory
  GROUP BY p.parent),
human_users AS (
  SELECT username, directory, uid FROM users WHERE SUBSTR(uuid,1,8) != 'FFFFEEEE')
SELECT
  username,
  uid,
  localized_name,
  bundle_id,
  short_version_string,
  bundle_version,
  bundle_path,
  installed,
  store_external_version_id
  device_id_for_vendor,
  store_cohort,
  vendor_name,
  class,
  store_front,
  profile_validated,
  store_item_id,
  receipt_type
FROM pointer_resolve pr 
JOIN human_users hu ON hu.directory = pr.rp_directory 
AND bundle_id NOT NULL;