Partial lookup and case using watchlist

%3CLINGO-SUB%20id%3D%22lingo-sub-2439620%22%20slang%3D%22en-US%22%3EPartial%20lookup%20and%20case%20using%20watchlist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2439620%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20to%20KQL%20and%20I%20cannot%20wrap%20my%20head%20around%20this.%20Maybe%20I%20have%20the%20wrong%20approach.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20have%20prefixes%20in%20our%20device%20names%20which%20contains%20the%20office%20location%20of%20the%20device.%20So%20xxBE*%2C%20would%20be%20the%20Belgium%20office%2C%20xxES*%20would%20be%20the%20Spain%20office%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20now%20trying%20to%20make%20a%20KQL%20query%20where%20the%20office%20name%20gets%20appended%20to%20the%26nbsp%3BDeviceLogonEvents.%20I%20have%20been%20able%20to%20do%20that%20without%20watchlist%20using%20the%20case%20operator%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20extend%20Office%20%3D%20case(DeviceName%20contains%20%22hu%22%2C%20%22Hungary%22%2C%20DeviceName%20contains%20%22jp%22...%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ebut%20this%20gets%20really%20long%2C%20really%20quickly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20wondering%20if%20there%20is%20an%20easier%20way%20to%20do%20this%20using%20watchlist%2C%20but%20I%20cannot%20figure%20it%20out.%3C%2FP%3E%3CP%3EI%20currently%20have%20a%20watchlist%26nbsp%3Bwith%202%20columns%2C%20the%20prefixes%20and%20the%20office%20names.%20From%20what%20I'm%20reading%20online%2C%20I%20need%20to%20do%20a%20join%20or%20a%20lookup.%20I%20can't%20do%20a%20join%20currently%2C%20because%20I%20only%20have%20the%20prefixes%20and%20not%20the%20full%20device%20names.%20And%20I%20have%20absolutely%20no%20idea%20how%20to%20do%20the%20lookup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20nudge%20me%20on%20the%20right%20track%3F%20Is%20watchlists%20even%20the%20way%20to%20do%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440703%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20lookup%20and%20case%20using%20watchlist%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1051828%22%20target%3D%22_blank%22%3E%40LukeI91%3C%2FA%3E%26nbsp%3BCan%20you%20extract%20the%20prefixes%20out%20of%20the%20DeviceName%20using%20something%20like%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20extract%20prefix%20%3D%20substring(DeviceName%2C0%2C4)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20do%20a%20join%20on%20that%20and%20the%20data%20you%20have%20in%20the%20watchlist%3F%26nbsp%3B%20%26nbsp%3BAre%20all%20the%20prefixes%20the%20same%20length%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies

@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?

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!