Splunk Search

Comparing multiple fields from multiple inputs

jmich0823
Engager

So I've found many questions that are similar to what I'm trying to do here but not quite the same and I've not been able to get any of them to work right for me. Apologies if the answer is out there and I just can't put it together. Hope someone can enlighten me on the best way to accomplish this:

I have two inputlookups that potentially have matching data (hostname, ip, mac). input1 is the 'master' I want to check against and I'm trying to get a count on how many records have hostname OR ip OR mac and then give me basic statistics about it. Basically answer the question 'are any of these in the master?'.

Field names are different in each input and hostname formats vary somewhat.

If input1.field1 = inputA.fieldA then A=Match
If input1.field2 = inputA.fieldB then B=Match
If input1.field3 = inputA.fieldC then C=Match

-Count all events from input1 = Total
(input1 is the 'master')
-Count where A OR B OR C=Match = ItsThere
(1 Match count per record regardless of how many matches it had)
-Percent of how much of input1 have matches from inputA = percentage

Also note that input1 has ~225,000 records and the other has 95,000+ records. When I got this as close as I think I could to 'working' it was capping my output to 50k. I increased various limits and added maxout to append but its still an issue. I'm hoping I can do this matching logic more efficiently to avoid this altogether.

I feel like there is a much better way to do this instead of using null checking and I'm not really trusting my results anyhow, seems like the logic is checking against the values in the fields of its own record, not all the other records - which is what it needs to do.

| inputlookup MASTER_LOOKUP
| append [|inputlookup append=t COMPARE_LOOKUP ]
| eval MASTER_hostname=rtrim(MASTER_hostname,".domain.com") | eval MASTER_hostname=rtrim(MASTER_hostname,".DOMAIN.COM")
| eval COMPARE_name=rtrim(COMPARE_name,".domain.com") | eval COMPARE_name=rtrim(COMPARE_name,".DOMAIN.COM")
| eval MASTER_name=rtrim(MASTER_name,".domain.com") | eval MASTER_name=rtrim(MASTER_name,".DOMAIN.COM")
| eval ip_result=nullif('COMPARE_ip','MASTER_ip') | eval ip_result=if(isnull(ip_result), "MASTER", "Match")
| eval name_result=nullif('COMPARE_name','MASTER_name') | eval name_result=if(isnull(name_result), "MASTER", "Match")
| eval mac_result=nullif('COMPARE_mac_address','MASTER_mac_address') | eval mac_result=if(isnull(mac_result), "MASTER", "Match")
| stats count AS Total, count(eval(ip_result="Match" OR mac_result="Match" OR name_result="Match")) AS Matches
| eval percent=round(Matches*100/Total,4) | table percent Total Matches

0 Karma
1 Solution

jmich0823
Engager

Eventually figured out what works for me here. Below is the basic logic minus the rtrim stuff and additional fields, but I've done it with several fields and work as desired.

| inputlookup MASTER_LOOKUP | fields + MASTER_name MASTER_ip
| lookup COMPARE_LOOKUP COMPARE_ip AS MASTER_ip OUTPUTNEW COMPARE_ip

repeat above for _name, _mac, and any other fields needed

| fillnull COMPARE_ip value="No Matches"
| stats count AS "MASTER", count(eval((MASTER_IP=COMPARE_ip ))) AS COMPARE
| transpose

View solution in original post

0 Karma

jmich0823
Engager

Eventually figured out what works for me here. Below is the basic logic minus the rtrim stuff and additional fields, but I've done it with several fields and work as desired.

| inputlookup MASTER_LOOKUP | fields + MASTER_name MASTER_ip
| lookup COMPARE_LOOKUP COMPARE_ip AS MASTER_ip OUTPUTNEW COMPARE_ip

repeat above for _name, _mac, and any other fields needed

| fillnull COMPARE_ip value="No Matches"
| stats count AS "MASTER", count(eval((MASTER_IP=COMPARE_ip ))) AS COMPARE
| transpose

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...