Configuring SQL 2008
Configuring SQL 2008 for usage tracker involves a bit of work. You will need to enable SQL Server to post logon events to the Windows security log. For that to happen, a Server Audit object needs to be created first:
Once the server audit has been created, it needs to be enabled because by default it is not. The server audit then needs to be associated with a Server Audit Specification. Like the server audit, the specification also needs to be enabled.
Also, if you want SQL Server to report object access information to Windows Security Log, the SQL service account needs to have the Generate Security Audits permission. You can configure it from the Local Security Policy applet. The applet interface is the same as that discussed under the Configuring Windows section: ies
- Under the Security Settings > Local Policies > Audit Policy, Audit object access property need to be set for both Success and Failure.
- Under the Security Settings > Local Policies > User Rights Assignment, the Generate security audits access right has to have the SQL service account as a member.
If you have configured Windows, SharePoint or SQL Server for generating log entries, Microsoft’s suggested waiting period is 90 days. In other words, MAP can analyse the logs from the previous 90 days to get a clearer picture of software usage. Once the log files for the server product have been created, you need to copy them to a location where MAP can access them.
Also, MAP needs to discover and inventory the machines where these logs were generated. This is necessary because MAP will need to be able to relate the log file information with the server’s configuration data saved in the database.
To give a simple example, I will try to measure the usage information for one particular Windows server that I know is being used by a client server application. I have already used MAP to inventory this server and I have also saved its Windows security log. The log has to be saved in its native format (.evt file).
Once the log file has been copied to a folder where MAP has access, I clicked on the Software Usage Tracker wunderbar on the left side navigation pane. As you can see, the detail pane shows the log parser needs to be configured:
Configuring the log parser basically involves telling it where the log files are located. In our case, I had saved the Windows security log .evt file under the D:Logs folder.
Once the path is specified, you can click on the Parse Logs link.
As log parsing starts, MAP goes through each log file in the directory and uses the information in the MAP database to create usage report.
Looking at the Log Files option under Log Parsing Overview node, I can see that the file has been processed. In this case it had about three days worth of events which is not ideal, but this is for illustration purpose only. You can configure Windows logs not to overwrite their entries and keep a certain number of days’ worth of events.
Once the log files have been parsed, you can then choose the Software Usage Summary node in the navigation pane and choose one of the server products. The detail pane will show the number of times devices and users connected to the server product in each of the computer systems audited. The default date range is 90 days, but you can change this from the action pane.
You can generate a report for the usage summary and it will create an Excel spreadsheet. The spreadsheet will be created in the same location where MAP saves its other reports and proposals. For license usage tracking, the worksheet you would be most interested in will be Client Access Summary. As the figure below shows, it has three columns: one for the server name, one for total the number of times devices connected to the server product during the time period specified and another column for the total number of times users connected to the server product in the same time.
The other page of interest will be the Client Access Details worksheet. It shows which user account accessed the server software from what device (IP address) on what date.