This post will illustrate a very simple end-to-end walkthrough of how you can use SQL 2008 reporting features to expose and analyze system administration information. This post will demonstrate the creation and deployment of a simple SSRS report that shows all the events in the “System” event log.
STEP 1: PREPARATION
What you’ll need
- Log Parser 2.2: Download details- Log Parser 2.2
- SQL Report Builder
- SQL 2008 (with Reporting Services installed)
- You have two machines – a development workstation and a separate SQL server that has the database and reporting services installed
- IIS installed and configured in your windows server machine
Notes about the demo:
- Be aware that all the server screenshots are done on a beta build of Window Server 2008 R2. You will see some obvious differences from Windows Server 2008 UI.
- The name of the SQL server in this example is “isotope”
STEP 2: CREATE A DATABASE IN SQL TO HOLD THE LOGPARSER DATA
Launch SQL Management Studio, and create a new Database called “DemoLogParser”
One the DemoLogParser DB is created, you’ll see there are no tables.
We’ll change that in a moment.
STEP 3: POPULATE THE DEMOLOGPARSE DATABASE WITH DATA FROM THE SYSTEM EVENT LOG
Start LogParser and run this command
LogParser.exe "SELECT * INTO eventlog_system from System" -o:SQL -server:isotope -database:DemoLogParser -createtable:ON
Now if you example the DemoLogParser database you’ll see a table called “dbo.eventlog_system” has been created. Examine the table and you’ll see all the columns that logparser created
STEP 4: DESIGN A REPORT WITH BUILDER 3.0
NOTE: for these steps, the demo assumes they are being done from a separate developer workstation. (in this example they are being done on my primary desktop called “saveenr10”)
Launch SQL Report Builder
Create a new DataSource to the DemoLogParser database. Call this DataSource “DataSourceLogParser”
Create a new DataSet to for the event log table, call this “DataSetSystemEventLog”
Then add the DataSet as a table to the report. Some of the fields will be automatically aggregated (for example with “Sum”) so clear that from the fields.
Preview the report
STEP 5: DEPLOY THE REPORT TO THE SSRS SERVER
Save the Report as an RDL file and upload it to the SSRS Server
STEP 6: VIEW THE REPORT
Now you can see the report via SSRS
- Security – this is a demonstration. Event logs can contain sensitive information; don’t expose data like without without thinking through who can see it.
- The end report isn’t very fancy – as you explore the feature set of Report Builder 2.0 you can easily make create an appealing layout and style.
- logparser is a nice way to get some quick simple demo data if you need to explore building reports. Again, be aware of inadvertently revealing sensitive information.
- logparser – it is far more powerful than this post shows.