Splunk Search

Help with SQL query to Splunk

davalabih
Engager

Need a similar query for Splunk.

 

SELECT a.[CUSTOMER ID], a.[NAME], SUM(b.[AMOUNT]) AS [TOTAL AMOUNT]
FROM RES_DATA a INNER JOIN INV_DATA b
ON a.[CUSTOMER ID]=b.[CUSTOMER ID]
GROUP BY a.[CUSTOMER ID], a.[NAME]

 

 

0 Karma

somesoni2
Revered Legend

It would be better if you provide information on how your (relevant) data is currently ingested in Splunk, what all fields are available and how they're related. Your SQL query gives the logic, but best solution could only be identified if we know how data is stored in Splunk.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @davalabih,

I suppose that you already have data in two different indexes.

So you have to make a join between the two tables.

You could use the "join" command that in Splunk works in a way similar to SQL, but it's a very slow command so it's better to have a different approach, like the following:

index=RES_DATA OR index=INV_DATA
| stats values(NAME) AS NAME sum(AMOUNT) AS TOTAL AMOUNT BY ID

Ciao.

Giuseppe

0 Karma

davalabih
Engager

@gcusello  Here indexes are same only the resources are different.  If in one resource values is true then other resource values  need to be verified for successful transaction. 

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @davalabih,

ok they are in the same index (good idea!) but how do you differentiate RES_DATA and INV_DATA?

anyway if RES_DATA and INV_DATA are the sourcetypes or another field, you can use the same approach

index=your_index (sourcetype=RES_DATA OR sourcetype=INV_DATA)
| stats values(NAME) AS NAME sum(AMOUNT) AS TOTAL AMOUNT BY ID

I don't understand what do you mean: one resource is true, the other resurce value must be verified.

Ciao.

Giuseppe

 

0 Karma
Get Updates on the Splunk Community!

Uncovering Multi-Account Fraud with Splunk Banking Analytics

Last month, I met with a Senior Fraud Analyst at a nationally recognized bank to discuss their recent success ...

Secure Your Future: A Deep Dive into the Compliance and Security Enhancements for the ...

What has been announced?  In the blog, “Preparing your Splunk Environment for OpensSSL3,”we announced the ...

New This Month in Splunk Observability Cloud - Synthetic Monitoring updates, UI ...

This month, we’re delivering several platform, infrastructure, application and digital experience monitoring ...