Software / OS Deployment Matrix

If you're looking for a possibility to get a report about your installed software related to the operating systems deployed in your K1000 managed network this might be interesting for you.

The result will be an Excel pivot table.

This could be useful if you're planning to migrate to a new OS version and want to check which software needs compatibility checking, prioritized by install count or if it is probably already running on your target OS.

Additionally, you can use it to follow your OS migration process since you can see the install counts "wandering" to your target OS while in migration.


Accessing the data can be done with K1000 external database access which needs to be enabled and configured, see this link if you need any guidance with that:



If you do not like that create a classic report in the K1000 with the "New (SQL)" function and configure the output to be an Excel-file. Be sure to disable the "Show Line Numbers" option.
But if you're planning to update your report regularly you should really consider using ODBC, it makes updating your report a whole lot more comfortable.


In both cases, you'll get a table with each row containing an item from the K1000 software catalogue and a machine where it is installed on.


The query you need is:

  SAMall.PUBLISHER As Publisher,
  SAMall.NAME As Software,
  mach.NAME As Computername,
  plost.OS_TYPE As OS,
  Concat('http://your.k1000.fqdn/adminui/sam_detail_titled.php?ID=',SAMall.ID) As LinkURL
    On SAMall.ID = smjt.SAM_CATALOG_ID Inner Join
  MACHINE mach
    On smjt.MACHINE_ID = mach.ID Inner Join
    On mach.OS_ID = osid.ID Inner Join
    On plost.ID = osid.PATCHLINK_OS_TYPE_ID
  SAMall.LICENSE_TYPE != 'Supporting Applications' And
  SAMall.LICENSE_TYPE != 'Freeware' And
  SAMall.LICENSE_TYPE != '0'
Group By
  SAMall.PUBLISHER, SAMall.NAME, mach.NAME, plost.OS_TYPE,
  Concat('http://your.k1000.fqdn/adminui/sam_detail_titled.php?ID=',SAMall.ID), SAMall.LICENSE_TYPE

- remove "SAMall.LICENSE_TYPE != 'Freeware' And" if you want to see freeware apps too.
- Adjust "http://your.k1000.fqdn" to your K1000 FQDN

To create the pivot table in Excel, select the whole table data (or choose your data source from your ODBC setup) as the source and click on the "PivotTable" button on the "Insert" taskbar.

Choose where to place the new pivot table, take "New sheet" if you are unsure.


Now you see the "PivotTable-Fields" section in the right corner of the Excel window and you can create you report by dragging and dropping the field names to the pivot tables sections:


1. Drag "Publisher" to the "Rows" first.

2. Drag "Software" to the "Rows", too but BELOW the "Publishers".

3. Drag "OS" to "Columns".

4. Drag "Computername" to "Values". Be sure that it shows "Count of Computername". If it says something else, click the field, go to "Value Field Settings" and change "Summarize Values by" to "Count".


You should now see a matrix of all the software discovered by the K1000 in your network and on how many computers it is installed on which operating system.

Even more: if you double-click the amount number of an entry in the pivot table it opens a new sheet with all the computers names having the software installed and a link to the details page of the software you're looking at.
Or, if you prefer, additionally drag the "Computername" field to the "Rows" below "Software".


  • Great work! Very useful for migrations and a quick and simple overview :) - Timokirch 6 years ago
This post is locked
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