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

Assumptions

  • 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”

 

snap0358 snap0359snap0367

snap0372 snap0376

One the DemoLogParser DB is created, you’ll see there are no tables.

snap0381

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

 

snap0386 snap0390

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

 

 snap0392 snap0397

 

 

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

image image

Create a new DataSource to the DemoLogParser database. Call this DataSource “DataSourceLogParser”

image snap0514 snap0515

 snap0518snap0519 snap0521

 snap0522 snap0523

 

Create a new DataSet to for the event log table, call this “DataSetSystemEventLog”

 

snap0526 snap0530 snap0534

snap0535snap0536  snap0539

snap0557 snap0558

 

 

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.

snap0537  snap0559 snap0560

Preview the report

snap0561 snap0562

 

STEP 5: DEPLOY THE REPORT TO THE SSRS SERVER

 

Save the Report as an RDL file and upload it to the SSRS Server

 

snap0565 snap0569 snap0570

snap0573 snap0577 snap0579

snap0580 snap0583 snap0584

snap0585

STEP 6: VIEW THE REPORT

Now you can see the report via SSRS

snap0585 snap0586 snap0587

 

PARTING THOUGHTS

  • 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.