How To Find The Missing Values Of A Field By Comparing A Lookup File
Hi Guys!!!
Today we have come with an interesting trick where we will show you How To Find The Missing Values Of A Field By Comparing A Lookup File.
So, let’s start.
Step: 1
Lets say we have a master lookup file called “inventory.csv” which contains all the information( i.e – Name, Location and Id ) about the users.
| inputlookup inventory.csv
| dedup Name,Location,Id
| table Name,Location,Id
Explanation:
Here, we have used | inputlookup inventory.csv to see the content of the lookup file inventory.csv.
| dedup Name,Location,Id -> We have used this to discard the duplicate values from “Name” ,”Location” and “Id” fields.
| table Name,Location,Id -> We have used this to get the unique values “Name” ,”Location” and “Id” fields in tabular form.
Step: 2
Now, we will show you the indexed data,
Please, see the below query,
index="test_index"
| fields user
| rename user as Name
| dedup Name
| table Name
Explanation:
Here, we have used “test_index” as index where we are having some sample data.
| fields user -> We have used this to take only the “user” field.
| rename user as Name -> We have used this to rename the “user” field as “Name”, because the lookup file “inventory.csv” has the field “Name” and this field we are going to use for the comparison, so the field name should be same.
NOTE: You don’t have to rename the field if the both sources have
the same field name.
| dedup Name -> We have used this to discard the duplicate values from “user” field.
| table Name -> We have used this to get the unique values “user” field in tabular form.
Now, if you will check the “image: 1” and “image: 2” properly, you can see that the field “Name” from “inventory.csv” and the field “user” from the “test_index” have some common values (abhay,ayan,sarada) [because of this we are going to use this field for the comparison and we renamed also the “user” field to “Name” field]
Now, we want to make a query by comparing this inventory.csv and the indexed data to take only the values of the “Name” field which are not present in the indexed data and we will get the corresponding values of “Location” and “Id”. So, please follow the next steps.
Step: 3
| inputlookup inventory.csv
| dedup Name,Location,Id
| table Name,Location,Id
| append
[ search index="test_index"
| fields user
| rename user as Name
| dedup Name
| table Name ]
| stats count,values(*) as * by Name
Explanation:
In this step first, we have appended the two queries from “step: 1” and “step: 2” using “append” command.
Then, we have used “| stats count,values(*) as * by Name”.
| stats count,values(*) as * by Name -> These we have used to get the count of values of “Name” field and also the corresponding values from other fields (here, Location, Id).
As, you can see in the above image, we got the values of “count” field according to the occurrence of the values of “Name” field. So we are getting the values in the count fields like 1 and 2.
Step: 4
| inputlookup inventory.csv
| dedup Name,Location,Id
| table Name,Location,Id
| append
[ search index="test_index"
| fields user
| rename user as Name
| dedup Name
| table Name ]
| stats count,values(*) as * by Name
| where count=1
Explanation:
Here we have added “| where count=1” with the previous query (“Step: 3”).
“| where count=1” -> We have used this to get the rows where the “count” field value is “1” . This will show us only the values of “Name” field which are present in the master lookup file ( inventory.csv ) but not present in the indexed data (nayan,sayan) and their corresponding values of “Location” and “Id” field. In this way we will be able to get the missing field values in the index data.
What would be the logic if we want to find the Name field values which are present both in the lookup file and in our index data.
The answer is pretty much simple. We will find the values of “Name” fields where count field value is not equal to 1.
| inputlookup inventory.csv
| dedup Name,Location,Id
| table Name,Location,Id
| append
[ search index="test_index"
| fields user
| rename user as Name
| dedup Name
| table Name ]
| stats count,values(*) as * by Name
| where NOT count=1
Explanation:
As, you can see in the above image, we have added “| where NOT count=1” to the previous query (“Step: 3”).
“| where NOT count=1“ -> We have used this to exclude the rows where the “count” field value is “1” and to get only the values of “Name” field for which the “count” field value is not equal to 1 that means it will show only the values of the “Name” field which are common, both the indexed data as well as in the lookup file( inventory.csv) and also to get the corresponding values of “Location” and “Id” field.
Hope you have understood How To Find The Missing Values Of A Field By Comparing A Lookup File
Also we can use Join command to get the same output. Please click here to know more about the join command.
Happy Splunking !!!
