Extract string to use for lookup

[malmiran]

Need to do a lookup using the hostname field from my events data and an asset name from my asset/cmdb data. However, the hostname would be something like Hostname="NTORGDBPXXX" while the asset would be AssetName="NTORGDBPXXX\MXXXCPRD01". So I simply need to extract the string until the backslash "\" (i.e. NTORGDBPXXX) so I can do a lookup using the Hostname=Assetname pair.

[horsefez · Accepted solution]

@malmiran,

this should be your solution.

| rex field=AssetName "AssetName=\"(?<hostname>[^\\\]+)"

[DalJeanis]

@malmiran -

@pyro_wood has given you what you need to extract the Hostname value from the AssetName field.

If you need more help with the structure - how to achieve the best match - please let us know. There are at least six different ways to bump that data together, depending on whether you meant there was already a lookup table for assets, or whether the assets are in a different index or the same index.

If you have what you need, then please accept the answer that solved your issue. You can also feel free to upvote any comment or answer that you found helpful.

[malmiran]

Thanks Pyro!

Hi Pyro/Daj,

I just had a chance to try the rex command you gave and it worked in extracting the hostname part of the asset name (i.e. string before the backslash). However, you're right, I seem to still need help with how exactly to integrate in some sort of "join" search to get fields from both my main events data (these are SCOM alerts actually) found on one index, and data from my assets lookup table (it's actually a "lookup definition" - don't know if that matters).

I've been playing around with using lookup and join but can't seem to get it to work. I simply can't figure out where in the search sequence do I place the Regex above. I'd appreciate any advice! 🙂

[malmiran]

Just to add...

Here's my search:
sourcetype="scom::alertlog" Hostname="NTHOSTSQL01" | lookup "asset-list" NAME as Hostname OUTPUT STATUS, SYSTEM_ROLE | table Hostname, Name, STATUS, SYSTEM_ROLE | RENAME Name AS "Alert Event"

Notes:
- The STATUS and SYSTEM_ROLE are those that I want to pull from the "asset-list" lookup definition.
- The problem is that sometimes the value in the NAME field of the "asset-list" would be in the format HOST\INSTANCE e.g. (NTHOSTSQL01\INSTANCE01), while the value of the Hostname that matches the record in my events data (scome:alertlog) only has NTHOSTSQL01.
- I can extract the Hostname portion in the NAME field using: rex field=NAME "(?[^\]+)". However, I don't know how to incorporate it in my lookup statement.

[malmiran]

I've been pulling my hair out with this, but after 3 cups of coffee, I think I finally solved it.

I used JOIN and switched the sequence of the search. Instead of searching the scom:alertlog first and using the "asset-list" in the lookup (where I needed to apply the REX), I searched the asset-list first, used REX so that the result at that stage already contains the substring, then did the lookup on teh scom:alertlog.

| inputlookup "asset-list" | table NAME, STATUS, SYSTEM_ROLE | rex field=NAME "(?[^\]+)" | JOIN type=inner NAME [SEARCH sourcetype="scom:alertlog" | RENAME Hostname AS NAME] | table NAME, STATUS, SYSTEM_ROLE, Name, Description | RENAME Name as "Alert Name"

(I guess my journey with Splunk has just started) 🙂