Automic Workload Automation

Hello everyone. We have GDPR requirement, to report all/failed login attempts on weekly basis. I am trying to build a solution based on DB query, instead of going through logs. Would anyone of you suggest a DB query, to get login statistics out of AE database? Thank you very much for your help!

Login attemps are treated much like job runs, and are logged in the AH table.

This can be your start point:

select * from AH where AH_OTYPE='USER';

You need to join AH_OH_IDNR to get the user name, AH.AH_OH_IDNR on OH.OH_IDNR.

I suspect that AH_MSGNR=0 is a successful login, and AH_MSGNR=3210 a failed login. You'd have to test that though. Furthermore, failed logins will only be logged if the user name is correct. Invalid logins attempts with invalid user names (e.g. your typical brute force attack) will not be logged at all.

Hope this helps.

You could use the Utility DB Revision Report

see for OBJECT_AUDIT, SECURITY_AUDIT_SUCCESS, SECURITY_AUDIT_FAILURE in UC_CLIENT_SETTINGS

https://docs.automic.com/documentation/WEBHELP/English/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucabuw.htm?Highlight=revision

cheers, Wolfgang

Thanks for your help! I will play around with that. You rock guys!

Guys, that absolutelly solved my issues! I owe you a beer haha :-D

Thx a lot again!

My last question... I have a variable to calculate current day and time:

Vojtech Bures said:

My last question... I have a variable to calculate current day and time:

:SET &DATE# = SYS_DATE_PHYSICAL('YYYYMMDD')

:SET &TIME# = SYS_TIME_PHYSICAL('HHMMSS')

Would you please suggest, how to lower that date by 7 days? So when I am executing this on 4.2., I will get date 29.1. ? (4.2. -7 days)

Thank you for your help!

I'm not sure if Automic has (or does not have) anything stock to do this, but I doubt I'd want to do it in Automic Script.

Tip: Have a Linux host? Make a JOBS that uses GNU date (date or gdate command, depending on your Linux). GNU date has provisions to do command line date calculations properly, flexible and most of all, reliably.

Ex:

date -d yesterday
date -d "today -7 days"

Hth,
Carsten

Hi Vojtech,

I think what you are seeking for is the SUB_DAYS command (ADD_DAYS would add days to a given date).

Please check here, if this might answer the question. Its taken from Version 11 Documentation but still valid for Version 12)

(only if its interesting for someone: same you can do for time, ADD_TIME, SUB_TIME)

Thx Harald, thats just what I am looking for!

Guys, I love this community, so many great people opened to help.

Thank you so much!!!!