Table of contents 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). ProcedureStep 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++ Step 2 Give the sheet a name like installed. Change the tab name to make it easy to identify 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 Step 4 Give this sheet a different name, like ran. Give the tab a name to reference it in the formula 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 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 Parent topic Overview for Analyzing with Aternity REST API v2 (OData)Related referenceFocus Your REST API Queries if Returned Data Too LargeView All Reported Activities with REST API (version 2.0)Analyze Resource Usage of a Managed Application (PRC) with REST API (version 2.0)Analyze the Raw List of Application Performance Reports with REST API (version 2.0)Analyze Application Performance Hourly or Daily with REST API (version 2.0)Analyze Application Performance Daily Anonymized (no PII) with REST API (version 2.0)Audit Aternity Access with REST API (version 2.0)Audit the Number of Dashboard Views with REST API (version 2.0)Audit the Changes Made by Aternity Users with Rest API (version 2.0)View Application Events with REST API (version 2.0)Analyze the Raw List of Activities with REST API (version 2.0)Analyze Activities Per Hour with REST API (version 2.0)Analyze Activities Per Day with REST API (version 2.0)Analyze Activities Per Day Anonymized (no PII) with REST API (version 2.0)View All Activities Not Reported to Aternity with REST API (version 2.0)Analyze the Boot Times of Devices with REST API (version 2.0)Analyze Device Inventory with REST API (version 2.0)Analyze Device (Agent) Status with REST API (version 2.0)Analyze Daily Device Resource Usage (HRC) with REST API (version 2.0)Analyze Daily Device Resource Usage (HRC) Anonymized (no PII) with REST API (version 2.0)Analyze Device's Resource Usage (HRC) with REST API (version 2.0)Analyze the Incidents Opened in Aternity with REST API (version 2.0)View Deployed Applications on All Devices with REST API (Installed Software) (version 2.0)View Software Changes on All Devices with REST API (Installed Software Change Log) (version 2.0)View Requests of Licenses in REST API (License Events)Analyze Inventory of Monitored Mobile Apps with REST API (version 2.0)Analyze Service Desk Alerts with REST API (version 2.0)Analyze Skype for Business Performance with REST API (version 2.0)Analyze WiFi Signal Strength and Reliability with REST API (version 2.0)View System Health Events with REST API (version 2.0)Related informationAternity REST API Column Names (version 2.0) SavePDF Selected topic Selected topic and subtopics All content Related Links
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). ProcedureStep 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++ Step 2 Give the sheet a name like installed. Change the tab name to make it easy to identify 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 Step 4 Give this sheet a different name, like ran. Give the tab a name to reference it in the formula 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 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 Parent topic Overview for Analyzing with Aternity REST API v2 (OData)Related referenceFocus Your REST API Queries if Returned Data Too LargeView All Reported Activities with REST API (version 2.0)Analyze Resource Usage of a Managed Application (PRC) with REST API (version 2.0)Analyze the Raw List of Application Performance Reports with REST API (version 2.0)Analyze Application Performance Hourly or Daily with REST API (version 2.0)Analyze Application Performance Daily Anonymized (no PII) with REST API (version 2.0)Audit Aternity Access with REST API (version 2.0)Audit the Number of Dashboard Views with REST API (version 2.0)Audit the Changes Made by Aternity Users with Rest API (version 2.0)View Application Events with REST API (version 2.0)Analyze the Raw List of Activities with REST API (version 2.0)Analyze Activities Per Hour with REST API (version 2.0)Analyze Activities Per Day with REST API (version 2.0)Analyze Activities Per Day Anonymized (no PII) with REST API (version 2.0)View All Activities Not Reported to Aternity with REST API (version 2.0)Analyze the Boot Times of Devices with REST API (version 2.0)Analyze Device Inventory with REST API (version 2.0)Analyze Device (Agent) Status with REST API (version 2.0)Analyze Daily Device Resource Usage (HRC) with REST API (version 2.0)Analyze Daily Device Resource Usage (HRC) Anonymized (no PII) with REST API (version 2.0)Analyze Device's Resource Usage (HRC) with REST API (version 2.0)Analyze the Incidents Opened in Aternity with REST API (version 2.0)View Deployed Applications on All Devices with REST API (Installed Software) (version 2.0)View Software Changes on All Devices with REST API (Installed Software Change Log) (version 2.0)View Requests of Licenses in REST API (License Events)Analyze Inventory of Monitored Mobile Apps with REST API (version 2.0)Analyze Service Desk Alerts with REST API (version 2.0)Analyze Skype for Business Performance with REST API (version 2.0)Analyze WiFi Signal Strength and Reliability with REST API (version 2.0)View System Health Events with REST API (version 2.0)Related informationAternity REST API Column Names (version 2.0)
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). ProcedureStep 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++ Step 2 Give the sheet a name like installed. Change the tab name to make it easy to identify 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 Step 4 Give this sheet a different name, like ran. Give the tab a name to reference it in the formula 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 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 Parent topic Overview for Analyzing with Aternity REST API v2 (OData)Related referenceFocus Your REST API Queries if Returned Data Too LargeView All Reported Activities with REST API (version 2.0)Analyze Resource Usage of a Managed Application (PRC) with REST API (version 2.0)Analyze the Raw List of Application Performance Reports with REST API (version 2.0)Analyze Application Performance Hourly or Daily with REST API (version 2.0)Analyze Application Performance Daily Anonymized (no PII) with REST API (version 2.0)Audit Aternity Access with REST API (version 2.0)Audit the Number of Dashboard Views with REST API (version 2.0)Audit the Changes Made by Aternity Users with Rest API (version 2.0)View Application Events with REST API (version 2.0)Analyze the Raw List of Activities with REST API (version 2.0)Analyze Activities Per Hour with REST API (version 2.0)Analyze Activities Per Day with REST API (version 2.0)Analyze Activities Per Day Anonymized (no PII) with REST API (version 2.0)View All Activities Not Reported to Aternity with REST API (version 2.0)Analyze the Boot Times of Devices with REST API (version 2.0)Analyze Device Inventory with REST API (version 2.0)Analyze Device (Agent) Status with REST API (version 2.0)Analyze Daily Device Resource Usage (HRC) with REST API (version 2.0)Analyze Daily Device Resource Usage (HRC) Anonymized (no PII) with REST API (version 2.0)Analyze Device's Resource Usage (HRC) with REST API (version 2.0)Analyze the Incidents Opened in Aternity with REST API (version 2.0)View Deployed Applications on All Devices with REST API (Installed Software) (version 2.0)View Software Changes on All Devices with REST API (Installed Software Change Log) (version 2.0)View Requests of Licenses in REST API (License Events)Analyze Inventory of Monitored Mobile Apps with REST API (version 2.0)Analyze Service Desk Alerts with REST API (version 2.0)Analyze Skype for Business Performance with REST API (version 2.0)Analyze WiFi Signal Strength and Reliability with REST API (version 2.0)View System Health Events with REST API (version 2.0)Related informationAternity REST API Column Names (version 2.0)