How can I detect when a new IP is used by a user ?

[ctaf]

Hello,

I have a search that find all the IPs used by each user. I would like to run this search periodically so that if a new IP is used by a user, I receive an alert. I was think about storing the results in a CSV and comparing the result of the following search with that CSV. But is it efficient ?

Also, I am not sure how to save it as a CSV since there can be several IPs for one user:

user1, ip1, ip2
user2, ip1
...

Any ideas ?

Thanks

EDIT:

I managed to progress. I used a join with a subsearch:

... distinct_count(src_ip) values(src_ip) AS IP earliest=-1d@d latest=@d by user | rename distinct_count(Web.src) AS count | fields user, count, IP | join user [ ... distinct_count(src_ip) values(src_ip) AS IP_old from earliest=-22d@d latest=-1d@d by user | rename distinct_count(Web.src) AS count_old | fields user, count_old, IP_old] | makemv delim=" " IP_old | where IP!=IP_old

But I don't know how to compare the multi-values field "IP" and the multi-values field "IP_old". I would like to keep only the event where IPs in "IP" are new compared to "IP_old".

Any ideas ?

[woodcock · Accepted solution]

You can do this:

First create a CSV for what you have seen so far:

... | stats dc(src_ip) AS numIPs values(src_ip) AS IPs BY user | nomv IPs | outputcsv IPsByUser.csv

Next, use this to compare:

 .... table src_ip user | eval type="NEW" | append [| inputcsv IPsByUser.csv | makemv delim=" " IPs | mvexpand IPs | rename IPs as src_ip | eval type="OLD"] | stats dc(type) AS numTypes values(*) AS * BY src_ip user

The above base search gives the fully merged set (full join). To get what you need, add this to it:

| where numTypes="1" AND type="NEW"

[kv]

Hi Woodcock,

Could you explain how it detects the new IP using your command? I can view the results but am not understanding the working part of the command.

Thanks In advance

[ctaf]

Oh it works great !

Just a remark, you mistype "user" at the end of the 2nd command (you typed "host").

And I changed "table src_ip user" by tstat command to use summary data.

Thank you very much !

[woodcock]

I fixed that mistake; thanks for pointing it out to me.