Is it possible to use join with the output of a lookup in a search query?

[ewanbrown]

Hi,

I have a search query like the one below

index=beacon BeaconType=userevent type=addonselected    | join INID TEST  [search index=clickstream   | eval Message=max (pageid)   | lookup TGIF Message  | eval new_add=Message." , ".'Location Number' ] | chart count by Message providername

I can join ok on INID and TEST
I The chart the results ok when doing it by Message providername
The lookup works fine when I run the inner query on its own

but when I try to show the output as

chart count by new_add providername

I do no tget any results

Do you know if it is possible to use a lookup in a join? If so, what am I doing wrong!

Thanks

[woodcock]

The only possible explanation is that field new_add does not exist in BOTH search's datasets; double-check your spelling/punctuation of all fields. It looks like it is supposed to exist in the first/outer search's dataset and you are trying to create it for the second dataset but perhaps you are trying to create it for both datasets and in that case, you forgot to do so for the first/outer dataset! Using coalesce should work for either case. Also, Try to avoid join so how about this:

 index=beacon BeaconType=userevent type=addonselected OR index=clickstream | stats values(*) AS * by INID TEST | eval Message=max (pageid) | lookup TGIF Message | eval new_add=coalesce(new_add,Message." , ".'Location Number') | chart count by new_add providername

[somesoni2]

The syntax in the subsearch for join seems incorrect.

...| eval Message=max (pageid)....

You can't use aggregate functions with eval. What is the requirement here and what all fields should be returned by the join subsearch?

[ewanbrown]

Hi

Using max with eval is supported here: http://docs.splunk.com/Documentation/Splunk/6.1.4/SearchReference/Eval so I don't think that is the problem

Thanks though

[aweitzman]

In eval, max simply takes any number of numerical arguments and returns the maximum value, which is not the same thing as an aggregate calculation. So in the clause that you've provided, it will simply take the value of pageid and fill the Message field with it. If that's what you want, then that's fine, although it seems unnecessary - you could just do eval Message=pageid for that.

If you did intend to do an aggregate calculation of the maximum value of pageid across all your results, I think what you want there is eventstats max(pageid) as Message. eventstats does an aggregate calculation for you, and then adds the result to each event, so no matter which result you pull out later, there will be a field Message with the maximum pageid in it.

But none of that will cause your search to fail, it'll just get you different results.

[ewanbrown]

For my actual query, I did want the maximum pageid, (as it is actually an array of pageids I get not just one, I just simplified the query when posting it on here, I think that confused things!)

Thanks for your help and the info on eventstats, that is useful I know!

[ndoshi]

A lookup adds fields to your search. It enriches your data. Unless stated in the docs, I believe it should still add fields that are from the lookup. To debug, can you instead of using chart, use table:

table new_add, Message, providername,..

and see what fields are there? You can also try to show every field that is not from Splunk:

|fields - splunk*, date*, time*, source, host, sourcetype, eventtype, linecount,eventcount, punct, index, tag* _raw|table _time *

[ewanbrown]

Thanks for the tip.

That helped, when I do | fields I get a UTF-8 error, so I think the problem is with the lookup csv file