@defensivedepth I got the JSON file via curl but how could I parse this? Today, I'm using fleet's osquery. It doesn't seem to support dissecting JSON. Do I miss something or do I've to take kolides launcher, as proposed by @seph?

Got it. We have to use json_each and json_extract functions from sqlite to parse the JSON. This is how my JSON looks like:

{
  "whitelist": [
    {
      "name": "Adobe Acrobat DC (64-bit)",
      "publisher": "Adobe",
      "version": "22.001.20117"
    },
    {
      "name": "Fleet osquery",
      "publisher": "Fleet Device Management (<http://fleetdm.com|fleetdm.com>)",
      "version": "0.5.0"
    }
  ]
}

And this is the corresponding query:

SELECT json_extract( value, '$.name' ) AS name, json_extract( value, '$.publisher' ) AS publisher, json_extract( value, '$.version' ) AS version FROM curl, json_each(curl.result, '$.whitelist') WHERE URL = "https://..."

Great! Thank you all.

@Keith Swagler @Jason

osquery neither has a mysql client built in, nor does it have a native mechanism to block a software installation.

P.S. The results can now easily filtered by list or installed version compared to defined version according to mode stated in JSON file.

Thanks @seph for sharing your thoughts. I was able to simplify the query I came up with yesterday. Again, I would like to share the results with the community. Black-, gray- and whitelist of software products are defined in a single JSON file (dsl = defined software list). Here is how it looks like: it supports different lists, allows to use patterns and to define how versions are compared.

{
  "dsl": [
    {
      "name": "Foxit %",
      "publisher": "%",
      "version": "%",
      "mode": "pattern",
      "list": "blacklist",
      "description": ""
    },
    {
      "name": "Cisco Webex %",
      "publisher": "%",
      "version": "%",
      "mode": "pattern",
      "list": "graylist",
      "description": ""
    },
    {
      "name": "7-Zip %",
      "publisher": "Igor Pavlov",
      "version": "19.00.00.0",
      "mode": "pattern",
      "list": "whitelist",
      "description": ""
    },
    {
      "name": "Adobe Acrobat DC (64-bit)",
      "publisher": "Adobe",
      "version": "22.001.20117",
      "mode": "ge",
      "list": "whitelist",
      "description": ""
    }
  ]
}

And this is the corresponding query filtered for records not being fully whitelisted according to definition in JSON file. When you copy and paste this, please adjust URL to JSON file.

WITH
  patterns AS (
    SELECT json_extract( value, '$.name' ) AS name,
           json_extract( value, '$.publisher' ) AS publisher,
	       json_extract( value, '$.version' ) AS version,
           json_extract( value, '$.mode' ) AS mode,
		   json_extract( value, '$.description' ) AS description,
           json_extract( value, '$.list' ) AS list
      FROM curl,
	       json_each(curl.result, '$.dsl')
	  WHERE URL = "https://..."
	  AND mode = "pattern"
  ),
  records AS ( 
    SELECT json_extract( value, '$.name' ) AS name,
	       json_extract( value, '$.publisher' ) AS publisher,
		   json_extract( value, '$.version' ) AS version,
		   json_extract( value, '$.mode' ) AS mode,
   		   json_extract( value, '$.description' ) AS description,
		   json_extract( value, '$.list' ) AS list
    FROM curl,
	     json_each(curl.result, '$.dsl')
	WHERE URL = "https://..."
	AND mode != "pattern"
  )
SELECT p.name,
       p.publisher,
       p.version,
       p.install_date,
	   COALESCE( patterns.name, records.name ) AS l_name,
	   COALESCE( patterns.publisher, records.publisher ) AS l_publisher,
	   COALESCE( patterns.version, records.version ) AS l_version,
	   COALESCE( patterns.mode, records.mode ) AS l_mode,
       COALESCE( patterns.description, records.description ) AS l_description,
	   COALESCE( patterns.list, records.list ) AS l_list
FROM programs p
LEFT JOIN patterns
  ON p.name LIKE patterns.name
 AND p.publisher LIKE patterns.publisher
 AND p.version LIKE patterns.version
LEFT JOIN records
  ON p.name = records.name
 AND p.publisher = records.publisher
WHERE
  l_list IS NULL
  OR l_list IN ( "graylist", "blacklist" )
  OR (
    l_list = "whitelist"
	AND (
      ( l_mode = 'eq' AND p.version != l_version )
      OR
      ( l_mode = 'ge' AND p.version < l_version )
    )
  )

I'm not 100% satisfied yet, but its a good enough 1.0. @All : let me know your thoughts. :)