AuthAnvil provides detailed audit logging within SQL Server which can easily be parsed using SQL Reporting Services or the likes of Crystal Reports. However, sometimes you just want a raw report of who logged on, and who failed to log on.
An easy way to do this is to configure a scheduled report to do this for you each day. Scorpion Software offers a script called AAEmailReport.vbs specifically to do this which you can find in the AuthAnvil Diagnostics Library.
Below are instructions on how to set up a daily scheduled take on Windows Server to do this. The result will be an email with a list of successful and failed authentication attempts for the previous day.
Step 1: Launch the Task Scheduler
Start the Task Scheduler. Depending on the version of Windows, you can typically find this in the Control Panel under "Schedule Tasks". Click "Add Scheduled Task".
Step 2: Select the launch the script engine
When asked what program you want Windows to run, click the Browse button and select "C:\Windows\System32\cscript.exe".
Click Next.
Step 3: Create Initial Task
When prompted to name the task, select something unique that identifies the task, such as "AuthAnvil Daily Auth Report". The select to run this task "Daily".
Click Next.
Step 4: Schedule to Time to execute
When prompted for the time, select to run this at 12:01AM. of course, if you have other tasks that may be running at this time, feel free to schedule this at an off peak time early in the morning.
Click Next.
Step 5: Enter credentials with privileges to SQL
When prompted for a set of credentials to use, select a user account with privileges to query the AuthAnvil database. This may be the AuthAnvil Database user used for impersonation, or a domain administrator account.
Click Next.
Step 6: Complete initial wizard and go to Advanced Settings
When at the final page of the wizard, click the checkbox offering to open the Advanced Settings, and click Finish. This will add the initial task, and allow you to reconfigure the task to work with AAEmailReport.vbs.
Step 7: Modify the Run command line options
The AAEmailReport.vbs script takes a few parameters that need to be added to the run line. These need to be configured in the Advanced Settings before the task will run correctly.
These, in order, are:
- The SQL server and instance name where the AuthAnvil database resides. ie: SERVERNAME\AUTHANVIL or SERVERNAME\SQLEXPRESS. For standalone SQL servers, it may simply be the SERVERNAME.
- The runlevel. This can be a number between 1 and 3, where:
1 = Send only failed logons
2 = Send only successful logons
3 = Send both failed and successful logons - The "From" address
- The "To" address
- The name or IP address of the mail server to send this report through
As an example, the Run line in our office looks something like this:
C:\Windows\System32\cscript.exe C:\Tools\AAEmailReport.vbs CORPSQL08 3 security@scorpionsoft.com authanviladmins@scorpionsoft.com 192.168.1.1
As you can see, our main SQL 2008 server where our AuthAnvil database exists is called CORPSQL08, and we are requesting to send both successful and failed logon information to the authanviladmins from security, going through our Exchange server on our SBS box.
At this point you can hit Apply, and you will be prompted to enter the credentials again. Once complete, you will start receiving daily reports with the information you seek!
Hope that's helpful. Enjoy!
Comments