XQL to get characters from Host Name

[Brad.Herbert]

Hello All:

Our host_names are formatted the same across our fleet.  I'd like to pull out the 5-8 characters in the hostname.  We've tried using trim, ltrim and rtrim, and even with them nested.  Any suggestions?

In this example WX260920162Q2R

we want to pull out 0920.

Thanks!

[bbucao · Accepted solution]

Hi Brad.Herbert,

If I understand correctly you are wanting to extract these 5-8 characters from the hostname to populate a new field? If that is the case, you can use the arrayindex and regextract functions together to accomplish this. The regextract function takes a string (in this case the endpoint_name field) followed by a regex to match on the characters you are wanting to extract. In my example below, "WX(\w{5,8})" is the regex, where the function will look for WX followed by 5-8 word characters (letters or numbers), and will only capture the pattern that is contained in the parenthesis (the 5-8 word characters that follow "WX"). You could also specify a non-capturing pattern that has to match at the end of the regex as well by including it outside of the closing parenthesis and inside the closing double quotation, such as "WX(\w{5,8})YZ" if you want to capture the 5-8 word characters ONLY when they are preceded by "WX" and followed by "YZ". 
The regextract function will return an array of all of the patterns that it matched on, and in this case it may be fine to use this function on its own since it is unlikely to have multiple pattern matches from a single endpoint name, but this function is commonly used in combination with the arrayindex function, which accepts an array (in this case it is the output from our regextract function) and a specified array position, and returns the corresponding value. Arrays are 0-based, meaning when we specify an array position of 0, we are telling the function to return the first value in the array.


| alter new_field = arrayindex(regextract(endpoint_name, "WX(\w{5,8})"), 0)

Regards,
Ben

[Brad.Herbert]

Thanks Ben, we ended up going this route shortly after I posed the question.  Thanks for the response!