I have squid proxy log that I want to mine for persistent connections from my client workstations to the internet (ie: teamviewer, gotomypc, spyware C&C, etc).
Looking to do a search to determine if clients connect to the same URL or IP address during each 1 hour period of the prior 24 hours.
I have started with this search:
index=proxy | bucket _time span=1h | stats count by _time,user,url | sort - user
Which gives me a list of all urls by user by count, I think I need to then search this query for all occurrences by user by hour for each URL to determine if they are in all 24 hour periods.
Suggestions? Thoughts? Thanks
Give this a try. This will give your all users and hours and url for last 24 hrs where a user-url combination did not appear for all 24 hrs.
index=proxy earliest=-24h@h latest=@h | bucket _time span=1h | stats count by _time,user,url | eventstats dc(_time) as totalHours by user,url | where totalHours<24
You were so close; run your search for
Last 24 hours and tack this onto the end of it:
| eventstats count(eval(count>0)) AS hours_non_zero BY user url | search hours_non_zero>=24