Splunk Search

DBXQuery where not in index data

rahmatn
Path Finder

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
Path Finder

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
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) v3.54.0

The Splunk Threat Research Team (STRT) recently released Enterprise Security Content Update (ESCU) v3.54.0 and ...

Using Machine Learning for Hunting Security Threats

WATCH NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more for ...

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...