How to find missing values from two different indexes?

[aliroumani]

dears
i want to compare two indexes to find the values in index 1 and not in index 2
index 1 have field called account_name with different values (numeric and alphabetic) and numeric values starts with 00
index 2 have field called personnel with same values in account_name with same numeric values but without the 00
ex account_name=0035315 and personnel=35315
both are the same but it depends in which index it appears.
i want to compare both fields to find values in account_name and not in personnel?
i'm facing this challenge:
even remove zeros from numeric values in account_name or add zeroes to numeric values in personnel to map these values as the same value.
please its top urgent and i will be thankful for anyone can help me with it.

regards

[niketn · Accepted solution]

You can try the following two options. Query 1 should work instantly, however for query 2 you need to create 2 knowledge objects i.e. Field Alias and Calculated Fields. Option 2 is recommended as that would eventually lead to easy maintenance and reuse of Splunk Search queries.

Option 1 - Simple but without the use of Splunk Knowledge Objects
If you do not want to create an Alias, you can do the same directly at Search time in Splunk.

index=personnelIndex OR index=accountNameIndex (account_name=* OR personnel=*)
| rename personnel as Account_Name
| rename account_name as Account_Name
| eval Account_Name=ltrim(Account_Name,"0") 
| stats count as MatchCount values(sourcetype) as Sourcetypes by Account_Name 
| search Sourcetypes="splunk_answers_483591_account" MatchCount=1

PS:
1. ltrim is used to remove leading zeros.
2. If possible you should be using source or sourcetype or host also in your base filter query to reduce the number of events filtered upfront(which will improve the performance of your search query).

Option 2 - With the use of Splunk Knowledge Objects - Field Alias and Calculated Fields
Ideally this is the use case for creating a Field Alias and Calculated fields Splunk Knowledge Objects, which will allow easy maintenance and re-usability with less code.

If you can distinguish personnelIndex and accountNameIndex based on either one of source, sourcetype or host, you can create a field alias (so that same field name can be used for both indexes). Also you can create a Calculated Field for account_name to trim leading zeros using ltrim SPL command.

For example you can create FIELDALIAS-account_name alias for personnelIndex index using either one of its sourcetype, source or host i.e. personnel AS account_name, which will create a search time field account_name in personnelIndex search as well.
You also need to create EVAL-account_name Calculated field for accountIndex using either one of its sourcetype, source or host i.e. ltrim(account_name,"0"). This will remove leading zeros from account_name field. Following is the snippet from props.conf once you create the knowledge objects (add your sourcetype/host/source segment as per your use case).

[<your_sourcetype_personnel>l]
FIELDALIAS-account_name = personnel AS account_name

[<your_sourcetype_account>]
EVAL-account_name = ltrim(account_name,"0")

Following is the query once above knowledge objects created:

index=personnelIndex OR index=accountNameIndex account_name=*
| stats count as MatchCount values(index) as IndexNames by account_name 
| search IndexNames="accountNameIndex" MatchCount=1

[gokadroid]

If the number of entries are less than 10K you can try this to see if it works for you:

index=personnelIndex NOT( [ search index=accountNameIndex 
| rex field=account_name "00(?<personnel>.*)"
| table personnel  ] )
| table personnel