tokcum
06/13/2022, 10:36 AMJason
06/13/2022, 12:17 PMIbra
06/13/2022, 12:22 PMJason
06/13/2022, 12:23 PMdefensivedepth
06/13/2022, 12:54 PMcurl
table to pull down data from a trusted site, parse out the list & use that in a subsequent query.Jason
06/13/2022, 1:05 PMIbra
06/13/2022, 1:07 PMseph
06/13/2022, 3:58 PMtokcum
06/13/2022, 5:19 PMWITH whitelisted_software_1 AS (
SELECT 'Microsoft Visual C++ % Redistributable %' AS pattern
),
whitelisted_publisher_1 AS (
SELECT 'Microsoft Corporation' AS pattern
),
whitelisted_software AS (
SELECT 'Adobe Acrobat DC (64-bit)' AS name, 'Adobe' as publisher, '22.001.20117' AS version, 'ge' AS mode, 'whitelist' AS list
UNION
SELECT 'Fleet osquery' AS name, 'Fleet Device Management (<http://fleetdm.com|fleetdm.com>)' as publisher, '0.5.0' AS version, 'eq' AS mode, 'whitelist' AS list
UNION
SELECT 'Local Administrator Password Solution' AS name, 'Microsoft Corporation' as publisher, '6.2.0.0' AS version, 'eq' AS mode, 'whitelist' AS list
UNION
SELECT 'Notepad++ (64-bit x64)' AS name, 'Notepad++ Team' as publisher, '8.0' AS version, 'ge' AS mode, 'whitelist' AS list
),
graylisted_software AS (
SELECT 'Cisco Webex Meetings' AS name, '0' AS version, 'ge' AS mode, 'graylist' AS list
),
blacklisted_software AS (
SELECT 'Foxit PDF Reader' AS name, '0' AS version, 'ge' AS mode, 'blacklist' AS list
)
SELECT p.name,
p.publisher,
p.version,
p.install_date,
COALESCE( black.list, gray.list, white.list ) AS list
FROM programs p,
whitelisted_software_1,
whitelisted_publisher_1
LEFT JOIN whitelisted_software white
ON p.name = white.name
AND p.publisher = white.publisher
LEFT JOIN graylisted_software gray
ON p.name = gray.name
LEFT JOIN blacklisted_software black
ON p.name = black.name
WHERE
p.name NOT LIKE whitelisted_software_1.pattern /* Software is whitelisted by pattern */
AND
p.publisher NOT LIKE whitelisted_publisher_1.pattern /* Publisher is whitelisted by pattern */
AND (
white.list IS NULL /* Software is not whitelisted */
OR white.list IS NOT NULL /* Software is whitelisted but version does not comply */
AND (
( white.mode = 'eq' AND p.version != white.version )
OR
( white.mode = 'ge' AND p.version < white.version )
)
OR gray.list IS NOT NULL /* Software is graylisted */
AND (
( gray.mode = 'eq' AND p.version = gray.version )
OR
( gray.mode = 'ge' AND p.version >= gray.version )
)
OR black.list IS NOT NULL /* Software is blacklisted */
AND (
( black.mode = 'eq' AND p.version = black.version )
OR
( black.mode = 'ge' AND p.version >= black.version )
)
)
I'll try the curl approach starting tomorrow to be able to define the list externally to the query.{
"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.seph
06/13/2022, 9:39 PMkolide_json
is for reading json files from disk. To parse a curl response, json extract seems pretty good.Ibra
06/14/2022, 1:59 PMseph
06/14/2022, 2:00 PMtokcum
06/14/2022, 3:31 PMIbra
06/14/2022, 3:42 PMseph
06/14/2022, 4:25 PMmode
field, and extend the dsl to have a pattern field, and a ge field, and whatever else.tokcum
06/14/2022, 5:38 PM{
"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. :)