Combine 2 searches such that for each row on the first search, all rows on the second search will be joined

[mgbersales]

I have a saved search which contains the following information:
ServerName Environment
Server1 Prod
Server2 Dev

I want join this with a lookup table containing a list of accounts:
AccountName Privilege
Account1 role1
Account1 role2
Account2 role2

The expected output is below:
ServerName AccountName Privilege
Server1 Account1 role1
Server1 Account1 role2
Server1 Account2 role2
Server2 Account1 role1
Server3 Account1 role2
Server4 Account2 role2

[woodcock]

We can do this by exploiting a very nuanced peculiarity of appendpipe like this:

Your Search Here
| appendpipe [ |inputlookup YourLookupTableNameHere.csv ]
| where isnotnull(ServerName) AND isnotnull(AccountName)
| table ServerName AccountName Privilege

Here is a run-anywhere demonstration:

|makeresults | eval _raw="ServerName,Environment
Server1,Prod
Server2,Dev"
| multikv forceheader=1
| fields - linecount _time _raw
| rename COMMENT AS "I want join this with a lookup table containing a list of accounts:"
| appendpipe [ |makeresults | eval _raw="
AccountName,Privilege
Account1,role1
Account1,role2
Account2,role2"
| multikv forceheader=1
| fields - linecount _time _raw]
| where isnotnull(ServerName) AND isnotnull(AccountName)
| table ServerName AccountName Privilege

Hey @DalJeanis, you need to check out this one.

[to4kawa · Accepted solution]

| makeresults 
| eval _raw="ServerName,Environment
Server1,Prod
Server2,Dev
Server3,Dev
Server4,Dev" 
| multikv forceheader=1 
| table ServerName,Environment 
`comment("this is sample data")`
| appendcols 
    [| makeresults 
    | eval _raw="AccountName,Privilege
Account1,role1
Account1,role2
Account2,role2" 
    | multikv forceheader=1 
    | table AccountName,Privilege 
    `comment("this is sample data, use inputlookup")`
    | eval count = 1 
    | stats list(*) as * by count 
    | append 
        [| makeresults 
        | eval _raw="AccountName,Privilege
Account1,role1
Account1,role2
Account2,role2" 
        | multikv forceheader=1 
        | table AccountName,Privilege 
        `comment("this is sample data, use inputlookup")`
        ]] 
| mvexpand AccountName 
| streamstats count as count by ServerName 
| eval Privilege = mvindex(Privilege, count - 1) 
| table ServerName, AccountName, Privilege

Hi, I'm not sure about server3 and server4, but how about this?

[mgbersales]

Thanks @to4kawa, kindly disregard server3 and server4. 🙂 From running the query(removing server3 and server4), the results show some missing entries

Server1 Account1 role1
Server1 Account1 role2
Server1 Account2 role2
Server2 Account1 role1
NULL Account1 NULL
NULL Account2 NULL

My goal is for each server to have all the rows exisiting on the Account_Name Lookup

[to4kawa]

| makeresults 
 | eval _raw="ServerName,Environment
 Server1,Prod
 Server2,Dev" 
 | multikv forceheader=1 
 | table ServerName,Environment 
 `comment("this is sample data")`
 | appendcols 
     [| makeresults 
     | eval _raw="AccountName,Privilege
 Account1,role1
 Account1,role2
 Account2,role2" 
     | multikv forceheader=1 
     | table AccountName,Privilege 
     `comment("this is sample data, use inputlookup")`
     | eval count = 1 
     | stats list(*) as * by count 
     | append 
         [| makeresults 
         | eval _raw="AccountName,Privilege
 Account1,role1
 Account1,role2
 Account2,role2" 
         | multikv forceheader=1 
         | table AccountName,Privilege 
         `comment("this is sample data, use inputlookup")`
         ]] 
 | mvexpand AccountName 
 | streamstats count as count by ServerName 
 | eval Privilege = mvindex(Privilege, count - 1) 
 | table ServerName, AccountName, Privilege

If you delete an item, you get a result.

[dindu]

Hi,
You could use look up .But it should have a common field value in the look up table to fetch the results.

|your_search
|field01,field02,common_field
|lookup lookup_name common_field OUTPUT field03,field04
|table field01,field02,common_field, field03,field04

However,based on your desired results - please check on the map command.
Map command will provide a looping mechanism to generate results.

Happy Splunking!!