Splunk Search

Using lookup table & Append

Path Finder

I have two queries. First one has multiple fields: source, IP, comment & cIP and this is exported CSV as a output lookup table. Second query also has source & IP which is used for comparison. It will have also have comment which needs to be appended with values from the first query. It will also have other fields nIP, logN. The second query needs to compare values of source & IP from the lookup table and retrieve fields comment & cIP from the look up table & output.

Ex.

Query 1 output -> lookup table:

source

IP

Comment

cIP

xyz.com

111.111.111.111

table1

1

xyz.com

111.111.111.112

table1

2

abc.com

111.111.111.111

table1

1

 

Query 2 Fields:

source

IP

Comment

nIP

logN

xyz.com

111.111.111.111

table2

2

951

xyz.com

111.111.111.112

table2

3

751

qwe.com

255.255.255.001

table2

2

152

 

Final Output

source

IP

Comment

cIP

nIP

logN

xyz.com

111.111.111.111

table1

table2

1

2

951

xyz.com

111.111.111.112

table1

table2

2

3

751

abc.com

111.111.111.111

table1

1

-

-

 

Query 2 contains more data than Query 1, I only need to include source & IP that are output from Query 1 only. For example, in Query 2, there is qwe.com which is not in Query 1 so that is not in the Final Output but abc.com is.

 

I am currently doing append:

 

index=* AND source=*.log
| dedup source IP
| fields source IP Comment nIP logN
| append
  [search index=app* earliest=-15m
  | eval host=IP
  | dedup source IPNAME
  | table SRVID IPNAME comment cIP]
| stats values(comment) as comment, values(nIP) as nIP, values(logN) as logN by IPNAME,SRVID
| where comment=="table1"
Labels (1)
Tags (3)
0 Karma