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
| 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?
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.
| 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?
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
| 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.
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!!