How to compare csv and search and show results which are different from csv?

[vrmandadi]

Hello Splunkers ,

I am using the following search which outputs the following fields   host ,Component  and output and then it compares with a lookup file (below) which has fields Component  and output .I filter the results by using | |where mvcount(from)=1 AND from="search" and I get correct results(attached screenshot) but i want to add another column from lookup to show what is in lookup and what is different in search .For instance in the attached screenshot for GPU0 the output value is 96.00.2F.00.06  which is different in lookup...I want to show the output value for that GPU0 of the lookup beside the output column

Components	output
BMC and AUX	22.10 0x12
CPLD	00 00 46
SBIOS version	0.2
nvme9	EPK9CB5Q
nvme8	EPK9CB5Q
nvme7	EPK9CB5Q
nvme6	EPK9CB5Q
nvme5	EPK9CB5Q
nvme4	EPK9CB5Q
nvme3	EPK9CB5Q
nvme2	EPK9CB5Q
nvme1	EPK9CB5Q
nvme0	EPK9CB5Q
GPU0	96.00.39.00.08
GPU1	96.00.39.00.08
GPU2	96.00.39.00.08
GPU3	96.00.39.00.08
GPU4	96.00.39.00.08
GPU5	96.00.39.00.08
GPU6	96.00.39.00.08
GPU7	96.00.39.00.08

index=preos host IN(*) *CPLD* OR BMC OR SBIOS OR *nvme* OR "*GPU* PCISLOT*" host=*
| rex field=_raw "log-inventory.sh\[(?<id>[^\]]+)\]\:\s*(?<Component>[^\:]+)\:\s*(?<Hardware_Details>.*)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*CPLD\:\s*(?<Hardware>[^.*]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*BMC\:\s*version\:\s*(?<Hardware1>[^\,]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*SBIOS\s*version\:\s*(?<Hardware2>[^ ]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*nvme\d*\:.*FW\:\s*(?<Hardware3>[^ ]+)" 
| rex field=_raw "VBIOS\:\s*(?<Hardware4>[^\,]+)" 
| eval output = mvappend(Hardware, Hardware1,Hardware2,Hardware3,Hardware4)
| replace BMC WITH "BMC and AUX" in Component 
| table Component output host _time

| sort Component 
| dedup Component 
| fields - _time | eval from="search" 
| append [| inputlookup component.csv 
| table Component output 
     | eval from="lookup"]
| stats values(from) as from values(host) as host by Component output
| where mvcount(from)=1 AND from="search"

[bowesmana]

There's a bunch of stuff in your search that's not necessary (table, sort, dedup), so I think this should work for you

index=preos host IN(*) *CPLD* OR BMC OR SBIOS OR *nvme* OR "*GPU* PCISLOT*" host=*
| rex field=_raw "log-inventory.sh\[(?<id>[^\]]+)\]\:\s*(?<Component>[^\:]+)\:\s*(?<Hardware_Details>.*)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*CPLD\:\s*(?<Hardware>[^.*]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*BMC\:\s*version\:\s*(?<Hardware1>[^\,]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*SBIOS\s*version\:\s*(?<Hardware2>[^ ]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*nvme\d*\:.*FW\:\s*(?<Hardware3>[^ ]+)" 
| rex field=_raw "VBIOS\:\s*(?<Hardware4>[^\,]+)" 
| eval output = mvappend(Hardware, Hardware1,Hardware2,Hardware3,Hardware4)
| replace BMC WITH "BMC and AUX" in Component 
| stats values(host) as host by Component output
| lookup component.csv Component OUTPUT output as defaultOutput
| where output!=defaultOutput

 i.e. do the stats and then just lookup the Component and OUTPUT the output field from the lookup and then compare if they match

[vrmandadi]

@bowesmana  Thank you for your reply .Does this search looks for the latest component and output values of each host and compare with the csv or it checks all the older values..I am trying to match the latest values of each host with the lookup

[vrmandadi]

This worked for me

index=pr  *CPLD* OR BMC OR SBIOS OR *nvme* OR "*GPU* PCISLOT*" 
| rex field=_raw "log-inventory.sh\[(?<id>[^\]]+)\]\:\s*(?<Component>[^\:]+)\:\s*(?<Hardware_Details>.*)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*CPLD\:\s*(?<Hardware>[^.*]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*BMC\:\s*version\:\s*(?<Hardware1>[^\,]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*SBIOS\s*version\:\s*(?<Hardware2>[^ ]+)" 
| rex field=_raw "log-inventory.sh\[\d*\]\:\s*nvme\d*\:.*FW\:\s*(?<Hardware3>[^ ]+)" 
| rex field=_raw "VBIOS\:\s*(?<Hardware4>[^\,]+)" 
| eval output = mvappend(Hardware, Hardware1,Hardware2,Hardware3,Hardware4) 
| replace BMC WITH "BMC and AUX" in Component 
| stats latest(output) as output latest(_time) as _time by Component host 
| sort Component 
| fields - _time 
| eval from="search" 
| join Component 
    [| inputlookup component.csv 
    | table Component output 
    | eval from="lookup" 
    | rename output as lookup_output 
    | fields lookup_output Component output] 
| stats count(eval(lookup_output==output)) AS case BY host Component output lookup_output 
| replace 1 WITH "match" IN case 
| replace 0 WITH "No match" IN case 
| sort case | stats  values(Component) as Component  by host lookup_output case output
| search case!=match 
| fields host Component output lookup_output 
| sort - Component