TheAssetMgr
Board Regular
- Joined
- Nov 8, 2011
- Messages
- 63
I'm trying to identify possible security issues by becoming aware of the number of account lockouts, by user name, over a given period of time. If I scroll through the pivot I've created (example below) I can see where User A's account has been locked out a total of 51 times in the 5 days I have listed. My data will always be a 5-day rolling window if that helps. I'm not worried about User B because he has only locked himself out 1 time in the last 5 days.
<colgroup><col><col></colgroup><tbody>
</tbody>
My problem statement is: I need to see which user accounts get locked at least once per day and more than 3 days in a 5 day period.
So for example if User A's account got locked out 5 times on 8/4 but 0 times on 8/5 through 8/10 I don't care. But if User A's account gets locked out once on 8/4, once on 8/6 and once on 8/7 I'd want to know about it. Or, if User A's account got locked out twice on 8/5, 5 times on 8/6 and once on 8/10 I'd want to know about that too. Hope I'm making sense here.
I have this data in a worksheet and I used that worksheet to drive the pivot I provided a sample of above. I'm not sure what the output would look like but ideally I'd want to be made aware of the Country (Philippines in this case), the account name (User A in this case) and the dates and counts their accounts got locked out that tripped the trigger. Maybe just a copy/paste from the pivot would work as below? I'm open for ideas there too.
<tbody>
</tbody>
Is there a formula I could use to analyse the data set or have I drifted into the VBA scripting realm?
If I need to be more clear on something please let me know - thanks!
Philippines | 52 |
User A | 51 |
8/4/2015 | 24 |
8/5/2015 | 10 |
8/6/2015 | 4 |
8/7/2015 | 12 |
8/10/2015 | 1 |
User B | 1 |
8/6/2015 | 1 |
<colgroup><col><col></colgroup><tbody>
</tbody>
My problem statement is: I need to see which user accounts get locked at least once per day and more than 3 days in a 5 day period.
So for example if User A's account got locked out 5 times on 8/4 but 0 times on 8/5 through 8/10 I don't care. But if User A's account gets locked out once on 8/4, once on 8/6 and once on 8/7 I'd want to know about it. Or, if User A's account got locked out twice on 8/5, 5 times on 8/6 and once on 8/10 I'd want to know about that too. Hope I'm making sense here.
I have this data in a worksheet and I used that worksheet to drive the pivot I provided a sample of above. I'm not sure what the output would look like but ideally I'd want to be made aware of the Country (Philippines in this case), the account name (User A in this case) and the dates and counts their accounts got locked out that tripped the trigger. Maybe just a copy/paste from the pivot would work as below? I'm open for ideas there too.
Philippines | 52 |
User A | 51 |
8/4/2015 | 24 |
8/5/2015 | 10 |
8/6/2015 | 4 |
8/7/2015 | 12 |
8/10/2015 | 1 |
<tbody>
</tbody>
Is there a formula I could use to analyse the data set or have I drifted into the VBA scripting realm?
If I need to be more clear on something please let me know - thanks!