KQL Query for Syslog table doesn't give the correct information

Question

This KQL query shown below does not display all of the Blocked utmevents shown under utmaction. I have to add a line - where SyslogMessage contains "blocked" and specify the computer name. The results for the KQL query below doesn't show the correct blocked utmevents in the utmaction column.

Syslog
| where SyslogMessage contains "utmevent"
| parse SyslogMessage with * "utmaction=" UTMACTION"utmevent"*
| parse SyslogMessage with * "utmevent=" UTMEVENT"threat"*
| parse SyslogMessage with * "threat=" Catagory"service"*
| parse SyslogMessage with * "url=" URL" "*
| parse SyslogMessage with * "user=" User"@"*
| parse SyslogMessage with * "os=" OS"("*
| parse SyslogMessage with * "(" version")"*
| project TimeGenerated, HostName, HostIP, User, SeverityLevel, UTMACTION, UTMEVENT, Catagory, URL, OS, version

Answer 1

@Claudia Morfin It sounds like you are experiencing an issue with the KQL query not displaying all of the blocked utmevents shown under utmaction. Adding a line to filter for "blocked" and specifying the computer name is a good workaround, but it may not be the most efficient solution.

One possible reason why the KQL query is not displaying all of the blocked utmevents could be due to the parsing of the SyslogMessage field. It's possible that some of the blocked utmevents are not being parsed correctly, which is causing them to be excluded from the results.

To troubleshoot this issue, you can try modifying the parsing logic to ensure that all of the relevant fields are being extracted correctly. You can also try using the "extract" operator instead of "parse" to see if that makes a difference.

Here's an example of how you can modify the parsing logic to extract the UTMACTION field and filter for "blocked":

Syslog

| where SyslogMessage contains "utmevent" and SyslogMessage contains "blocked" and Computer == "<computer name>"

| extend UTMACTION = extract("utmaction=(?<UTMACTION>[^ ]+)", 1, SyslogMessage)

| extend UTMEVENT = extract("utmevent=(?<UTMEVENT>[^ ]+)", 1, SyslogMessage)

| extend Catagory = extract("threat=(?<Catagory>[^ ]+)", 1, SyslogMessage)

| extend URL = extract("url=(?<URL>[^ ]+)", 1, SyslogMessage)

| extend User = extract("user=(?<User>[^ ]+)", 1, SyslogMessage)

| extend OS = extract("os=(?<OS>[^ ]+)", 1, SyslogMessage)

| extend version = extract("\\((?<version>[^\\)]+)\\)", 1, SyslogMessage)

| project TimeGenerated, HostName, HostIP, User, SeverityLevel, UTMACTION, UTMEVENT, Catagory, URL, OS, version

This query filters for "blocked" utmevents and the specified computer name, and then uses the "extract" operator to extract the relevant fields from the SyslogMessage field. This should ensure that all of the blocked utmevents are included in the results.

Remember, troubleshooting KQL queries can sometimes be a process of trial and error. Adjusting your query incrementally and testing each change can help you pinpoint the issue more effectively.

Please let me know if you need further assistance or have more questions.

---

If the response helped, do "Accept Answer" and up-vote it