How to create Search for orphaned assets from a lookup - reverse wildcard?

[KMoryson]

Hi, I am working on a way to find an orphaned asset based on asset inventory I have in a lookup, which looks something like this:

assetname, owner, os
asset-01-abc, bob, win10
asset-03-abc, bob, win10

and I was able to find out in a different search that there exists asset "asset-02-abc" - an orphan - which is not present in the lookup.

So far I managed to transform the orphan name to "asset-*-abc", and I would like to create a search that would map that asset to the lookup and output something like this:

siblingassets, orphanedasset, owner
asset-01-abc asset-03-abc, asset-02-abc, bob

I tried the following:

[...search which created the orphaned asset list...]
|table orphaned_asset_originalname    orphaned_asset_wildcarded

| map [|inputlookup assets | search assetname="$orphaned_asset_wildcarded$"
| table assetname, "$orphaned_asset_full_name$"  owner]

But it doesn't output correctly, and I also tried using lookup but it simply didnt output anything

Thank you

[yuanliu]

---

something like this:

siblingassets, orphanedasset, owner
asset-01-abc asset-03-abc, asset-02-abc, bob

There is a question of definition in this desired output.  If I'm understanding your illustrated lookup correctly, the term "orphaned" means "no owner found in the lookup."  The lookup says that "bob" owns asset-01-abc and asset-03-abc, but not asset-02-abc.  Therefore, asset-02-abc is deemed "orphanedasset" as your desired output puts.  However, because you didn't specify how to determine "sibling" relationship, how can all three assets correspond to "bob"?

Here  is an exercise to help you translate the lack of owner in lookup into "orphaned" state (yes, I had to do in my searches too:-), assuming the main search returns events containing a field named asset_name (with underscore), and your lookup is named assettable.

mainsearch
| lookup assettable assetname AS asset_name
| fillnull owner value=orphaned_asset
| stats values(asset_name) as assets by owner

If your main search returns events containing asset-01-abc, asset-02-abc, and asset-03-abc (not just "orphaned" assets), the above exercise should give you a table like

owner	assets
bob	asset-01-abc asset-03-abc
orphaned_asset	asset-02-abc

Without knowing the relationship between "bob"s assets and orphaned assets, this is the best you can get.

Now, if I take a wild guess, you want to establish a sibling relationship based on some text pattern in asset name, e.g., that "-abc" that is common among the three, and you want these siblings to all be associated with owner "bob" even if the lookup table doesn't agree.  Under this expressed condition, you can something similar to the desired output you described, using something like

mainsearch
| rex field=asset_name "asset-\d+-(?<sibling>\w+)"
| lookup assettable assetname AS asset_name
| fillnull owner value="orphaned_asset"
| stats values(asset_name) as assets by sibling owner
| eval assets = "[" . mvjoin(assets, ", ") . "] (" . owner . ")"
| stats values(assets) as assets by sibling

Using the same assumptions about data as above, you'll get

sibling	assets
abc	[asset-01-abc, asset-03-abc] (bob) [asset-02-abc] (orphaned_asset)

Another guess would be that you definitely want all siblings, e.g., "abc", to be owned by any owner that showed up in lookup for one of siblings, e.g., "bob".  If this is your desire, you can simplify the search to

| mainsearch
| rex field=asset_name "asset-\d+-(?<sibling>\w+)"
| lookup assettable assetname AS asset_name
| eval orphanedasset = if(isnull(owner), asset_name, null()), asset_name = if(isnull(owner), null(), asset_name)
| stats values(asset_name) as siblingassets values(orphanedasset) as orphanedasset values( values(owner) as owner by sibling

Using the same assumptions about data,

sibling	siblingassets	orphanedasset	owner
abc	asset-01-abc asset-03-abc	asset-02-abc	bob

Although this is the closest to your described output, note that this depends on that all siblings with -abc must only return a unique owner in the lookup.  Otherwise the output can be surprising.

[KMoryson]

Hi looks like I made some mistakes with explaining what I want to achieve, my apologies.

The thing is that I have a lookup with various system families that are different only in numbers in them, for example;

familyA01
familyB02
familyB03

Which is one family set, then other it can be:

01-group-B
02-group-B
03-group-C

Or any other combo, where the only difference are the numbers inside.
But the issue is that some of the assets are not in the lookup AT ALL and I find them in for example wineventlog, like in this example;
in lookup:

asset,owner
pack01C,alan
pack03C,alan

but in search, AND NOT IN LOOKUP I find:
pack02C

^ this asset doesn't exist in lookup at all

And I need to find a way to compare that asset to lookup to find what family it belongs in.
So far I have searches that find those orphans, based on just comparing what isnt in the lookup and transforming them into pattern, like with the last example into: "pack*C", and I would like to see an output:

orphan,rest_of_family,owner
pack02C,pack01C pack03C, alan

So I can create a report this set just like above to the owner
Apologies once again, and I hope that I am more clear this time

[yuanliu · Accepted solution]

This explanation is much clearer.  The key relationship among assets is "family". (My previous reply used "sibling".)  You also expressed confidence that any asset in the packXXC family are all with owner alan in lookup if they are in the lookup at all.  This corresponds to the third scenario I described.  So, the same code concept can be applied.

| mainsearch ``` that returns all assets of interest no matter which family ```
| eval family = replace(asset_name, "\d+", "") ``` asset name without numbers ```
| lookup assettable asset AS asset_name
| eval orphanedasset = if(isnull(owner), asset_name, null()), asset_name = if(isnull(owner), null(), asset_name)
| stats values(orphanedasset) as orphan values(asset_name) as rest_of_family values(owner) as owner by family

Here, I assume that events from the mainsearch contain a field called asset_name.  It could be the server's Windows name in wineventlog, for example; you can substitute "asset_name" with the actual field name. This should get you the table.

Although this method kind of works, you may want to consider how best to design your lookups, if you are free to design them.  Since you are confident that each family of assets should have one owner (that is also my use case), I prefer to introduce an orthogonal table to express that relationship:

table: family_owner

family	owner
familyA	bob
familyB	bob
-group-B	alice
-group-C	alice
packC	alan

With this auxiliary table, your assets table can go without owner, or if owner is included, only record exceptions to family_owner values.

table: assettable

asset	operatingsystem
pack01C	win10
pack03C	win10
family01A	win10
family02A	win10

With these two tables, if all you want is owner, you can simply lookup asset_owner; if all you want is to find out which assets are not in assettable, you can look up just  assettable, etc.   Data normalization has many advantages.

[KMoryson]

I ended up using another lookup with families and comparing against it, thank you for your help.