How to search for IPs on our network that have not logged in within a 30 day period against a lookup table of IPs?

[troconn]

I have a requirement to find which IPs on our network are not logging in, no activity for a 30 day period. I can run a query to show me "activity" levels, but I can't figure out how to get it to show me "zero" usage, i.e., which IPs are not logging on. I have a lookup table with all my IPs listed, but I can't figure out how to write a query that will show me which ones from the list are not returning results. Any thoughts?

[somesoni2]

It'll be like this. Share your current query for more accurate suggestions.

your current query to show "activity" level with field ip_address | eval status=1
| append [| inputlookup yourlookuptable.csv | tablle ip_address | eval status=0]
| stats max(status) as status by ip_address | where status=0

[troconn]

Thank you! I "think" this is working, but this query only returns the "zeros". The zero use sites seem to fall in line with what I'd expect, but my goal is to show a consolidated list of sites with their usage. Can you tell me how I could modify this query so I also get the rest of the results from the other IPs as well?

[somesoni2]

If you remove the where clause in the end, it'll give a record for each IP address. You can then add an eval-if command to set appropriate status (if 0 then not reporting or similar).

[troconn]

So, it seems like I'm close, but I can't get it to output IP usage from zero to X. The below query gets me logins (event type 6152) from 1 to infinity, but no zero site usage.

lines 2 through five extract the IP from the query results and format it down to the first three octets.

When I add your query to the end of this one it no longer does the lookup against the csv file, BUT it does extract the correct number of zero usage sites from the list (I've verified it manually), so that part works.

What it doesn't do is do the lookup against the .csv and output the entire list with the 0-999 login information. I'm really new at this and sadly not a computer scientist... so I'm not sure where to go from here (for instance your suggestion to add an eval-if command... no idea what that means) and simply removing that where clause causes all IPs to report "1" login.

Here is the query I'm using now to get the login data... I know it's ugly.

index="client_index" AND Event_Type=6152
| eval new=substr(audit_filename,16,14)
| eval ip=mvindex(split(new,"_"),0)
| eval mvip=split(ip,".")
| eval site_ip_range=mvindex(movie,0).".".mvindex(mvip,1).".".mvindex(movie,2)
| stats count By site_ip_range
| sort no limit site_ip_range
| lookup site usage.csv site_ip_range output Site_Name Site_Number