Is it possible to combine multiple rows in a lookup table?

[AlexeySh]

Hello,

I use a dbxquery to import asset’s tags which includes information about asset’s category, business unit and priority. The initial result looks like this:

asset   | tag
asset_1 | server
asset_1 | marketing
asset_1 | medium
asset_2 | network_equipment
asset_2 | infrastructure
asset_2 | low

Using eval command I split it that information to appropriate columns, so the result looks like that:

asset   | category          | bunit          | priority   |
asset_1 | server            |                |            |
asset_1 |                   | marketing      |            |
asset_1 |                   |                | medium     |
asset_2 | network_equipment |                |            |
asset_2 |                   | infrastructure |            |
asset_2 |                   |                | low        |

The result is saved to a lookup table. Unfortunately, I can’t use that format as Data Enrichment source for Splunk Enterprise Security because it will automatically dedup asset’s names.

So I’d like to know if it’s possible to combine the information from different rows from the previous table to a format like this:

asset   | category          | bunit          | priority   |
asset_1 | server            | marketing   | medium     |
asset_2 | network_equipment | infrastructure | low        |

Thanks for the help!

[p_gurav]

Can you try after your search query:

| stats values(category) values(bunit) values(priority) by asset 


OR 

 | stats list(category) list(bunit) list(priority) by asset 

[AlexeySh]

Exactly!
Thank you so much.

[FrankVl · Accepted solution]

I'm wondering if this couldn't be solved by a smarter dbxquery, that puts it in the right format immediately, but you can anyway fix this, by adding the following to your search:

| eventstats values(category) AS category values(bunit) AS bunit values(priority) AS priority by asset
| dedup asset

Edit: Or as @p_gurav suggests a simple stats instead of eventstats, which saves you the dedup step. Not sure why I didn't think of that 😛

[AlexeySh]

Thanks for your help @Frank!