How to get a list of applications discovered out of MAP

• Thursday, February 02, 2012 1:46 AM

  Moderator

   

   
  

  1
  Currently, we don’t have a way to generate a report of products installed by machine because of the row limitation of Excel which could easily be exceeded in a large environment. We are trying to determine if we can add this feature in a future release. In the meantime, here is a method for extracting that data into Excel and then you can use pivot tables or whatever you like to distill the information.

  Disclaimer:

  This advice assumes that you are comfortable using SQL Management Studio or SQL commands to accomplish these tasks. No guarantees or warranties are given or implied by using this advice and it is to be used at your own risk.

  Option 1:

  If you just need to know the query to return the list of applications so you can write your own custom query, run this:

  select * from Get_UI_ProductResults ()
  

  Note: If you copy/paste the query and get an error, try manually typing it. Sometimes hidden characters or alternate versions of characters get substituted by the browser and can't be read by SQL Server.

  Option 2:

  If you want to import the data into Excel: (assumes Excel 2010)

  1. Create a view in SQL Server Management Studio:
     1. Find the database with the info you want
     2. Find the 'Views' folder and right-click
     3. Select 'New View…'
     4. In the 'Add Table' dialogue box, go to the 'Functions' tab
     5. Select 'Get_UI_ProductsResults'
     6. Click Add
     7. Click Close
     8. In the function box, choose Computer Name, Name, ProductVersion, ProductVendor
     9. Go to File » Save and choose a name for the view
     Once you have created this view, you can open Excel on the machine with MAP installed and use the data connection from other sources to import that view into Excel and manipulate the data any way you want.
  2. Connect Excel 2010 to the SQL Server database:
     1. Click on the 'Data' tab
     2. In the 'Get External Data' area, choose 'From Other Sources'
     3. Choose 'From SQL Server'
     4. Enter the server name with instance, i.e. YourComputerName\MAPS
     5. From the drop down list, select the database that you created the view in
     6. Find and Select the view you created above
     7. Click Finish
     8. Choose how you want to view the data
     Note: I prefer to import the data as a table so I can see the data in a “raw” format before I create a pivot table.

  Warning:

  Before Excel 2007, the row limit was 65,536 and now it is 1,048,576 rows. It is recommended that you check to see how many rows are returned in the view’s query before importing the view into Excel, otherwise it will fail.

  ---

  Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
  

  • Edited by Michael Switzer [MSFT CSG]Moderator Friday, February 24, 2012 7:09 PM Added note for possible query error
  •  
  • Reply
  • Quote