Automatic Login/Logoff Reports.

Article created 2013-09-17 by Florian Riedl.

In this article we have a scenario of a Windows Server 2008 system, which monitors the Security Logs for Logon and Logoff Events through EventReporter, writes the log data to a database and creates automatic reports through Adiscon LogAnalyzer and Scheduled Tasks.

Table of Contents

1. Requirements

1.1 About the requirements

1.2 Installing and configuring WAMP

1.3 Installing MYSQL ODBC Connector

2. Installing and configuring EventReporter

2.1 Download and Install EventReporter

2.2 Setup Processing RuleSet

2.2.1 Setup Database Logging

2.2.2 Create the Database Action in EventReporter

2.4 Add EventLog Monitor Service(s)

2.5 Starting EventReporter and verifying the configuration

3. Install and Setup LogAnalyzer

3.1 Download and copy LogAnalyzer to the right location

3.2 Install and configure LogAnalyzer

3.3 Creating a Report

4. Creating a Scheduled Task

Final Thoughts

Custom Reports

1. Requirements

1.1 About the requirements

Back to Top

1.2 Installing and configuring WAMP

After you downloaded WAMP, start the installation and follow the instructions.

Make sure you do not have a web server or MYSQL Server already installed because this could result in conflicts. Most often Microsoft ISS is already installed on the Windows platform. If so, there is no need to install WAMP, butyou still need MySQL and php for IIS.

I will use the default installation location in this article which is C:\wamp.

Back to Top

Once the Installation is finished, a new Icon appears in Windows Icon tray. Click it, and choose “Localhost” from the menu to verify if the installation was performed successfully. If it was, you should see a web site looking like the one on the right. To check if your MYSQL is running, click on the phpMyAdmin Menu button in the WAMP Menu, and login with the username “root” and no password – if you are asked for a login.

Back to Top

1.3 Installing MYSQL ODBC Connector

Since we intend to store messages in a MYSQL database, you need to install the MYSQL ODBC Connector. EventReporter will need a MYSQL ODBC driver in a later step in order to write into the MYSQL database. These drivers have to be downloaded and installed separately from here: http://dev.mysql.com/downloads/connector

If your Windows System is a x64 version, it is important to install the x64 Version of the MySQL Connector driver. As the EventReporter Service runs as a 64bit application itself, it will need the connector to be 64bit as well.

2. Installing and configuring EventReporter

2.1 Download and Install EventReporter

2.2 Setup Processing RuleSet

2.2.1Setup Database Logging

We want to store messages inside the MYSQL database, so follow this step. Click on your WAMP Icon, and open the phpMyAdmin. Now Create a new database called “eventreporter“.

Back to Top

Once done, select the newly created database, switch to the “SQL” tab and copy the SQL statements from the textbox below.

CREATE TABLE SystemEvents
{
ID int unsigned not null auto_increment primary key,
CustomerID bigint,
ReceivedAt datetime NULL,
DeviceReportedTime datetime NULL,
Facility smallint NULL,
Priority smallint NULL,
FromHost varchar(60) NULL,
Message text,
NTSeverity int NULL,
Importance int NULL,
EventSource varchar(60),
EventUser varchar(60) NULL,
EventCategory int NULL,
EventID int NULL,
EventBinaryData text NULL,
MaxAvailable int NULL,
CurrUsage int NULL,
MinUsage int NULL,
MaxUsage int NULL,
InfoUnitID int NULL,
SysLogTag varchar(60),
EventLogType varchar(60),
GenericFileName VarChar(60),
SystemID int NULL
};

CREATE TABLE SystemEventsProperties
{
ID int unsigned not null auto_increment primary key,
SystemEventID int NULL,
ParamName varchar(255) NULL,
ParamValue text NULL
};

Now insert the copied commands into the SQL field. Then Click “GO”, you should see “Your SQL query has been executed successfully” after that as well as two new tables on the left list called systemevents and systemeventsproperties.

