Splunk Search

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

KMoryson
Explorer

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

Labels (5)
Tags (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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

familyowner
familyAbob
familyBbob
-group-Balice
-group-Calice
packCalan

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

assetoperatingsystem
pack01Cwin10
pack03Cwin10
family01Awin10
family02Awin10

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.

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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

ownerassets

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

siblingassets

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,

siblingsiblingassetsorphanedassetowner

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.

 

0 Karma

KMoryson
Explorer

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

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

familyowner
familyAbob
familyBbob
-group-Balice
-group-Calice
packCalan

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

assetoperatingsystem
pack01Cwin10
pack03Cwin10
family01Awin10
family02Awin10

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
Explorer

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

0 Karma
Get Updates on the Splunk Community!

New Cloud Intrusion Detection System Add-on for Splunk

In July 2022 Splunk released the Cloud IDS add-on which expanded Splunk capabilities in security and data ...

Happy CX Day to our Community Superheroes!

Happy 10th Birthday CX Day!What is CX Day? It’s a global celebration recognizing innovation and success in the ...

Check out This Month’s Brand new Splunk Lantern Articles

Splunk Lantern is a customer success center providing advice from Splunk experts on valuable data insights, ...