What is the best way to search for patterns in username field values?

[moshiro]

Need help with searching for patterns in username field values...

I want to know if anyone has suggestions for the best way to search for a pattern in a username field. Do I use subsearches or a join or anything else? The username field is a concatenation of the first_name and last_name fields and then 4 digits are added to the end.

Examples:

first_name          last_name          username
rina                Goletz             rigol9911
dannie              Parden             dannparde1234

I was thinking of extracting first two digits of first_name and first_name fields then searching for:

 | search username="first_name_first_two\.*last_name_first_two.\*\d\d\d\d"

What would be the best way to accomplish that search?

[DalJeanis]

it's probably going to be using match or like, rather than =.. not sure of the exact escaping, but you should make your .* lazy with a ?.

| where match(username,"first_name_first_two\.*?last_name_first_two.\*?\d{4}"

[woodcock]

Like this:

... | where match(username, "(?i)^" . substr(first_name,1, 2) . ".*" . substr(last_name,1, 2) . ".*\d{4}$")

[maciep]

so are there any rules to building the username or is it just whatever the admin felt like concatenating from the first and last names at the time? Anyway, something like this maybe?

your search | eval search_string = "(?i)" . substr(first_name,1,2) . ".*" . substr(last_name,1,2) . ".*\d{4}$" | where match(username,search_string)

[mrgibbon]

This rex should point you in the right direction:

\s{1}\w+\d{4}(?P<username>[^ ]+)