topic How to compare and list the difference between search result and lookup file? in Getting Data In

How to compare and list the difference between search result and lookup file?

cheriemilk — Thu, 17 Nov 2022 14:41:38 GMT

hi team,

1. I have a query with below 2 columns returned only

PQ, ACT
pq1, act1
PQ1, act2
pQ1, act3
pq2, act4
QP2, act5
Pq2, act6
pq3, act7
Pq3, act8
pq_3, act9
...

2. Then I have a standard pq list csv file uploaded in splunk. In the csv file, there is a clumn called 'pq' with a standard pq values defined. please check below sample.

PQ
pq1
pq2
pq3
pq4
pq5
pq6
...

3. I want to compare the pq values in the splunk query result with the lookup csv file to find out

a) return the PQ and ACT that the PQ value is not exactly matched with the one defined in lookup file, include case sensitive issue.

b) return the PQ and ACT that the PQ value is in the query result but not in the lookup table

c) return the PQ and ACT that PQ value is not in the query but in the lookup table.

How to compose the query to meet the 3 requirements in step3?

BestRegards!

Re: Help - How to compare and list the difference between search result and lookup file?

ITWhisperer — Wed, 16 Nov 2022 08:29:37 GMT

Re: Help - How to compare and list the difference between search result and lookup file?

gcusello — Wed, 16 Nov 2022 08:35:00 GMT

Hi @cheriemilk,

please try something like this:

Ciao.

Giuseppe

Re: Help - How to compare and list the difference between search result and lookup file?

FrankVl — Wed, 16 Nov 2022 10:32:13 GMT

Can you clarify a bit what you mean with point a about the non-exact matches? Can you (given this sample data) sketch what the expected output of the query would be?

E.g. having Pq3, pq3 and pq_3, do you want the query to just tell you that pq3 exists in the lookup but Pq3 and pq_3 don't? Or should the query try to also match those 'imperfect' cases and if so should the query highlight that it is a non-exact match?

What you could do to cover all that is this:

First: create a lookup definition "pq" that refers to the pq.csv lookup file and configure that lookup definition to be case sensitive (as it is by default I believe)
Then, run this query (not tested, so you may need to debug the SPL a bit in case I made some typo or so):

Re: Help - How to compare and list the difference between search result and lookup file?

cheriemilk — Thu, 17 Nov 2022 06:40:29 GMT

Hi @ITWhisperer

Thank you for the help. is the stats values() function case senstive? Does it treat 'PQ1' , 'pq2', 'Pq2' and 'pQ2' as a same value or treat them to 4 different values?

| stats values(ACT) as ACT values(from) as from by PQ

Re: Help - How to compare and list the difference between search result and lookup file?

cheriemilk — Thu, 17 Nov 2022 06:31:41 GMT

hi @gcusello

Thank you for the help.

Re: Help - How to compare and list the difference between search result and lookup file?

ITWhisperer — Thu, 17 Nov 2022 06:47:57 GMT

The by PQ will treat the values in these fields case-sensitively i.e. PQ1 is not the same as Pq1 or pQ1 or pq1

Re: Help - How to compare and list the difference between search result and lookup file?

gcusello — Thu, 17 Nov 2022 06:52:07 GMT

Hi @cheriemilk ,

if one answer solves your need, please accept one answer for the other people of Community or tell us how we can help you.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the Contributors;-)