topic Re: Union in Splunk like in MySQL in Splunk Dev

Union in Splunk like in MySQL

andrey2007 — Mon, 28 Sep 2020 13:31:04 GMT

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?

Re: Union in Splunk like in MySQL

kristian_kolb — Thu, 14 Mar 2013 16:40:10 GMT

Could this page be of help to you?

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

Re: Union in Splunk like in MySQL

emiller42 — Fri, 15 Mar 2013 01:28:59 GMT

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

Re: Union in Splunk like in MySQL

lguinn2 — Fri, 15 Mar 2013 02:00:20 GMT

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

Re: Union in Splunk like in MySQL

andrey2007 — Fri, 15 Mar 2013 11:52:45 GMT

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

Re: Union in Splunk like in MySQL

lguinn2 — Sun, 17 Mar 2013 06:18:31 GMT

why can't you just say

(search1) OR (search2)

Re: Union in Splunk like in MySQL

andrey2007 — Thu, 21 Mar 2013 11:34:13 GMT

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 Whats the best way to do it?

Re: Union in Splunk like in MySQL

helge — Thu, 03 Jul 2014 21:02:29 GMT

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.