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!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...