topic Re: How to compare these two CSV files? in Splunk Search

How to compare these two CSV files?

Rithekakan — Mon, 14 Feb 2022 15:23:04 GMT

I have reports Quarter1.csv and Quarter2.csv.
after I upload these two csv report I got

host="***" source="****" sourcetype="***" and those fields IP_Address,Plugin_Name,Severity,Protocol,Port,Exploit,Synopsis,Description,Solution,See_Also,CVSS_V2_Base_Score,CVE,Plugin.

I want 3 reports base on joining with these 6 files:

IP_Address, Plugin_Name, Severity, Protocol, Port, Exploit, | table IP_Address,Plugin_Name,Severity,Protocol,Port,Exploit,Synopsis,Description,Solution,See_Also,CVSS_V2_Base_Score,CVE,Plugin, status

First report: - if the event are in Quarter1.csv and Quarter2.csv. show status as "Active Vulnerability"

Second report:- if the event are in Quarter1.csv but not in Quarter2.csv. show status as "Fixed"

Third report:- if the event are not in Quarter1.csv but there are in Quarter2.csv. show status as "New Active Vulnerability"

Re: How to compare these two CSV files?

yuanliu — Fri, 11 Feb 2022 03:22:57 GMT

I assume that you ingested these files as events, not upload as CSV lookups, i.e., respective contents can be accessed with source=Quarter1.csv and source=Quarter2.csv. You can do something like

| fields IP_Address,Plugin_Name,Severity,Protocol,Port,Exploit,Synopsis,Description,Solution,See_Also,CVSS_V2_Base_Score,CVE,Plugin source | stats values(*) as * by Plugin,IP_Address,Plugin_Name,Severity,Protocol,Port,Exploit | eval status = case(mvcount(source)>1,"Active Vulnerability", source=="Quarter1.csv","Fixed", true(), "New Active Vulnerability")

Re: How to compare these two CSV files?

PickleRick — Fri, 11 Feb 2022 07:25:55 GMT

With two data sets to compare I prefer the "summing" approach.

sourcetype=A OR sourcetype=B
| eval picker=if(sourcetype="A",1,2)
| stats sum(picker) as picker by whatever fields you need
| eval status=case(picker=1,"only A",picker=2,"only B",picker=3,"both",1=1,"something wrong")

Yes, I know OP wanted three separate reports, but that's the general approach.

Re: How to compare these two CSV files?

Rithekakan — Fri, 11 Feb 2022 23:21:26 GMT

Hi yuanliu , I appreciate your helpful Splunk query. It is 100% correct and fit my query. Thanks for your help.

Regards,

Rithekakan

Re: How to compare these two CSV files?

Rithekakan — Sun, 20 Feb 2022 04:51:55 GMT

Hi PickleRick, Thanks for your query, It help me for my report, but something is missing.

I want a report with these fields.
| table IP_Address, device, Plugin_Name, Severity, model, Protocol, Port, Exploit, Synopsis, Description, Solution, See_Also, CVSS_V2_Base_Score, CVE,Plugin

with join only 6 fields in the | stats sum " IP_Address,Plugin_Name,Plugin,Severity,Protocol,Port. "

| stats sum(picker) as.....

How eve if I add all 13 fields in the | stats sum(picker)as ..........It provide the uncorrected report becuse of some fields have duplicate data.
Note: There are two even (device and model) are lookup from other Inventory CSV file.

Could you pls help with this reequipment? I will be appreciate for your help.

Regards,
Ritheka Kan

Re: How to compare these two CSV files?

Rithekakan — Sun, 20 Feb 2022 05:09:31 GMT

Hi yuanliu , I 've reply to accept your query in the previous time. It is 100% correct.
How ever the management needs two more fields (device, model) which it is lookup from the CVS inventory file.
and the report is something look like this.
| table IP_Address, device, Plugin_Name, Plugin, Severity, model, Protocol, Port, Exploit, Synopsis, Description, Solution, See_Also, CVSS_V2_Base_Score, CVE,

Could you pls help me edit your query you've provided to make it work with the lookup CSV file.

I have this lookup fils.
| lookup ABLNInventory.csv ip_address as IP_Address output device, model
| table IP_Address, device, Plugin_Name, Severity, model, Protocol, Port, Exploit, Synopsis, Description, Solution, See_Also, CVSS_V2_Base_Score, CVE, Plugin

I will appreciate for your help.
Best regards,
Ritheka Kan

Re: How to compare these two CSV files?

yuanliu — Sun, 20 Feb 2022 08:12:45 GMT

The way I see it, device and model are not used as criteria. So, simply add it.

After this, you can arrange the table however you like.

Re: How to compare these two CSV files?

Rithekakan — Mon, 21 Feb 2022 15:17:13 GMT

Hi yuanliu,

I got it now. Thanks for your solution.

Regards,

Ritheka Kan