Prevent duplicate IP lookups by verifying it hasn't been used previously

[Nextbeat]

Our department has created a Splunk integration that performs API lookups against IPQualityScore.  One of our searches was augmented with the returned data by adding extra fields for context (fraud_score, recent_abuse, vpn, tor, etc.).  Unfortunately, the integration doesn't keep the results, so I used outputlookup to store them.  While we have a reasonable amount of API queries, I was trying to figure out a way to check if an IP was previously used, append the results and prevent a duplicate lookup if it was.  Otherwise, perform the lookup and append the results.

This was the query that was used:

index=*_auth sourcetype="azure:aad:signin" userAgent=CBAInPROD riskEventTypes_v2{}=* 8.8.8.8  
| rename authenticationDetails{}.succeeded as loginStatus,authenticationDetails{}.authenticationMethod as mfaAuthMethod,authenticationDetails{}.authenticationMethodDetail as mfaAuthDetail,status.additionalDetails as mfaResult,status.failureReason as failureReason,location.city as city,location.state as state,location.countryOrRegion as country,ipAddress as SourceIP,location as Location,userAgent as UserAgent,appDisplayName as Application,riskState as RiskState,riskEventTypes_v2{} as RiskEventType,riskLevelAggregated as RiskLevel,riskLevelDuringSignIn as SignInRisk,conditionalAccessStatus as Status 
| eval mfaAuthDetail=if(mfaAuthDetail="","-",mfaAuthDetail),city=if(city=="","N/A",city),state=if(state=="","N/A",state),country=if(country=="","N/A",country),Location=city.", ".state.", ".country,User=if(isnull(userDisplayName),userPrincipalName,userDisplayName)
| fillnull value="-" 
| stats count by User,SourceIP,Location,Application,UserAgent,RiskEventType,RiskState,RiskLevel,SignInRisk,Status 
| fields - count 
| lookup ipqs clientip as SourceIP 
| outputlookup append=true override_if_empty=false ipqs.csv
| rename bot_status as Bot,city as City,country_code as Country,fraud_score as Fraud_Score,latitude as LAT,longitude as LONG,mobile as Mobile,proxy as Proxy,recent_abuse as Abuse,success as Success,tor as TOR,vpn as VPN 
| table User,SourceIP,Location,Application,UserAgent,RiskEventType,RiskState,RiskLevel,SignInRisk,Status,
    Bot,City,Country,Fraud_Score,LAT,LONG,Mobile,Proxy,Abuse,Success,TOR,VPN 
| sort 0 User

[Richfez]

There's enough complexity that I'm not sure which is the right answer.

1) You could possibly switch the stats, lookup and outputlookup around.  They're currently in the order I just listed them (we'll ignore that "fields" to dump the count), but if you did them instead as lookup, stats then output lookup, you could rework those just a bit and make them dedup before you write them back out.

But I'm not quite convince that will work (complexity, and my old brain is tired at the moment).

2) If it were me, I'd not try to do this in here.  I'd separate out a new saved saved that does  inputlookup -> massage/dedup/stats/whatever - >outputlookup, and  schedule that to run for every 5 minutes or so.

I hope that helps, if nothing else I just bumped this thread.  🙂

Happy Splunking,

Rich