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

[greekleo89]

Hi

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

[bowesmana]

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

[greekleo89]

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

[bowesmana]

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

| eval dns_mismatch=if(match(DNS_enabled, "(?i)true|enabled") AND match(dns, "(?i)false|disabled") OR match(DNS_enabled, "(?i)false|disabled") AND match(dns, "(?i)true|enabled"), 1, 0)
| where dns_mismatch=1

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.

[greekleo89]

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.

[bowesmana]

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.

[greekleo89]

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

[richgalloway]

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