topic Re: Search all the login events from a location in Splunk Search

Search all the login events from a location

jarapally — Mon, 19 Mar 2018 17:31:28 GMT

Need to run a report where the user is supposed to work remotely for 110 days in any given 365 days. The 365 days is a rolling window. Within any 365 the user is supposed to work only 110 days. Can someone help me with the logic

Re: Search all the login events from a location

tiagofbmm — Mon, 19 Mar 2018 17:47:25 GMT

Hey

search *login* earliest=-365d
| eval unique_date= date_mday +"/" + date_month
| stats count by unique_date, user
| stats count by user
| eval crossed_threshold=if(count>110,"True","False")

If the result of that search is greater than 110 for any user in a 365 days period, then he crossed your threshold.

Could this sketch be according to your needs?

Re: Search all the login events from a location

jarapally — Mon, 19 Mar 2018 17:51:18 GMT

We are running the report every 30days so using a lookup to store all the remote logins. Can you modify the search based on that

Re: Search all the login events from a location

tiagofbmm — Mon, 19 Mar 2018 17:53:48 GMT

Sorry I'm not following now. Do you want to store the results of this search in a lookup? Or do you have a lookup with something else that you want to include in the search?

Re: Search all the login events from a location

jarapally — Mon, 19 Mar 2018 17:56:20 GMT

We are not running the search for 365 days but every 30 days and sending the remaining days left within the 365 rolling window.

Re: Search all the login events from a location

tiagofbmm — Mon, 19 Mar 2018 18:09:50 GMT

Ok so then just change the earliest time you are looking at it:

 search *login* earliest=-30d
 | eval unique_date= date_mday +"/" + date_month
 | stats count by unique_date, user
 | stats count by user
 | eval crossed_threshold=if(count>110,"True","False")

Re: Search all the login events from a location

jarapally — Mon, 19 Mar 2018 19:42:19 GMT

But the user is allowed to work for 110 days within any given 365 days. And we run this report every 30 days to send them the remaining days that are left

Re: Search all the login events from a location

tiagofbmm — Mon, 19 Mar 2018 19:46:42 GMT

Sorry, I think I finally understood what you intend:

  search *login* earliest=-365d
  | eval unique_date= date_mday +"/" + date_month
  | stats count by unique_date, user
  | stats count by user
  | eval remaining_days = 110-count

Let me know if that is what you want to send to the users

Re: Search all the login events from a location

tiagofbmm — Wed, 21 Mar 2018 17:26:22 GMT

Please let me know if the answer was useful for you. If it was, accept it and upvote. If not, give us more input so we can help you with that