topic How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list? in Splunk Search

How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

Log_wrangler — Tue, 29 Sep 2020 20:18:30 GMT

I am trying to monitor an application where remote users with different GeoLoc(s) and unique sourceIP(s) login and interact with the application.

In an effort to monitor behavior for possible credential theft/ inappropriate access, I am looking to create a historic sourceIP and GeoLoc list, and compare last 24 hours of logins against the list.

so far I have been testing

index=waf sourcetype=waf_logs "a few key words" | stats values(sourceIP) values(GeoLoc) by userID | outputlookup append=f historic_login_list.csv (I run this for last 30 days but not include current day).

The part I am fumbling with is how to check the list with a search for past 24 hours. I am not getting the correct " |lookup output" or there is something else wrong.

I will keep working on this in parallel, however if someone has a better way to do this, or experience with this, please advise.

Thank you

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

nadlurinadluri — Mon, 09 Jul 2018 23:17:28 GMT

So if my understanding is correct, you are trying to compare last 30days of data (GeoLoc,sourceIP) with current date?

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

renjith_nair — Tue, 10 Jul 2018 03:38:19 GMT

Hi @Log_wrangler ,
From your search, you are updating multi valued fields to lookup file and trying to match against that. Instead of stats, you could use table to print ouft your fields

index=waf sourcetype=waf_logs "a few key words" |table sourceIP ,GeoLoc ,userID |dedup sourceIP ,GeoLoc ,userID | outputlookup append=f historic_login_list.csv

Once you have the lookup table ,then you could find the difference by

your base search  NOT [inputlookup historic_login_list.csv |fields sourceIP ]

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

Log_wrangler — Tue, 10 Jul 2018 13:46:08 GMT

Thank you for your reply.

I like your approach to search based on NOT on the input csv.

In theory I thought that would work, the format is showing userID and address address address, so the matching is off.

To clarify each userID can have multiple sourceIP(s). I need to know if a pair (userID, sourceIP) matches the historic list. Currently the output of the list is userID, sourceIP sourceIP sourceIP, which causes the problem.

I hope that makes sense.

Originally I was trying to create fields userID sourceIP flag on the historic list, and then use the flag values (e.g. flag = 1) to filter a yes or no on_list... but I could not get it to work right.

I would need to dedup pairs somehow.

Thank you

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

Log_wrangler — Tue, 29 Sep 2020 20:24:02 GMT

Using your "NOT" logic... I think this is working correctly, please advise

To create the list (historic time period not including last 24hrs)

index=waf sourcetype=waf_logs "a few key words" |table sourceIP userID | outputlookup append=f historic_login_list.csv

This creates duplicates which make the list longer, but until I can figure out how to dedup the pairs of userID and sourceIP, it will do, until it gets too big and crashes something

To check the list (last 24 hrs)

index=waf sourcetype=waf_logs "a few key words" | table sourceIP userID | search NOT [|inputlookup historic_login_list.csv |fields sourceIP userID]

This seems to work. Is there a better way? Any 2nd opinion greatly appreciated.

Thank you

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

renjith_nair — Tue, 10 Jul 2018 14:13:40 GMT

Just to clarify , what's the output of your historic SPL - userID, sourceIP sourceIP sourceIP ? Can it be constrained to userID,sourceIP ?

To make the search working, try this

" your base search running for last 24  hours" |fields userID, sourceIP|search NOT [inputlookup historic_login_list.csv |fields userID, sourceIP ]

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

woodcock — Tue, 10 Jul 2018 17:09:10 GMT

Like this:

| dedup userID sourceIP

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

Log_wrangler — Tue, 29 Sep 2020 20:24:26 GMT

I want to give you 5 points for helping me out with the subsearch-filter logic, i.e. NOT [inputlookup historic_logon_list.csv....]

as I only have so few points, the real value is about 500 pts.

Thank you

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

renjith_nair — Wed, 11 Jul 2018 01:12:11 GMT

@Log_wrangler , no worries 🙂 , you could eliminate the duplicates using dedup ie. dedup userID, sourceIP

Re: How to outputlookup historic IP activity / userID and create an alert that will occur if the IP address is not on the historic IP activity list?

woodcock — Wed, 11 Jul 2018 17:34:06 GMT

Just click on the ^ to UpVote helpful answers and comments (this gives points but does not cost you points).