Splunk Search

How to write an efficient search to compare new IPs against the previous 30 days?


Disclaimer: I know the search below is ugly, its based on several examples including the exploring splunk book. I was trying to get an example from splunk .conf working http://www.mensk.com/detecting-bank-accounts-takeover-fraud-cyberattacks-with-splunk-part3-the-advan... but failed miserably.

Desired Results:

  1. When a user performs a specific admin function, let me know if that IP address was ever used with that user account in the last 30 days.
  2. If it was NOT used, compare the IP address against all activity for all groups and let me know which groups used that IP address
  3. Compare the Account Number found to ones used by other groups.

I have scenario 1 working (below) it is just really expensive. Even if I can just improve performance on that, it would be a big help.

The search below works, however I need to compare this results against 2 other searches (

Compare Returned IPs against IPs used by different groups last 30 days:

index=pssql sourcetype=logins IPAddress=(questionable_IP) | eval iDate=strftime(_time, "%m/%d/%Y")
| stats values(Group_Name) values(User_Name) values(iDate) by IPAddress

Compare returned Account Number against Account Numbers used last 30 days by different groups:

index=pssql sourcetype=UserActivity Account_Number=(questionable_account)

Working search:

index=pssql sourcetype=UserActivity Message="Admin Function*" earliest=-2d latest=now 
| eval iDate=strftime(_time, "%m/%d/%Y %H:%M:%S")
| stats values(IPAddress) as lastIP values(Message) as iMessage  values(iDate) as iDate 
   values(Account_Number) as Account_Number values(Routing_Number) as Routing_Number by Group_Name
| sort lastIP
| append [ search
   index=pssql sourcetype=logins earliest=-30d latest=-2d
   | eval aDate=strftime(_time, "%m/%d/%Y")
   | eval dateIP=IPAddress." - ".aDate
   | stats values(IPAddress) as PreviousIPs values(User_Name) as User_Name 
        dc(IPAddress) as ipCount  values(dateIP) as dateIP by Group_Name
   | where ipCount>1
   | fields PreviousIPs,User_Name,ipCount,PrevTime,Group_Name,_time,dateIP
| stats values(lastIP) as lastIP dc(lastIP) as lastIPCount values(User_Name) as User_Name 
    values(dateIP) as dateIP values(iMessage) as iMessage
    values(PreviousIPs) as PreviousIPs dc(PreviousIPs) as prevIPCount values(Account_Number) as Account_Number 
    values(iDate) as iDate values(Routing_Number) as Routing_Number by Group_Name
| where lastIPCount>0 AND prevIPCount>0
| sort lastIP
| eval Match=if(lastIP!=PreviousIPs,"NO","YES") 
| where Match="NO"
| table Group_Name, User_Name, iDate, lastIP, dateIP, Routing_Number, Account_Number
0 Karma
1 Solution




I have read that multiple times, honestly I get lost how to apply that to my specific use case. Any hints?

0 Karma


Here is a skeleton of how, I think this could work without subsearches. NOt sure I understand the 3 scenario, so this is only for 1&2. This is to only get you started...

index=pssql (sourcetype=UserActivity Message="Admin Function*") OR sourcetype=logins earliest=-30d latest=-2d | eventstats earliest(_time) as previous latest(_time) as current by group_name ip | eval seenbefore=if(current<=relative_time(now(), "-2d@d"), "yes", "no") | eventstats values(eval(if(seenbefore="no", group_name", null())) as groupsusingip by ip 

I think this should give you, for each event, if it was seenbefore, if not, groups using the ip. Hopefully, I'm not waay off base 🙂

0 Karma


Thank you, that makes sense.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!