Error in the Query field - Field must contain valid SQL to be run against osquery tables

[s-frostick]

Hello,

I'm trying to add the following query

SELECT
    eventid,
    JSON_EXTRACT(data, '$.EventData.CommandLine') AS cmdline,
    JSON_EXTRACT(data, '$.EventData.NewProcessId') AS pid,
    JSON_EXTRACT(data, '$.EventData.ProcessId') AS parent,
    JSON_EXTRACT(data, '$.EventData.ParentProcessName') AS parent_path,
    JSON_EXTRACT(data, '$.EventData.NewProcessName') AS path,
    JSON_EXTRACT(data, '$.EventData.SubjectLogonId') AS uid,
    JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS username,
    JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS domain,
    REPLACE(JSON_EXTRACT(data, '$.EventData.NewProcessName'), (SELECT REGEX_SPLIT(JSON_EXTRACT(data, '$.EventData.NewProcessName'), "[\.\w-]+$", 0)), '' ) AS name,
    REPLACE(JSON_EXTRACT(data, '$.EventData.ParentProcessName'), (SELECT REGEX_SPLIT(JSON_EXTRACT(data, '$.EventData.ParentProcessName'), "[\.\w-]+$", 0)), '' ) AS parent_name
FROM windows_events
WHERE eventid = 4688;

but always get the message "Error in the Query field - Field must contain valid SQL to be run against osquery tables".

To try and diagnose this i followed the validation method at

doorman/doorman/utils.py

Line 270 in 9a9b97c

def validate_osquery_query(query):

. Created a new sqlite3 database and created the tables from the osquery_schema.

 
sqlite3 test.db
 /tmp $ sqlite3 test.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .read osquery_schema.sql 

where osquery_schema.sql is https://raw.githubusercontent.com/mwielgoszewski/doorman/master/doorman/resources/osquery_schema.sql.

I then added my query from above to a query.sql file and ran

SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .read query.sql 
Error: near line 1: no such function: REGEX_SPLIT
sqlite>  

The regex_split function is described here https://osquery.readthedocs.io/en/3.2.1/introduction/sql/ but is not part of the SQLite syntax which is why i think the failure is occurring.