topic Re: Compare .csv file with results from search and join by uniqueID in Splunk Search

How to compare .csv file with results from search and join by uniqueID?

greekleo89 — Wed, 02 Nov 2022 02:40:51 GMT

I have a search

index=main sourcetype=data2 type=policy

that gives me the following in json:

customerId: man0000

dns: false

ioc: true

type: policy

I have a csv which has the following (the purpose of the csv is to show what the default settings should be across all customers)

Config Item, Config setting
DNS, Enabled
IOC, Disabled

We also have a list of customers in a database with the customerId's

So my search logic was as follows:

Search the index to bring all the different search results as a table
rename the search results so instead of dns have DNS and instead of ioc have IOC etc

| join customer ID
[| dbxquery query=.....] - to get cus id's

|Inputlookup the csv file (here is where i get stuck)

I don't know how to link them together so that for every customerid from the DB that matches the customerID in the search to compare the results from search i.e where ioc: true and on csv is Disabled, to output the results.

Any help would be appreciated.

Thanks in advance

Re: Compare .csv file with results from search and join by uniqueID

richgalloway — Tue, 01 Nov 2022 16:48:52 GMT

You want lookup rather than inputlookup. The latter reads in the entire lookup file whereas the former maps field values to lookup values. Because DNS and IOC are in separate fields, we need separate lookups.

| join customer ID [| dbxquery query=.....] - to get cus id's |lookup mylookup.csv "Config Item" AS DNS OUTPUT dns_enabled |lookup mylookup.csv "Config Item" AS IOC OUTPUT ioc_enabled

Re: Compare .csv file with results from search and join by uniqueID

bowesmana — Tue, 01 Nov 2022 21:32:49 GMT

If you are intending to get the 'default' setting for the "DNS" and "IOC" into your event, so you can compare the returned search value "false" in the dns field against the default DNS setting if "Enabled" then you can either do

| eval item="DNS" | lookup mylookup.csv "Config Item" AS item OUTPUT dns_enabled | eval item="IOC" | lookup mylookup.csv "Config Item" AS item OUTPUT ioc_enabled

then you will have two new fields in each event with the value of the config setting. You can then make comparisons as needed against your dns and ioc fields against the setting fields.

Alternatively you can do

your search... | appendcols [ | inputlookup mycsv.csv ``` This is to remove the spaces in the field names ``` | rename "Config Item" as ConfigItem, "Config setting" as ConfigSetting | eval {ConfigItem}_default = ConfigSetting | fields *_default | stats values(*) as * ] | filldown *_default

and that will give you two new fields in each event, IOC_default and DNS_default

Re: Compare .csv file with results from search and join by uniqueID

greekleo89 — Tue, 01 Nov 2022 21:41:16 GMT

Hi,

Thats correct, so in a nutshell.

If the search result is:

ioc: false
dns:false

and in the csv i have

Config Item, Config Setting
DNS,Enabled

Then i want my search result to basically show a line similar to the below.

customer Config Item default live

man0000 DNS Enabled or true Disabled or false

Only return result where there is a mismatch, showing the above.

Thanks,

Greg

Re: Compare .csv file with results from search and join by uniqueID

bowesmana — Tue, 01 Nov 2022 22:09:52 GMT

So having used either of the techniques in the earlier post to get the data, use the following clause

this sets a 1/0 value to dns_mismatch field if the actual value of dns field does NOT match the default setting (here is is case insensitive and supports the true/false/enabled/disabled variants of setting.

The just table out the fields you want.

Same applies to the IOC variant.

Re: Compare .csv file with results from search and join by uniqueID

greekleo89 — Tue, 01 Nov 2022 22:23:21 GMT

Hi,

Thank you for this, i will try it.

However there are 15 settings files and most of them have like 12/13 settings so these searches will be huge to write out, but if this is the only way then so be it.

Re: Compare .csv file with results from search and join by uniqueID

bowesmana — Wed, 02 Nov 2022 05:32:08 GMT

There are other ways to make it a one liner, e.g. this example is based on a lookup file containing the following lookup values

Config_Item Config_setting DNS,Enabled IOC,Disabled S3,Disabled TCP,Enabled UDP,Disabled XYZ,Enabled

This example below then makes 10 events each with fields containing a random true/false setting to prove how this works.

| makeresults count=10 ``` THIS MAKES RANDOM TRUE/FALSE VALUES FOR THE FOLLOWING FIELDS ``` | eval options=split("true,false",",") | eval dns=mvindex(options, random() % 2), ioc=mvindex(options, random() % 2), s3=mvindex(options, random() % 2), tcp=mvindex(options, random() % 2), udp=mvindex(options, random() % 2), xyz=mvindex(options, random() % 2) | fields - options ``` THIS APPENDS THE LOOKUP COLUMNS ``` | appendcols [ | inputlookup mycsv.csv | eval Item=lower(Config_Item) | eval {Item}_default = Config_setting | fields *_default | stats values(*) as * ] | filldown *_default ``` AND THIS MAKES XXX_MISMATCH FIELD TO INDICATE MISMATCH, AS WELL AS A MASTER MISMATCH FIELD ``` | foreach *_default [ eval <<MATCHSTR>>_mismatch=if(match('<<FIELD>>', "(?i)true|enabled") AND match(<<MATCHSTR>>, "(?i)false|disabled") OR match('<<FIELD>>', "(?i)false|disabled") AND match(<<MATCHSTR>>, "(?i)true|enabled"), 1, 0), mismatch=if(<<MATCHSTR>>_mismatch=1, 1, mismatch) ] | where mismatch=1

So, it's that final one foreach statement that does the comparison for all fields named *_default and compares the * component to a field in your data with the same name. You don't need to set a xxx_mismatch field, that's just to show you what's happening.

However, when you talk about 15 different settings files, maybe this may not be the right solution for your use case. If you can give a broader definition of your use case, and how all those settings files are supposed to work, we can see if there is a more appropriate solution.

Re: Compare .csv file with results from search and join by uniqueID

greekleo89 — Wed, 02 Nov 2022 09:28:45 GMT

Hi,

Sure so i have 15 different .csv's all named differently.

policy.csv

audit.csv

The files all contain the columns:
Configuration Item Configuration Setting
DNS,Enabled
IOC,Disabled

**Note - I can't combine the csv's together because there would be duplicate fields i.e exist in more than one file.

My search is as follows:

Index=main sourcetype="data" type=policy ''' the type basically exists in every csv file, coincidently the csv's are named by the type, i.e policy.csv.

|rename dns as DNS ioc AS IOC customerId as companyId

|table DNS IOC companyId

| join companyId

[| dbxquery query="SELECT companyId FROM table_systems WHERE status =1 connection="live_Db"]

My results (before the input lookup) look like this in a table

IOC DNS companyId

false true man0000

What i would like is

Configuration Item Default Current or Live companyId
IOC Enabled disabled or False man0000

Thank you in advance