I think this is simple and I think I see similar questions, but I've failed to implement them for my case and any help is gratefully received.
I have 2 searches that end in two tables with variable numbers of rows.
1)
table, mac, stat1, stat2, stat3, stat4
2)
table, macAddress, hostname
Every mac can be found in a row of macAddress. So I want:
*)
table, hostname, stat1, stat2, stat3, stat4
But I can't work out how. Lookups seem to static, subsearchs seem to only pass information in 1 direction I can append searches but that is just a mess. I haven't had much luck parsing other questions for useful answers. Any inspirations?
| eval mac3=if(isnull(mac),macAddress,mac)
In this situation, data only needs to pass one way, or the other. It sounds like you already know how to get the mac address and hostname into a table. Only the name is going to pass from that side, so we'll make that the right side of the join (or the lookup).
Most of the data is on the stats side. Once you have the stats, you want to retrieve the hostname for any given mac address. So you want a left join with the stats on the left and the mac on the right.
your base search that gives stats
| table mac, stat1, stat2, stat3, stat4
| join type=left mac [ your second search | table, macAddress, hostname | rename macAddress as mac]
| fillnull value="((host name not found))" hostname
| table hostname, mac, stat1, stat2, stat3, stat4
The same thing could be done with a lookup, or, if there are WAY too many hostnames to use a subsearch, then there are a couple of other strategies.
The first other strategy I call "Splunk Stew". It's very weird, for those of use who come from relational databases, to find out that making nice clear swim lanes of cleanly parsed homogenous data isn't always the most efficient way in splunk. It's more eficient in a lot of cases to just throw it all in the pot and stir until it separates.
(your base search that gives stat events, before processing)
OR ( your base search that gives macaddress events before processing)
(then your processing for both, with each eval qualified to make sure
which kind of record you are working with, leaving unavailable fields
from the other one null)
(finally ending with records that have one of two forms -
form 1 - mac stat1 stat2 stat3 stat4 (( nulls in hostname))
form 2 - mac hostname ((nulls in stat1, stat2, stat3, stat4))
)
| stats values(*) as * by mac
| fillnulls value="((unknown))" stat1, stat2, stat3, stat4, hostname
| table hostname, mac, stat1, stat2, stat3, stat4
The last method is a map, which you do NOT want to get into for a large file if there is any other way. it doesn't seem like the case in your situation, so I'll leave that conversation for another day.
I'd just started down the soup path but not much luck with the sub-search and the fillnull value didn't get me there either.
I had to get myself doughnuts when I figured out what did work:
| eval mac3=if(isnull(mac),macAddress,mac)
now I have a col of macs that I could stats and eval to my hearts content
Heh. Glad it worked out for you. I assume that code is somewhere in the splunk soup version?
By the way, that code is equivalent to...
| eval mac3=coalesce(mac,macAddress)
With only one field value in question, the if(isnull(A),B,A)
is almost as easy to read as coalesce(A,B)
, but as soon as you get a third potential place to go for non-null data, coalesce
is much cleaner code.
Please accept an answer so that readers will know your issue is handled.
It's okay to accept your own, but if you do, please make sure to explain what part of your question that answer is the answer to, since a single line of code is not very specific.
Just in case I wasn't clear enough in my pseudocode on the second code example, here's a link to a very specific (smaller) question with working code that demonstrates the method.
I've also tried thinking this through crafting 1 search that has all the fields but not getting anywhere fast. Obv I have a list of all the significant log files and fields but I'm not able to take that next step.