Back to Top

2.2.2 Create the Database Action in EventReporter

Get back to the EventReporter Client and create a new Rule in your self-created RuleSet called “Database”. Then add a new “Write to Database” Action, and name it “MYSQL ODBC”. After creating this action, you should automatically be taken to the actions properties.Click on the “Data Sources (ODBC)” button to open the System ODBC Administrator. Click on the “System DSN” Tab and add a new Datasource, select “MySQL ODBC 5.1 Driver” as driver. It is important to add a System DSN rather then a User DSN, because User DSN’s are not usable by the EventReporter Service (this is a Windows design restriction).Name the new datasource “eventreporter” and use “localhost” as Server, “root” as username and no password. Then you are able to select the database which we created before called “eventreporter“.

Check the database logging action again, it should look like the one in the screenshot. Back to Top

2.4 Add EventLog Monitor Service(s)

Now add a Eventlog Monitor service. Inside that service, configure the logs to be monitored. For example you could only monitor the system or the security log. Just set the check markers as you like. For our setup, we only need Events from the security log.

It may also be a good idea to set a syslog tag name that matches the log (or the function of the machine name, e.g. “server_1”). By doing so, you can easily filter inside LogAnalyzer.

2.5 Starting EventReporter and verifying the configuration

From the EventReporter configuration point of view, everything is setup now. So kindly start the EventReporter Service and wait a few moments, so that the data can be processed.

Since you are logging into a database, switch back to phpMyAdmin and browse through the systemevents table. You should see at least one data record in this table now, like in the screenshot sample. If not, something went wrong, in this case please check the Windows Application Event Log for possible error reports from EventReporter.

Back to Top

3. Install and Setup LogAnalyzer

3.1 Download and copy LogAnalyzer to the right location

We recommend to download the latest stable or beta build from here: http://loganalyzer.adiscon.com/downloads. Though, if you are using MonitorWare Agent for this Guide, you can use the version that is shipped with it, we still recommend to always get the latest version through the link above.In this article I will use LogAnalyzer Version 3.6.4.To unpack the install set, you need a program capable of processing tar.gz files. Most ZIP programs support this. If you do not have one, you can find WinRAR by following the link (we have no affiliation with the makers of WinRAR, but have found it to be a useful tool – use at your own risk).

Open windows explorer and go to the www folder of your Apache web server, which is the folder where you can place html/php files. By default this will be “C:\wamp\www” if you have installed WAMP into the default installation folder. Create a new folder called loganalyzer there.

When you downloaded and unpacked LogAnalyzer, copy or move the content of the src folder into the C:\wamp\www\loganalyzer folder.

The explorer window should look like in the screenshot now.

Back to Top

3.2 Install and configure LogAnalyzer

Open this link to start the LogAnalyzer installation: http://localhost/loganalyzer/If you do not see a page like in the screenshot, something went wrong in the steps before, please check them in this case.Otherwise click on the text-link “here” on LogAnalyzer’s error page to start its installation routine.

Follow the installation steps of LogAnalyzer.I recommend to “Enable User Database” in Step 3, as this will give you an advanced admin control panel. The User Database requires a MYSQL database to work, you can use the same one as you are using for EventReporter.

If you are using MYSQL to store log messages and you have reached Step 7, switch the source type to “MYSQL Native” and name the Source “EventReporter” Use “eventreporter” as Database Name and “root” as Database User. Leave the other configuration variables as they are, see the screenshot for how it should look like.Then click on the Next button to finish the installation.
After you finished the Installation of LogAnalyzer, you need to login and switch to the sources admin and configure the source “WebLogStore DB” there.

– In field “Message Parsers” add apache2 if you are using combined log format. Add apach2common if you are using common log format.

After clicking on the “Finish” link, you should see a working LogAnalyzer installation. If you do not see any data, there may be no data in your database yet. Otherwise you will see an error code and message from LogAnalyzer.

Back to Top

3.3 . Creating a report in LogAnalyzer

