How to remove wild cards/suffix values from values of hostname?

[srv007]

below is my search query

index="inm_inventory"
|table inventory_date, region, vm_name, version
|dedup vm_name | search vm_name="*old*" OR vm_name="*restore*"

output as below :

The challenge here is each vm_name has different suffix added and its not standard since any user adds any comment to to it so it could be anything. how do i perform look for the vm names since lookup file only has hostnames and no suffix.

i have a lookup file named itso.csv which has details like hostname(all in lower case), tier, owner, country. I want to use lookup in my main search for the fields tier, owner, country

end requirement is to do lookup for the vm_name in itso.csv file and add details like tier, countrycode, owner in the main search output.

[ITWhisperer]

| rex field=vm_name "^(?<real_vm_name>[a-z0-9]+)"
| lookup itso.csv hostname as real_vm_name

[srv007]

This did help actually.
few hosts have entries like ###gbl12344 - old###
anyway to fetch only hostname for such cases?

[yuanliu]

Given that no real hostname begins with pound sign, you can use ltrim.

index="inm_inventory"
|dedup vm_name | search vm_name="*old*" OR vm_name="*restore*"
| eval vm_name = ltrim(mvindex(split(vm_name, " "), 0), "#")
|table inventory_date, region, vm_name, version

ltrim and split use fixed patterns,  therefore less compute and RAM.

[ITWhisperer · Accepted solution]

| rex field=vm_name "^#*(?<real_vm_name>[a-z0-9]+)"

[yuanliu]

I suspect that people got confused by the field name "vm_name".  From your description, this is a free-text field that users can add additional string after the real hostname.  Dealing with human input can be challenging.  It will depend on how careful the user is, what kind of separator they use after hostname, and what characteristics the hostname string may have.

Take the simplest case where the user always added a white space before entering their comments (and that hostname itself doesn't contain white space, and that there is no leading whitespace in the field), you can do

index="inm_inventory"
|dedup vm_name | search vm_name="*old*" OR vm_name="*restore*"
| eval vm_name = mvindex(split(vm_name, " "), 0)
|table inventory_date, region, vm_name, version

(I moved table command to last as this can improve performance.  If you want to restrict number of fields carried into dedup command, use fields instead of table.)

[srv007]

Any guidance/help is appreciated 

[srv007]

##Note - Also point to be noted that some hostname can be of 6 characters some can be 8 and like so extracting hostname as number of characters might not be suitable.