All Apps and Add-ons

How can i convert SQL query to Splunk search?

joy76
Path Finder

SQL Query :
SELECT A.a0,A.a1, A.a2, A.a3 FROM TableA AS A, TableB B
WHERE (A.a1=B.b OR A.a2=B.b OR A.a3=B.b)

Splunk search : ???
index=A | fields a0,a1,a2,a3 | join b [search index=B | fields b]

How can i convert it?

Tags (1)

southeringtonp
Motivator

In addition to the suggestions Nick has already provided, you may wish to look here for various example scenarios:

http://www.innovato.com/splunk/SQLSplunk.html

jonathon
Path Finder

Is there an updated link for innovato? This link is desd.

0 Karma

ChrisG
Splunk Employee
Splunk Employee

There is some SQL/SPL information in the docs, if you haven't seen it yet:

http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/SQLtoSplunk

0 Karma

sideview
SplunkTrust
SplunkTrust

Although this is not what new users expect, Splunk searches that are analogous to SQL joins usually dont need an actual join command. You can usually do these things with a simple OR, sometimes sprinkled with a little eval to normalize things. In fact there are lots of great reasons not to use the join command.

If you were just joining A.a1 to B.b, you'd use searches like this:

index=A OR index=B | eval myNormalizedIdField=if(index=="A",a1,b) | stats values(someFieldFromA) last(someFieldFromB) sum(someNumericField) by myNormalizedIdField

or

index=A OR index=B | eval myNormalizedIdField=if(index=="A",a1,b) | transaction myNormalizedIdField

But for your specific example where you want to join A.a1 to B.b, OR A.a2 to B.b OR A.a3 to B.b, then you'll need a little more search language to normalize. I think it'll look more like this:

(index=A OR index=B) | eval myNormalizedIdField=if(index=="A",a1+"-"+a2+"-"+a3,b) | makemv delim="-" field="myNormalizedIdField" | transaction myNormalizedIdField

or replace that transaction with stats, depending on what you're trying to do with the joined set.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...