DBXQuery where not in index data

[rahmatn]

Hi Splunkers,

Need your help, i have DBXQuery like this :

| dbxquery connection="myconn" query="sdbxquery connection=monsplunk_ibank query="select CREATED_BY, BILLER_CUST_ID, BILLER_NAME, TRX_AMT as AMOUNT from "APPDB"."TBL_APP_PYMT_PURCHASE""

and i want to filter the data where CREATED_BY not in :

index=mobile_purchase_idx  | fields CREATED_BY

I have tried using join type inner but the result still show all data from the index, not the negative, this is my query :
| dbxquery connection=monsplunk query="select CREATED_BY, BILLER_CUST_ID, BILLER_NAME, TRX_AMT as AMOUNT from "APPDB"."TBL_APP_PYMT_PURCHASE""
| join type=inner CREATED_BY
[| search index=mobile_purchase_idx latest=now | fields CREATED_BY]
| table CREATED_BY, BILLER_CUST_ID, BILLER_NAME

[rahmatn · Accepted solution]

hi all,

Thank you for allow your time to see this question, finally i found the query that i'm looking for.

I have to add another field from index, if it empty then it must be negative data that will show on result, and change join type to outer :
| dbxquery connection=monsplunk query="select CREATED_BY, BILLER_CUST_ID, BILLER_NAME, TRX_AMT as AMOUNT from "APPDB"."TBL_APP_PYMT_PURCHASE""
| join type=outer CREATED_BY
[| search index=mobile_purchase_idx latest=now | fields CREATED_BY, Note]
|where isnull (Note)
| table CREATED_BY, BILLER_CUST_ID, BILLER_NAME

Thanks all