topic How to compare two lookup files and get two coloumns in serch in Splunk Search

How to compare two lookup files and get two coloumns in serch

Dheeraj25 — Thu, 01 Apr 2021 12:20:42 GMT

I have two lookups B1.csv and B2.csv. B1 has block member and B2 has block id and both have one same column departments. I want to compare these both with departments and get matching values of Block member and BLOCK ID. I also have index Z from which I am done search along with my two looks ups

B1:
BlockMember --- Department--email
B2:
BlockID --Department

Index and B1 has email as same values so I used "lookup B1.csv" email command and got block member in my table but now I am not sure how to get blockid from b2.

My current search

index=Z pipename=static-website*
|lookup b1 email
|rename member AS BlockMember (got this blockmember from above lookup b1 using email from my index)
|stats count by grid BlockMember Status

current table:
grid----status--Blockmember

so my future table should be
grid----status--BlockID--Blockmember(which will have same department)

Re: How to compare two lookup files and get two coloumns in serch

richgalloway — Thu, 01 Apr 2021 12:19:26 GMT

The two lookups can be matched using inputlookup and stats.

| inputlookup B1.csv | inputlookup append=true B2.csv | stats values(*) as * by Department

Re: How to compare two lookup files and get two coloumns in serch

Dheeraj25 — Thu, 01 Apr 2021 12:27:10 GMT

Thanks but I also have an index that does the lookup with B1. I had updated my question above .. can you check and suggest ?

Re: How to compare two lookup files and get two coloumns in serch

richgalloway — Thu, 01 Apr 2021 17:01:31 GMT

Just add another lookup command for B2. It looks like it doesn't matter, though, since the query only uses the grid, member, and Status fields.

index=Z pipename=static-website* |lookup b1 email |lookup b2 department |rename member AS BlockMember (got this blockmember from above lookup b1 using email from my index) |stats count by grid BlockMember Status