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"