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
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
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?
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
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.
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!
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 *
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