2

How can you Manipulate the output of a string in KQL? For example I have a query to find loggedon users for a specific group of devices and this is an output I received. I would only want Username to show in the output.

DeviceInfo
|where  DeviceID== "hksjdfhksdf"
|project DeviceName, LoggedOnUsers

[{"UserName":"djlskjfdl","DomainName":"kfjgldkjfg","Sid":"jldfkgjfd2"}]

John418
  • 33
  • 1
  • 6
  • Hi John, if the answer below helped, please accept it (see [this](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work?answertab=active#tab-top) to know why and how). If the answer doesn't help, please add a comment specifying what's not working/missing, and I'll assist. Thanks! – Slavik N Aug 02 '21 at 04:20
  • The query returns 2 fields (DeviceName, LoggedOnUsers) while the presented result contain only a single field (array) – David דודו Markovitz Feb 25 '22 at 13:20

1 Answers1

2

If your column is of type dynamic, then you can simply extract the first element in the array, and then extract the value of the UserName key, like this:

let str = dynamic([{"UserName":"djlskjfdl","DomainName":"kfjgldkjfg","Sid":"jldfkgjfd2"}]);
print str[0].UserName

Output:

print_0
djlskjfdl

If your column is of type string, you can make it dynamic by using todynamic().

Slavik N
  • 4,705
  • 17
  • 23
  • Still not working for me but thank you for the help. – John418 Jul 27 '21 at 20:22
  • Please provide a sample of what's not working, and I'll help. – Slavik N Jul 27 '21 at 20:48
  • This is my query: DeviceInfo | where RegistryDeviceTag == "Standard" | extend AllProperties = todynamic(LoggedOnUsers) | project DeviceName, Users = AllProperties["Username"] Im receiving an output of DeviceName(Correct) but I'm not receiving anything for the Users. I tried using parse_json but after researching I found that it doesn't work correctly with json arrays. – John418 Jul 28 '21 at 12:48
  • What's the type of the `LoggedOnUsers` column? You can get it by running `DeviceInfo | project LoggedOnUsers | getschema`. – Slavik N Jul 29 '21 at 05:45