Save the Cost of Unused Licenses by Viewing Software Installed but Never Used

You can save costs by identifying unused license fees with Aternity's REST APIs, by checking if your deployed applications are actually run by end users. If some monitored devices have an app but never launched or used it, you can withdraw those licenses or switch to a floating license system with far fewer licenses.

For example, if some users never launch Photoshop, you can remove the cost of that license for those devices, or use floating licenses instead.

You can check this quickly by running two REST APIs in Excel (learn more): INSTALLED_SOFTWARE which shows the device names which have a specific app (whether or not a user opened it), and APPLICATIONS_DAILY which lists the devices where the user opened the app recently. Then you can compare their results to see the devices which have the software but do not appear in the APPLICATIONS_DAILY list.

View the apps deployed but not used by merging tables from two APIs

To merge the results, create an extra column and use Excel's VLOOKUP function to check whether this hostname appears in the other table. If it does not find it there, it displays N/A in that cell, indicating that you spent money on this license for that hostname, but they did not run this app in the past three months.

Before you begin

Find the URL, username and password you need to run a REST API on your company's data, and learn how to access the results via Excel (learn more).

Procedure

  1. Step 1 In Excel, run INSTALLED_SOFTWARE (learn more) to list all devices with a specific app deployed.

    Use $select to display only the hostname (DEVICE_NAME) and app name (INSTALLED_SW_NAME), and filter to display only entries for this application name using $filter:

    .../INSTALLED_SOFTWARE?$select=DEVICE_NAME,INSTALLED_SW_NAME&$filter=(INSTALLED_SW_NAME eq 'appname')

    Tip

    To find the exact app name as it appears in the REST API, we recommend running the API without filters initially. During your test run, you may also find you need to add more filters, like showing only a specific version or location.

    Some applications may display with slightly different names for each flavor, like Notepad++, Notepad++ : a free (GNU) source code editor, Notepad++ (32-bit x86), or Notepad++ (64-bit x64). In these cases, we recommend using contains (not eq), or filter by the APPLICATION_IDENTIFIER for discovered apps.

    For example, to find all devices which have Notepad++, enter:

    .../INSTALLED_SOFTWARE?$select=DEVICE_NAME,INSTALLED_SW_NAME&$filter=contains(INSTALLED_SW_NAME,'Notepad++')

    View all hostnames which have Notepad++
  2. Step 2 Give the sheet a name like installed.
    Change the tab name to make it easy to identify
  3. Step 3 In another sheet, run the APPLICATIONS_DAILY query (learn more) to list all devices which ran the same app during the last 90 days.

    Use $select to display the hostname (SERVING_DEVICE_NAME) and the app name (APPLICATION), and filter to display only entries with this application's name. Also set the timeframe to the last 90 days:

    .../APPLICATIONS_DAILY?$select=SERVING_DEVICE_NAME,APPLICATION&$filter=(APPLICATION eq 'appname') and relative_time(last_90_days)

    Tip

    As above, check the API without filters first, to ensure you are using the correct filters and values.

    For example, to list all devices which have run Notepad++ in the last 90 days, enter:

    .../APPLICATIONS_DAILY?$select=SERVING_DEVICE_NAME,APPLICATION&$filter=contains(APPLICATION,'Notepad++') and relative_time(last_90_days)

    View the hostnames which ran Notepad++ in the last 90 days
  4. Step 4 Give this sheet a different name, like ran.
    Give the tab a name to reference it in the formula
  5. Step 5 Go back to the installed sheet in Excel and add a column called used_or_not, to insert the VLOOKUP function:

    =VLOOKUP(cell_of_hostname,sheet_name!lookup_cell_range,column_within_range,FALSE)

    It compares the hostnames in this sheet with the hostnames in the ran sheet. In our example, enter: =VLOOKUP(A2,used!B:B,1,FALSE)

    Enter the formula to check for this hostname in the other sheet

    VLOOKUP() requires four parameters:

    Field Description
    input

    The cell containing the input to VLOOKUP, which is the hostname (DEVICE_NAME).
    lookup range

    The range of cells which Excel must use to compare with this input (column B for the SERVING_DEVICE_NAME in the other sheet (called ran).
    lookup column

    The exact column within the range of cells to compare with this input (the first column in the range, column B)
    match

    Whether the match must be rough or exact. FALSE = exact match.

    Apply this function to all the cells in that column. All hostnames which do not appear in the ran sheet (those deployed but not used) display as N/A,

    Run the formula on the whole table
  6. Step 6 (Optional) To make it easier to read, you can turn the N/A into Not used with Excel's IFNA function.

    =IFNA(VLOOKUP(A2,ran!B:B,1,FALSE),"Not used")

    Tip

    You can add conditional formatting so that any cell containing the phrase Not used displays as bold or in a color.

    Make it easier to read by turning N/A into predefined text