topic Remove record from Query1 if present in Query2 in Splunk Search

Remove record from Query1 if present in Query2

ngautam760 — Wed, 06 Apr 2022 14:00:45 GMT

I have 2 Splunk Queries

First Query will return the Employee ID of the Active and Retired Employees.

Second Query will return the Employee ID of the retired Employees.

I want to merge both the queries to get the result of only the Active employees.

by removing the Retired_Employee_ID from the list of Employee_Id

Query1)

index=employee_data | rex field=_raw <regular expression used to extract Employee_ID>offset_field=_extracted_fields_bounds | table Employee_Id

Query2)

index=employee_data | rex field=_raw <regular expression used to extract Retired_Employee_ID>offset_field=_extracted_fields_bounds | table Retired_Employee_ID

Re: Remove record from Query1 if present in Query2

gcusello — Wed, 06 Apr 2022 12:29:23 GMT

Hi @ngautam760,

please try soimething like this:

index=employee_data Employee_ID="*" NOT Retired_Employee_ID="*" | table Retired_Employee_ID

Ciao.

Giuseppe

Re: Remove record from Query1 if present in Query2

ITWhisperer — Wed, 06 Apr 2022 12:49:37 GMT

I think Giuseppe meant

index=employee_data Employee_ID="*" NOT Retired_Employee_ID="*" | table Employee_ID

Re: Remove record from Query1 if present in Query2

gcusello — Wed, 06 Apr 2022 12:50:03 GMT

Hi @ngautam760,

if you do't have the fields Employee_ID and Retired_Employee_ID, you have two choices:

create a permanent field extraction using your regexes, so you'll have both the fields and you can use my regex,
insert the filters on the fields after the rex extraction, something like this:

index=employee_data | rex "Employee_ID_extraction" | rex "Retired_Employee_ID" | search Employee_ID="*" NOT Retired_Employee_ID="*" | table Retired_Employee_ID

Ciao.

Giuseppe