SQL TO KQL Conversion (Experimental)
The sql_to_kql
module is a simple converter to KQL based on
mo-sql-parsing. It is an
experimental feature built to help us convert a few queries but we
thought that it was useful enough to include in MSTICPy.
It supports a subset of ANSI SQL-92 which includes the following:
SELECT (including column renaming and functions)
FROM (including from subquery)
WHERE (common string and int operations, LIKE, some common functions)
LIMIT
UNION, UNION ALL
JOIN - only tested for relatively simple join expressions
GROUP BY
ORDER BY
SQL comments are removed from the output but should not break the parser.
It does not support HAVING, multiple SQL statements or anything complex and fancy like Common Table Expressions. It also does not support modification such as INSERT and UPDATE nor any DML.
It does support a few additional Spark SQL extensions like RLIKE.
Caveat Emptor!
This module is included in MSTICPy in the hope that it might be useful to others. We do not intend to expand its capabilities.
It is also not guaranteed to produce perfectly-executing KQL - there will likely be things that you have to fix up in the output query. You will, for example, nearly always need change the names of the fields used since the source data tables are unlikely to exactly match the schema of your Kusto/Azure Sentinel target.
The module does include an elementary table name mapping function that we demonstrate below.
from msticpy.data.sql_to_kql import sql_to_kql
Simple SQL Query
sql = """
SELECT DISTINCT Message, Otherfield
FROM apt29Host
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID BETWEEN 1 AND 10
AND LOWER(ParentImage) LIKE '%explorer.exe'
AND EventID IN ('4', '5', '6')
AND LOWER(Image) LIKE "3aka3%"
LIMIT 10
"""
kql = sql_to_kql(sql)
print(kql)
apt29Host
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID between (1 .. 10)
and tolower(ParentImage) endswith 'explorer.exe'
and EventID in ('4', '5', '6')
and tolower(Image) startswith '3aka3'
| project Message, Otherfield
| distinct Message, Otherfield
| limit 10
SQL Joins
sql="""
SELECT DISTINCT Message, Otherfield, COUNT(DISTINCT EventID)
FROM (SELECT EventID, ParentImage, Image, Message, Otherfield FROM apt29Host) as A
--FROM A
INNER JOIN (Select Message, evt_id FROM MyTable ) on MyTable.Message == A.Message and MyTable.evt_id == A.EventID
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) LIKE "%explorer.exe"
AND LOWER(Image) RLIKE ".*3aka3%"
GROUP BY EventID
ORDER BY Message DESC, Otherfield
LIMIT 10
"""
kql = sql_to_kql(sql)
print(kql)
apt29Host
| project EventID, ParentImage, Image, Message, Otherfield
| join kind=inner (MyTable
| project Message, evt_id) on $right.Message == $left.Message
and $right.evt_id == $left.EventID
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) endswith 'explorer.exe'
and tolower(Image) startswith '.*3aka3'
| summarize any(Message), any(Otherfield), dcount(EventID) by EventID
| order by Message desc, Otherfield
| limit 10
Table Renaming
sql="""
SELECT DISTINCT Message, Otherfield, COUNT(DISTINCT EventID)
FROM (SELECT EventID, ParentImage, Image, Message, Otherfield FROM apt29Host) as A
INNER JOIN (Select Message, evt_id FROM MyTable ) on MyTable.Message == A.Message and MyTable.evt_id == A.EventID
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) LIKE "%explorer.exe"
AND LOWER(Image) RLIKE ".*3aka3%"
GROUP BY EventID
ORDER BY Message DESC, Otherfield
LIMIT 10
"""
table_map = {"apt29Host": "SecurityEvent", "MyTable": "SigninLogs"}
kql = sql_to_kql(sql, table_map)
print(kql)
SecurityEvent
| project EventID, ParentImage, Image, Message, Otherfield
| join kind=inner (SigninLogs
| project Message, evt_id) on $right.Message == $left.Message
and $right.evt_id == $left.EventID
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) endswith 'explorer.exe'
and tolower(Image) startswith '.*3aka3'
| summarize any(Message), any(Otherfield), dcount(EventID) by EventID
| order by Message desc, Otherfield
| limit 10
Join with Aliases
sql="""
SELECT Message
FROM apt29Host a
INNER JOIN (
SELECT ProcessGuid
FROM apt29Host
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) RLIKE '.*partial_string.*'
AND LOWER(Image) LIKE '%cmd.exe'
) b
ON a.ParentProcessGuid = b.ProcessGuid
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(Image) LIKE '%powershell.exe'
"""
kql = sql_to_kql(sql, table_map)
print(kql)
SecurityEvent
| join kind=inner (SecurityEvent
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) matches regex '.*partial.string.*'
and tolower(Image) endswith 'cmd.exe'
| project ProcessGuid) on $left.ParentProcessGuid == $right.ProcessGuid
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(Image) endswith 'powershell.exe'
| project Message
Unions and Group By
sql="""
SELECT DISTINCT Message, COUNT(Otherfield)
FROM (SELECT *
FROM (SELECT EventID, ParentImage, Image, Message, Otherfield FROM apt29Host)
UNION
SELECT DISTINCT Message, Otherfield, EventID
FROM (SELECT EventID, ParentImage, Image, Message, Otherfield FROM apt29Host) as A
INNER JOIN MyTable on MyTable.mssg = A.Message
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) LIKE "%explorer.exe"
AND LOWER(Image) RLIKE ".*3aka3%"
LIMIT 10
)
GROUP BY Message
ORDER BY Message DESC, Otherfield
"""
kql = sql_to_kql(sql, table_map)
print(kql)
SecurityEvent
| project EventID, ParentImage, Image, Message, Otherfield
| union (SecurityEvent
| project EventID, ParentImage, Image, Message, Otherfield
| join kind=inner (SigninLogs) on $right.mssg == $left.Message
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) endswith 'explorer.exe'
and tolower(Image) startswith '.*3aka3'
| project Message, Otherfield, EventID
| distinct Message, Otherfield, EventID
)
| distinct *
| limit 10
| summarize any(Message), count(Otherfield) by Message
| order by Message desc, Otherfield
Aliased and Calculated Select Columns
sql="""
SELECT DISTINCT Message as mssg, COUNT(Otherfield)
FROM (SELECT EventID as ID, ParentImage, Image, Message,
ParentImage + Message as ParentMessage,
LOWER(Otherfield) FROM apt29Host
)
WHERE Channel = "Microsoft-Windows-Sysmon/Operational"
AND EventID = 1
AND LOWER(ParentImage) LIKE "%explorer.exe"
"""
kql = sql_to_kql(sql, table_map)
print(kql)
SecurityEvent
| extend ParentMessage = ParentImage + Message, Otherfield = tolower(Otherfield)
| project ID = EventID, ParentImage, Image, Message, ParentMessage, Otherfield
| where Channel == 'Microsoft-Windows-Sysmon/Operational'
and EventID == 1
and tolower(ParentImage) endswith 'explorer.exe'
| extend Otherfield = count(Otherfield)
| project mssg = Message, Otherfield
| distinct *