1

Need a query to be able be able to list the fields in the table that have text "mouse" and what that count is, for example:

col_1    col_2    col_3
dog      cat      mouse
cat      cat      mouse
mouse    bird     dog

Result for "mouse"

col_3   2
col_1   1

The practical application of this is to determine what fields in the Windows SecurityEvents log will have a computer name in them. As Azure Sentinel logs are not normalized there is no expectation to always find a computer name in the same place across all event types.

As an alternative we've had to feed event exports to a python script, this works just fine but doing this within KQL would certainly make things easier.

Can't find any alternatives.

Yoni L.
  • 22,627
  • 2
  • 29
  • 48
JLospinoso
  • 11
  • 1

1 Answers1

0

Can you try out the below KQL ?

issue | project col1, col2 , col3

| summarize col1 = countif(col1 == "mouse"),

        col2 = countif(col2 == 'mouse'),

        col3 = countif(col3 == 'mouse')

| extend Temp = bag_pack("Col1",col1,"Col2", col2 , "Col3", col3)

| mv-expand kind= array Temp

| project Temp[0] , Temp1

/////////////

Local testing output from my end:

enter image description here

Shiva Patpi
  • 197
  • 5
  • This works but becomes impractical for a large or unknown number of fields. Because Azure Sentinel does not normalize messages, you just don't know what field a user ID, workstation name, or any other bit of data will be in. Our goal was to be able to use some syntax to determine what fields might contain a workstation name for each different message type. For example, in our case, the SecurityEvents log has a workstation name in 11 different fields across 12 unique event IDs, but there are 226 net fields across these event types. Taking an export into a Python script is able to tackle that. – JLospinoso May 03 '23 at 13:19