How To Find The Missing Data In Inventory But Present In Index And Vice Versa
Hi Guys!!!
In our earlier blog we had discussed about How To Find The Missing Values Of A Field By Comparing A Lookup File. But what if there is some missing value in the inventory file itself. Today we have come with an interesting trick where we will show you how we can get the missing data in a lookup ( inventory ) file but present in index data and vice versa.
So, let’s start.
Step: 1
Here, we will show you the data of the lookup file “inventory.csv”
| inputlookup inventory.csv
Explanation:
Here, we have used | inputlookup inventory.csv to see the content of the lookup file inventory.csv.
Step: 2
Now, we will show you the indexed data,
Please, see the below query,
index="test_index"
| table user,id,location
Explanation:
Here, we have used “test_index” where we have our sample data.
| table user,id,location -> We have used this to get the values “user”, “id” and “location” fields in tabular form.
Now, if you will check the “image: 1” and “image: 2” properly, you can see that some values of the field “Name” from “inventory.csv” are missing in the “user” field from the indexed data and vice versa.
Now, we want to make a query by comparing this inventory.csv and the indexed data to get this missing values of Name / user field. So, please follow the next steps.
Step: 3
Here, we will show you the query to get the missing data in lookup file, but present in the indexed data. So, please find the below query,
|inputlookup inventory.csv
| table *
| eval comment="inventory"
| append
[ search index="test_index"
| table user,id,location
| rename user as Name,id as Id,location as Location
| eval comment="index"]
| stats values(*) as *,count by Name
| search count=1 AND comment="index"
Explanation:
QUERY: 1
|inputlookup inventory.csv -> To see the content of the lookup file Name.csv.
| table * -> To get all the fields in tabular form.
| eval comment=”inventory” -> We created a new field named “comment” which has “inventory” as value, to recognize values of all the fields from lookup file as inventory data.
QUERY: 2
index=”test_index” -> To get the data from the index “test_index”
| table user,id,location -> We have used this to get the values “user”, “id” and “location” fields in tabular form.
| rename user as Name,id as Id,location as Location -> To rename the “user” field as “Name”, “id” field as “Id”, “location” field as “Location”.
| eval comment=”index” -> We created a new field named “comment” which has “index” as value to recognize values of all the fields from indexed data.
To append the QUERY: 1 and QUERY: 2 together, we used “| append” command.
| stats values(*) as *,count 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).
| search count=1 AND comment=”index” -> Now to get the data missing from “inventory.csv” file which are present in indexed data, we are taking the rows where the “count” field value is “1” along with the “comment” field value “index”.
As a result, you can see in the above image, we are only getting the values of “Name” field (mohit, roshni) which are missing in the lookup file, but present in the indexed data and also the corresponding values of other fields.
Step: 4
Here, we will show you the query to get the data missing in the index, but present in the lookup file. So, please find the below query,
| inputlookup inventory.csv
| table *
| eval comment="inventory"
| append
[ search index="test_index"
| table user,id,location
| rename user as Name,id as Id,location as Location
| eval comment="index"]
| stats values(*) as *,count by Name
| search count=1 AND comment="inventory"
Explanation:
This query is almost similar to the query of “Step: 4” except the last command.
Here we have used,
| search count=1 AND comment=”inventory” to get the missing values from indexed data, but present in the lookup file “inventory.csv”, we are taking the rows where the “count” field value is “1” along with the “comment” field value “inventory”.
As a result, you can see in the above image, we are only getting the values of “Name” field (moumita, sayan) which are missing in the indexed data, but present in the lookup file “inventory.csv” and also the corresponding values of other fields.
Hope you have understood the topic: How To Find The Missing Data In Inventory But Present In Index And Vice Versa
Also we can use Join command to get the same output. Please click here to know more about the join command.
Happy Splunking !!!
