XQL - 4740 event log - Account Lock out different languages - merging

[RFeyertag]

Hello dear community!

is there a way to merge the german and english variants in XQL? The action_evtlog_message is sometimes german and more often english. But how do I get the results from both in one 

//Account Locked https://github.com/busterix76/Cortex_XDR_XQL_Queries/commit/8a2eee205cb16c1d36599cbdedd8808301652cfa
preset = xdr_event_log
| filter action_evtlog_event_id = 4740
|alter Domain_Name_GER =arrayindex(regextract(action_evtlog_message ,"Kontodomäne:.*?(\w.*)\r\n"),0)
|alter Domain_Name_ENG =arrayindex(regextract(action_evtlog_message ,"Account Domain:.*?(\w.*)\r\n"),0)
| alter Locked_Account_ENG =arrayindex(regextract(action_evtlog_message, "Account That Was Locked Out:\r\n.*\r\n.*Account Name:.*?(\w.*)\r\n"),0)
| alter Locked_Account_GER =arrayindex(regextract(action_evtlog_message, "Gesperrtes Konto:\r\n.*\r\n.*Kontoname:.*?(\w.*)\r\n"),0)
| fields action_evtlog_description,action_evtlog_message ,agent_hostname,agent_ip_addresses , Domain_Name_ENG,Domain_Name_GER, Locked_Account_GER,Locked_Account_ENG

BR

Rob

[micomi]

Hello Rob

We had the same issue. Usually we use coalesce to merge German and English into one field.

Based on your query you could do the following:

|alter Domain_Name = coalesce (Domain_Name_GER, Domain_Name_ENG)

Coalesce takes the first value which is not NULL. But usually only GER or ENG has a value and not both.

Hope this helps. If you find a better solution I'm interested how you solved it.

Michael