Partial lookup and case using watchlist

[LukeI91]

Hi,

I'm new to KQL and I cannot wrap my head around this. Maybe I have the wrong approach.

We have prefixes in our device names which contains the office location of the device. So xxBE*, would be the Belgium office, xxES* would be the Spain office etc.

I'm now trying to make a KQL query where the office name gets appended to the DeviceLogonEvents. I have been able to do that without watchlist using the case operator

| extend Office = case(DeviceName contains "hu", "Hungary", DeviceName contains "jp"...

but this gets really long, really quickly.

I'm wondering if there is an easier way to do this using watchlist, but I cannot figure it out.

I currently have a watchlist with 2 columns, the prefixes and the office names. From what I'm reading online, I need to do a join or a lookup. I can't do a join currently, because I only have the prefixes and not the full device names. And I have absolutely no idea how to do the lookup.

Can anyone nudge me on the right track? Is watchlists even the way to do this?

[#2]

@LukeI91 Can you extract the prefixes out of the DeviceName using something like

| extract prefix = substring(DeviceName,0,4)

and then do a join on that and the data you have in the watchlist?   Are all the prefixes the same length?

[#3]

Hi Gary,

That is a great suggestion, thank you!
There is only 1 prefix that is 1 character longer but for now I can use your trick twice. Thanks a lot!