Splunk Search

DBXQuery where not in index data

rahmatn
Explorer

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

 

Labels (2)
Tags (1)
0 Karma
1 Solution

rahmatn
Explorer

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

View solution in original post

0 Karma

rahmatn
Explorer

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

View solution in original post

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!