Archive

Comparing multiple fields from multiple inputs

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 MASTERLOOKUP
| append [|inputlookup append=t COMPARE
LOOKUP ]
| eval MASTERhostname=rtrim(MASTERhostname,".domain.com") | eval MASTERhostname=rtrim(MASTERhostname,".DOMAIN.COM")
| eval COMPAREname=rtrim(COMPAREname,".domain.com") | eval COMPAREname=rtrim(COMPAREname,".DOMAIN.COM")
| eval MASTERname=rtrim(MASTERname,".domain.com") | eval MASTERname=rtrim(MASTERname,".DOMAIN.COM")
| eval ipresult=nullif('COMPAREip','MASTERip') | eval ipresult=if(isnull(ipresult), "MASTER", "Match")
| eval name
result=nullif('COMPAREname','MASTERname') | eval nameresult=if(isnull(nameresult), "MASTER", "Match")
| eval macresult=nullif('COMPAREmacaddress','MASTERmacaddress') | eval macresult=if(isnull(macresult), "MASTER", "Match")
| stats count AS Total, count(eval(ip
result="Match" OR macresult="Match" OR nameresult="Match")) AS Matches
| eval percent=round(Matches*100/Total,4) | table percent Total Matches

0 Karma
1 Solution

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 MASTERLOOKUP | fields + MASTERname MASTERip
| lookup COMPARE
LOOKUP COMPAREip AS MASTERip OUTPUTNEW COMPARE_ip

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

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

View solution in original post

0 Karma

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 MASTERLOOKUP | fields + MASTERname MASTERip
| lookup COMPARE
LOOKUP COMPAREip AS MASTERip OUTPUTNEW COMPARE_ip

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

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

View solution in original post

0 Karma