https://github.com/osquery/osquery logo
#fleet
Title
# fleet
z

zhong

08/29/2022, 8:25 PM
Hi all, Running into a
constraint failed
error when querying the
windows_eventlog
table from fleet. After some troubleshooting I found that
channel
is a required constraint in the WHERE clause, which I have added but still get the same
constraint failed
error. is there another required constraint when querying
windows_eventlog
? Any help is greatly appreciated!
k

Kathy Satterlee

08/29/2022, 8:57 PM
Hi @zhong ! Can you share the query in question?
z

zhong

08/29/2022, 8:59 PM
Copy code
SELECT datetime, computer_name, json_extract(windows_eventlog.data,'$.EventData.ParentProcessName') as 'Parent' from windows_eventlog where eventid = '4688' and channel = 'Security'
and json_extract(windows_eventlog.data,'$.EventData.ParentProcessName') like "winword.exe"
and json_extract(windows_eventlog.data,'$.EventData.NewProcessName') = "C:\Windows\System32\wbem\WMIC.exe"
or json_extract(windows_eventlog.data,'$.EventData.NewProcessName') like "excel.exe"
or json_extract(windows_eventlog.data,'$.EventData.NewProcessName') like "outlook.exe";
k

Kathy Satterlee

08/29/2022, 9:00 PM
Try ‘source’ rather than ‘channel’.
z

zhong

08/29/2022, 9:03 PM
Returned
no such column: source
k

Kathy Satterlee

08/29/2022, 9:03 PM
I may have checked the wrong table :) one sec.
Reaching out for a hand. bear with me!
Try removing the quotes around
eventid
. It could be that it isn't getting parsed properly as an integer.
z

zhong

08/29/2022, 9:49 PM
still got
constraint failed
😓
k

Kathy Satterlee

08/29/2022, 9:51 PM
Does a simpler query without the json extraction work?
Copy code
SELECT datetime, computer_name from windows_eventlog where eventid = 4688 and channel = 'Security'
z

zhong

08/29/2022, 9:54 PM
yes! just tried running this query
Copy code
SELECT datetime, computer_name, json_extract(windows_eventlog.data,'$.EventData.ParentProcessName') as 'Parent' from windows_eventlog where eventid = 4688 and channel = 'Security';
and was able to get results
k

Kathy Satterlee

08/29/2022, 9:56 PM
Brilliant! You can go from there to start layering the additional clauses on and see what happens!
z

zhong

08/29/2022, 9:56 PM
Thank you!!
k

Kathy Satterlee

09/01/2022, 10:13 PM
@zhong Did you get this sorted? I'd love to know what the culprit was.
z

zhong

09/01/2022, 10:23 PM
yes! error came down to syntax. using
or
resets the constraints set before so in the end, i had it working with:
Copy code
SELECT datetime, computer_name, json_extract(windows_eventlog.data,'$.EventData.ParentProcessName') as 'Parent' from windows_eventlog where eventid = '4688' and channel = 'Security'
and json_extract(windows_eventlog.data,'$.EventData.ParentProcessName') like "winword.exe"
and (json_extract(windows_eventlog.data,'$.EventData.NewProcessName') = "C:\Windows\System32\wbem\WMIC.exe"
or json_extract(windows_eventlog.data,'$.EventData.NewProcessName') like "excel.exe"
or json_extract(windows_eventlog.data,'$.EventData.NewProcessName') like "outlook.exe");
the parenthesis ensures that it keeps the previous constraints
k

Kathy Satterlee

09/02/2022, 2:17 AM
Nicely done! SQL can be a bear.
7 Views