To use the reports-module, you need to have some requirements to fulfill. You need a working installation of LogAnalyzer, a user with admin privileges in LogAnalyzer and a data source, preferably already filled with log data. When you have all this done, you can start creating your reports.

First of all use the Login Button to log in to your account. You can directly go to the admin center. As you can see, you get a new menu with a lot of submenus for customizing your configuration. But this is all not so interesting right now. All except the Report Modules. Click on the “Report Modules” button to get to the configuration menu. There you will find a overview of the installed reports.

By clicking on “Add Savedreport” at the report you want to configure, you get to a configuration window.

Here you can set various details like the name and description of the report, configure a filter for the report or set the Data Source you want to use and the output format.

For our example, make sure to add a “Date” filter and set it to “Last 24 hours”. Also, I chose to create the report as PDF and store it into a file. Make sure the folder is existant or the report cannot be generated. Now simply save this report by clicking on “Add Savedreport and return to reportlist”. You will be automatically redirected to the report overview and your new report is ready to use.

We need to repeate this step for a 7-day report. This time, instead of setting the filter to “Last 24 hours”, set it to “Last 7 days”. To be able to distinguish the reports, alter the Report Title accordingly.

Back to Top

Now you can directly test the report by clicking the green arrow on the right. The report will be generated and (depending on your settings) stored or opened. In our case, the report will be stored to “C:\reports\”.

Since we want to schedule the report to be automatically generated, click on the name of your configured report. You get to the configuration window again. At the bottom of the form, you see a line with a console command. This is automatically generated when saving the report the first time. With this command line you can create a cron job or a scheduled task, which allows you to automatically generate the report at specific time intervals as you need it.

Back to Top

4. Creating a Scheduled Task

All we need now is a Scheduled Task that runs that command line. So got to Windows Control Panel and open “Administrative Tools” and select “Scheduled Tasks”. There we have to add a new task. By clicking on “Create Basic Task” on the right side, a wizard will open that lead us threw the configuration.

First you have to title your task as you prefer. I entered “LogAnalyzer-Report”.

The next step would be to select the time interval (we selected “Daily”).

The next step would be to select the time when the report should be generated. Like every day at midnight or at noon. Choose a time to start the task and click “Next”. In the next step you have to choose “Start a program” and then press next.

Now we decide what the task should do. In our case, start a program. We need to insert the command line from our saved report, we created in LogAnalyzer, in “Program/script:” and “Add arguments” as in the screenshot. In the case of this sample the command line is: C:\wamp\bin\php\php5.4.12\php.exe C:\wamp\www\loganalyzer\cron\cmdreportgen.php runreport logonlogoff 14

In the next window we get a summary of the task that will be created. You can now finish the wizard.

You will now see your library of Scheduled Tasks. There might already be some tasks you created, from other software or that the system has created. Choose your new task, right-click on it and choose run. By that, you can verify if the task works as expected.

Now that we have created the first task, repeat the steps and create a second task for our weekly report. Instead of the daily occurence, set it to weekly and choose a time to generate the report to your liking (probably sunday at midnight). Also, choose the command line options for the weekly report to execute instead of the daily ones we just used. After you have done this, the task library should hold 2 tasks for our LogAnalyzer reports.

Please note that LogAnalyzer will overwrite the created file if it is not renamed. In one of the next releases there will be a feature that makes it possible that LogAnalyzer is able to create unique filenames.

Back to Top

Final Thoughts

I hope this article will help you installing and configuring Loganalyzer and EventReporter. If you have problems or question related to this article, don’t hesitate to contact me or our support by email.

Please note that while this setup works, it is not very secure. At a minimum, it is recommended to set proper passwords for the databases (instead of using a password-less root account). Please review the relevant documentation on how to do that.

Custom Reports

If you have need of a special report that is not yet available in Adiscon LogAnalyzer, you may consider funding development for it. More details can be found here on the LogAnalyzer Support Page.