Splunk Dev

Union in Splunk like in MySQL

andrey2007
Contributor

Hello, i need do something like this

select t_resources.t_name, t_users.t_nick
from t_resources
left join t_users on t_users.t_id = t_resources.t_userid
union
select t_resources.t_name, t_users.t_nick
from t_resources
right join t_users on t_users.t_id = t_resources.t_userid

I know there is only left join in Splunk, i can get right join from left by exchanging names of tables.
How can i get union operator?

Tags (1)

lguinn2
Legend

There is probably an even easier Splunk solution, but it is hard to see when you express the problem in SQL. If you can express the problem in words, we may be able to break through to a different, more "Splunk-like" point-of-view.

For example, if I say: I have a table that associates users with resources. I also have a record of which users were logged-in. How can I see which resources were in use over a period of time?

My answer would be: put the user-to-resource mapping in a lookup table. Then run a search like this:

source="myuserrecord" 
| lookup resource-lookup userId
| stats count by resourceId

Or, you could use the join command or maybe the append command or even the union command. But without any background on your data, it's hard to give good advice. (It would also be helpful to know how much data you have. Do you need to run this search over thousands of events or billions of events, etc.)

andrey2007
Contributor

I need to group events by 3 filelds ip,login,city

user1 192.168.1.1 London field4...fieldn
user1 192.168.1.1 London field4...fieldn
user1 192.168.1.1 London field4...fieldn

user1 2.2.2.2 London field4...fieldn
user1 2.2.2.2 London field4...fieldn
....
user10 4.4.4.4 NY field4...fieldn
user10 4.4.4.4 NY field4...fieldn
user10 4.4.4.4 NY field4...fieldn
after this i need to calculate custom fields INSIDE EVERY GROUP, for example sum of events in group with field4s value=5, minimal value of fieldn where field4=12 and etc. in one table-report
What
s the best way to do it?

0 Karma

lguinn2
Legend

why can't you just say

(search1) OR (search2)

0 Karma

andrey2007
Contributor

the final goal of my question is to know how can i realize in splunk full outer join,

i made something like this
(search1) join by field [search2] | append [(search2) join by field [search1]]| dedup field

it works correct, but it looks terrible and not optimal.
Search on 200 events takes near 7 seconds, but this search is for 3000 events

0 Karma

emiller42
Motivator

The set command has the ability to union two sub searches:

| set union [search foo] [search bar]

So just figure out the base searches for the two queries you're trying to do separately, then throw them in the subsearch brackets above.

Set Reference

However, I'm not sure why the SQL you presented needs a union in the first place. I'm assuming the goal is to get all records from t_resources and t_users, matching where possible, and retaining all records from either table that do not have a match. That can be simplified to a full outer join in mySQL, and appears to be possible with the Splunk join command:

... | join type=outer field [search foo]

Join Reference

helge
Builder

Splunks outer join is not the same as a union. It takes all events from the main search and adds matching fields from the subsearch. BUT it discards events from the subsearch that are not matched by any event in the main search.

0 Karma

kristian_kolb
Ultra Champion
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...