Creating Configuration Manager 2007 Reports – Part II Creating the first report

8th September 2008 19:30

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.

clip_image002 Open SQL Server Management Studio and log on to the server.
clip_image002[4] Navigate to the Configuration Manager database. The name is most likely SMS_<Sitecode>.
clip_image002[6] Right click Views and select New View
clip_image002[8] Select the Views tab.
clip_image002[10] Add V_R_System and V_GS_Add_Remove_Programs

Note: V_R_System contains the basic discovery data.
clip_image002[12] Now when we have the views we must create an Inner Join.From V_R_System drag the column RessourceID onto the RessourceID column onto the V_GS_Add_Remove_Programs view
clip_image002[14] Select Netbios_Name() from V_R_System and DisplayName() from V_GS_Add_Remove_Programs.
clip_image002[16] In the Criteria pane type SMS 2003 Toolkit and press enter in the Filter cell for the DisplayName() column.Notice that SQL automatically will replace the text with =’SMS 2003 Toolkit’ – Nice :-)
clip_image002[18] Let’s view the result before we copy the statement to Configuration Manager.Make sure that the SQL and Results panes are visible. If not right click, select Pane and enable them.
clip_image002[20] If everything looks OK; copy the entire SQL statement.
clip_image002[22] Open the Configuration Manager console and navigate to Reporting. Click New in the Actions pane.
clip_image002[24] In name type: All Computers with SMS 2003 Toolkit installedIn Category type: <Company name> SoftwareIn comment type the date, your name and a short description of the content.
clip_image002[26] Click Edit SQL Statement. Highlight the default SQL statement, right click and Paste your SQL statement into the Window.
clip_image002[28] Click OK.
clip_image002[30] Click Next.
clip_image002[32] Click Next.
clip_image002[34] Click Next.
clip_image002[36] Click Next.
clip_image002[38] Click Close.
clip_image002[40] Click Close – Congratulations you survived the wizard :-)
clip_image002[42] Highlight your report and click Run in the Actions pane.
clip_image002[44] You have now created your first report – it’s time to go impress management :-)

In the 3 part we will take a closer look at some of the conditionals and aggregate functions.

Tags:
Category: Config Mgr. Inventory and Reporting  |  Comment (RSS)




5 Responses to “Creating Configuration Manager 2007 Reports – Part II Creating the first report”

  1. chungb says:
    18th March 2009 at 02:52

    Hi Kent,

    this article is really useful for newbie like me, just a question on it.Can i add new column inside the existing table?
    The purpose i want to do this is to generate extra information like asset tag, department, etc. This will making life more easier for user to gain information. Please advise.

    Thanks in advanced.
    Chungb

    Reply
  2. Amnon says:
    20th November 2009 at 06:07

    I do not think you can, as these are pre-built tables. I think you can connect create your table and tie it to the database using a third party like MSACCESS or so.

    Reply
  3. Kent Agerlund Kent Agerlund says:
    20th November 2009 at 09:59

    Hi Amnon,

    It’s not a problem creating reports this way. We are not messing up the database only working with the views. Having said that, you can also use other methods like Microsoft Access.

    Reply
  4. Andrew says:
    8th December 2009 at 03:08

    WIth the example of creating the report is there a way to make a report with these software titles and include the Netbios name for the systems that have these programs. Microsoft only. Microsoft Office Enterprise 2007, Microsoft Office Professional Edition 2003,Microsoft Office Professional Plus 2007,Microsoft Office Project Professional 2003,Microsoft Office Project Professional 2007,Microsoft Office Visio Professinal 2003,Microsoft Office Visio Professional 2007,Microsot Ofice Visio Standard 2003,Microsot Ofice Visio Standard 2007.
    I tried the example above and was able to get a report for one title but how do I make a report for multiple titles.

    Reply
  5. karen says:
    11th May 2011 at 15:57

    trying to create the report or a query to make a report with these software titles and include the Netbios name/workstations, etc. for the systems that have these programs. Microsoft only. Microsoft Office Enterprise 2007, Microsoft Office Professional Edition 2003,Microsoft Office Professional Plus 2007,Microsoft Office Project Professional 2003,Microsoft Office Project Professional 2007,Microsoft Office Visio Professinal 2003,Microsoft Office Visio Professional 2007,Microsot Ofice Visio Standard 2003,Microsot Ofice Visio Standard 2007. Please help. Thanks!!!!

    Reply

Leave a Reply