Splunk Search

How to combine my 2 reports?

chaoservices
Explorer

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?

0 Karma
1 Solution

chaoservices
Explorer

| eval mac3=if(isnull(mac),macAddress,mac)

View solution in original post

0 Karma

chaoservices
Explorer

| eval mac3=if(isnull(mac),macAddress,mac)

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

chaoservices
Explorer

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

DalJeanis
SplunkTrust
SplunkTrust

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.

https://answers.splunk.com/answers/509188/what-to-do-when-appendcols-command-cant-handle-lar.html#an...

0 Karma

chaoservices
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...