zhong
08/29/2022, 8:25 PMconstraint 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!Kathy Satterlee
08/29/2022, 8:57 PMzhong
08/29/2022, 8:59 PMSELECT 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";
Kathy Satterlee
08/29/2022, 9:00 PMzhong
08/29/2022, 9:03 PMno such column: source
Kathy Satterlee
08/29/2022, 9:03 PMeventid
. It could be that it isn't getting parsed properly as an integer.zhong
08/29/2022, 9:49 PMconstraint failed
😓Kathy Satterlee
08/29/2022, 9:51 PMSELECT datetime, computer_name from windows_eventlog where eventid = 4688 and channel = 'Security'
zhong
08/29/2022, 9:54 PMSELECT 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 resultsKathy Satterlee
08/29/2022, 9:56 PMzhong
08/29/2022, 9:56 PMKathy Satterlee
09/01/2022, 10:13 PMzhong
09/01/2022, 10:23 PMor
resets the constraints set before so in the end, i had it working with:
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 constraintsKathy Satterlee
09/02/2022, 2:17 AM