creating configuration manager 2007 reports

8/8/2019 Creating Configuration Manager 2007 Reports

1/12

Creating Configuration Manager 2007 Reports

Part I Basics

Kent Agerlund - 7th September 2008

I often hear customers and students complaining about the complexity of creating reports. Statements like;

the database is a mystery and I am not a SQL expert. I agree, the database can be a mystery. But using the

proper tools and some very basic SQL skills you will be able to create powerful reports.

I will post 4 parts explaining the basics and how to build a few reports.

Views:

Reports are based on SQL views. A view is basically just a query joining one or more tables. Knowing a few

basics of the naming standard I very helpful:

V_GS_Name = Contains current inventory data

V_HS_Name = Contains history inventory data

V_R_Name = Contains Scalar Discovery data.

V_RA_Name = Contains Array (Multi value) Discovery data.

SQL Server Management Studio:

I use SQL Server Management Studio to create the reports. Its a nice graphical interface and I can easily test

the syntax.

Conditionals:

SQL uses well-known conditionals like:

=

AND

OR

NOT

DISTINCT

LIKE

TOP

Aggregate Functions:

GROUP BY

COUNT

MIN

MAX


2/12

Part II Creating first Report

In this part I will walk you through the creation of a report in SQL Server Managmenet Studio. The report will

show all clients with SMS 2003 Toolkit installed.

Open SQL Server Management Studio and log on tothe server.

Navigate to the Configuration Manager database. The

name is most likely SMS_.

Right click Views and select New View

Select the Views tab.

Add V_R_System and V_GS_Add_Remove_Programs

Note: V_R_System contains the basic discovery data.

Now when we have the views we must create an Inner

Join.From V_R_System drag the column RessourceIDonto the RessourceID column onto the

V_GS_Add_Remove_Programs view

Select Netbios_Name() from V_R_System andDisplayName() from V_GS_Add_Remove_Programs.


3/12

In the Criteria pane type SMS 2003 Toolkit and pressenter in the Filter cell for the DisplayName()column.Notice that SQL automatically will replace the

text with =SMS 2003 Toolkit Nice

Lets view the result before we copy the statement to

Configuration Manager.Make sure that the SQL andResults panes are visible. If not right click, select Pane

and enable them.

If everything looks OK; copy the entire SQL statement.

Open the Configuration Manager console and navigate to

Reporting. ClickNew in the Actions pane.

In name type: All Computers with SMS 2003 Toolkit

installedIn Category type: SoftwareIn comment type the date, your name and a

short description of the content.

ClickEdit SQL Statement. Highlight thedefaultSQLstatement, right click and Paste your SQL statement into

the Window.


4/12

ClickOK.

ClickNext.

ClickNext.

ClickNext.


5/12

ClickNext.

ClickClose.

ClickClose Congratulations you survived the wizard

Highlight your report and clickRun in the Actions pane.

You have now created your first report its time to go

impress management


6/12

Part III Using Count

In this example I explain how to use the Count function. I anticipate that you already by now know how to

create the report in Configuration Manager and how to start SQL Server Management Studio. This report willgroup all computer objects by Vendor and Model with a count of each unique model.

Ive started SQL Server Management Studio, navigated to the

SMS_Sitecode database, and created a new View. For information those steps please refer to Part II of this post.

Select V_GS_Computer_System, clickAdd and Close.

Select the columns Manufacturer(), Model() and Model() - the M

column is selected twice as we need the one column with a name ansecond column with a count.

In Alias type Total for the second Model() column.

From the Query Designer menu, select Add Group By.

Select Count in the second Model() column. Your SQL statement slook like this:

SELECT Manufacturer0, Model0, COUNT(Model0) AS Total

FROM dbo.v_GS_COMPUTER_SYSTEMGROUP BY Manufacturer0, Model0


7/12

Execute the SQL Statement. The result from my test environment idisplayed in the left column. Now copy the SQL statement to a newreport in Configuration Manager as explained in Part II of this post

Part IV Using Prompt

In this example I explain how to use the prompt function. I anticipate that you already by now know how to

create the report in Configuration Manager and how to start SQL Server Management Studio. This report willlist some basic hardware details with a prompt on the Bios Serial number. I am creating the first part of the

report in SQL server 2008 Studio Manager, and the prompt in the Config Mgr. console.

When you are working with prompts in reports there is a few things you need to know first.

y To use variables use the @ symbol eg. WHERE V_GS_PC_BIOS.SerialNumber0 = @BiosSerialNumbery Variables in SQL statement stores the values from the prompt

The following views are used in this example:

V_R_SYSTEM

V_GS_PC_BIOS


8/12

V_GS_COMPUTER_SYSTEM

Ive started SQL Server Management Studio, navigated to the

SMS_Sitecode database, and created a new View. For informationabout those steps please refer to Part II of this post.

Select V_GS_Computer_System, V_R_SYSTEM, V_GS_PC_BIOSclickAdd and Close.

From V_R_System drag the column RessourceID onto the

RessourceID column onto the V_GS_Computer_System view.

From V_R_System drag the column RessourceID onto theRessourceID column onto the V_GS_PC_BIOS view.

From V_R_SYSTEM select, Name()From V_GS_PC_BIOS select, Name(), Releasedate(),

SerialNumber()From V_GS_COMPUTER_SYSTEM select, Manufacturer0,

Model0, NumberOfProcessors0

In the V_GS_PC_BIOS.Name() Alias. column type Bios Name

The SQL statement:

SELECT dbo.v_R_System.Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0,dbo.v_GS_COMPUTER_SYSTEM.Model0,

dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0, dbo.v_GS_PC_BIOS.Name0


9/12

AS [Bios Name], dbo.v_GS_PC_BIOS.ReleaseDate0,dbo.v_GS_PC_BIOS.SerialNumber0

FROM dbo.v_R_System INNER JOINdbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID =

dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOINdbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID =

dbo.v_GS_PC_BIOS.ResourceID

The Result

Copy the SQL statement, open the Config Mgr. console, create a newreport and paste the SQL statement as explained in post II.

If you finish the report wizard, you will have a useful report with basic

hardware information.

Now you need to create the prompt and the select statement. We start

by creating the prompt, click on the Promptsbutton.

In the Prompts dialog box click on the yellow starburst icon (to create

a new prompt)

In the Name column type the variable name (this is the name we willuse later in the Where statement). In my example I type

BiosSerialNumber

In prompt text type the text that will be displayed for the end user. I

typed Select bios serial number


10/12

Next we will provide a SQL statement which will fill the list of biosserial numbers.Click Provide a SQL statement and click the Edit SQL Statement

button

In the SQL statement box type:Select distinct SerialNumber0 From v_GS_PC_BIOS

Click OK

ClickOK

ClickOK


11/12

ClickYes

By now you should be back in the SQL statement, navigate to the endand type

WHERE (dbo.v_GS_PC_BIOS.SerialNumber0 =

@BiosSerialNumber)

Notice: You could also type @BiosSerialNumber in the criteriacolumn in SQL Server Management Studio.

ClickOKand save the report.

When you run the report it should look like this.

When you click Values you will run the SQL statement from theprompt

ClickDisplay


12/12

Nice report

creating configuration manager 2007 reports

Documents