Help with SQL query to Splunk

[davalabih]

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]

[somesoni2]

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.

[gcusello]

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

[davalabih]

@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. 

[gcusello]

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