How to Export License Usage and Calculate License Consumption in Excel

By Katie Huckett posted 22 days ago

  

In a previous article, Monitoring Float License Usage, we have detailed out several ways to analyze license metrics between the Admin section of Connect and the exported raw data. In this article we will expand on a more advanced analysis of the raw license data exported to Excel to evaluate license consumption.

Exporting License Usage from Connect

The Admin license page does not store data older than 90 days, therefore it is recommended to export the license usage data every 90 days for all license types in order to maintain a historical record locally for year-over-year analysis and identifying longer-term trends.

You will start by performing these steps within the Connect Admin License page:

  • Log in to Connect > Admin > License
  • Set the license type dropdown to 'All'
  • Set the timeframe dropdown to 'last 90 days'
  • Select the 'Export' button and save the Excel file locally
  • Open the Excel file and perform the following steps necessary for performing a license consumption analysis:
    • NOTE: If you have completed the analysis from the Monitor Float License Usage article mentioned above, you may add this analysis to your existing spreadsheet instead of exporting a second copy.
    • Label the original worksheet from the export 'RawData' to identify the original data from Connect
    • Right-click on the worksheet > Select Move or Copy... > Select the RawData sheet and check the Create a copy box > select OK
    • Right-click the new worksheet and rename it 'CreatorFloat'
      • Repeat the above steps to copy the RawData worksheet for all unique license types you would like to analyze - in this example, a worksheet has been created for both Creator and CreatorFloat license types

Perform data santization and cell formatting

  • Starting on the CreatorFloat worksheet, perform the following data sanization steps:
    • Remove the rows for any data that is not for a Creator (float) license
      • With your cursor somewhere in the worksheet select the Sort & Filter dropdown > select Custom Sort...
      • In Sort by select LicenseAssigned > leave Sort On and Order as the default settings > select OK
      • Highlight and Delete any rows that contain data for a license type other than 'Creator (float)'
    • Insert a new column to calculate the Duration in Milliseconds into a HH:MM:SS format
      • Place your cursor in the second row of the new column and enter the following formula: =DurationInMilliseconds/86400000
      • Copy the formula down the entire column
      • Highlight the column and set the cell format to Time as hh:mm:ss

  • Prepare the LoggedInAt column for the proper date and time formatting and analysis
    • Highlight the entire LoggedInAt column > select Find & Select > Replace... and complete the following two Find/Replace steps: 
      • Find what = " AM UTC" (be sure to include the space before AM) and Replace All with = " AM" with a space preceding
      • Find what = " PM UTC" (be sure to include the space before PM) and Replace All with = " PM" with a space preceding
      • Highlight the entire column > select the dropdown to format the cell and select More Number Formats... > select the Category 'Custom' and choose the m/d/yyyy hh:mm:ss option > select OK
      • (Optional) You may wish to rename the column 'LoggedInAtUTC' as a reminder that the time is UTC and not your local time zone

  • Calculate the Logged Out time by adding the LoggedInAtUTC time to the Duration
    • Create a new column next to the LoggedInAtUTC column and label it 'LoggedOutAtUTC'
    • Enter the formula: =LoggedInAtUTC+DurationTime - in the example shown this is =G2+E2
    • Copy the formula down the remainder of the column
    • IF the LoggedOutAtUTC column does not follow the same formatting as the LoggedInAtUTC column, then apply the same Custom formatting as before (m/d/yyyy hh:mm:ss)
    • Highlight the LoggedOutAtUTC column > Copy the data > Paste Values only to replace the formula with the values alone (this is so that we can delete unnecessary columns, such as DurationTime, from the worksheet in the next step)

  • Remove the following columns which are no longer needed to clean up the worksheet:
    • LastActivityAt
    • LicenseAssigned
    • DurationInMilliseconds
    • DurationTime
    • Downgraded
    • You should only have three columns remaining: UserName, LoggedInAtUTC, and LoggedOutAtUTC
  • Convert the LoggedInAtUTC and the LoggedOutAtUTC columns into text so that Excel may do the correct COUNTIF function calculations
    • In the next empty column create a label 'TEXT_LoggedInAtUTC' -- in this example, this is column D
    • In the next empty column create a label 'TEXT_LoggedOutAtUTC' -- in this example this is column E
    • With your cursor in the first empty cell under TEXT_LoggedInAtUTC enter the following formula
      • =TEXT('TEXT_LoggedInAtUTC',"m/d/yyyy hh:mm:ss") -- in this example, the formula is =TEXT(B2,"m/d/yyyy hh:mm:ss")
    • Copy the formula down the remainder of the column
    • Repeat the formula for the TEXT_LoggedOutAtUTC column -- in this example, the formual is =TEXT(C2,"m/d/yyyy hh:mm:ss")
    • Copy the formual down the remainder of the column

Calculate the Licenses Consumed Using the Login/LogOut Data

  • Create a new column to the right of your current data (you may choose to leave one blank column in between) and label it 'DateTime'
    • Starting in the second cell of the TEXT_LoggedInAtUTC column (following the header), highlight the entire column down (Ctrl+Shift+Down Arrow) > Copy the data > Paste Values only under the DateTime column header -- this is so you copy the values over and not the formula
    • Repeat the copy instructions for all of the data in the TEXT_LoggedOutAtUTC column (excluding the header) > Paste Values only in the DateTime column UNDER the LoggedIn data you just pasted in
    • Highlight the entire DateTime column > select the Data tab > select Remove Duplicates 
    • In the Remove Duplicates modal > ensure the only column selected is DateTime > ensure the 'My data has headers' box is checked  > select OK
    • Highlight the DateTime column and select Sort (still under the Data tab)
    • In the Sort modal, Sort by 'DateTime'; Sort On 'Cell Values'; and Order 'A to Z' > ensure 'My data has headers' is checked > select OK (only the DateTime column should have sorted and not the rest of your worksheet data)
    • Your worksheet should resemble the example pictured below at this point

  • Create the following columns to the left of the DateTime column:
    • LogInCount  -- was there a log in at this moment
    • LogOutCount -- was there a log out at this moment
    • RSumLogIn -- running sum of the log ins
    • RSumLogOut -- running sum of the log outs
    • LicenseConsumption -- count of licenses consumed
  • Highlight all five columns you have just created and set the format to 'Number' and remove any decimals
  • Under LogInCount, enter the following formula:
    • =COUNTIF(the range of data in the TEXT_LoggedInAtUTC,the value in the DateTime field)
    • In this example, the formula is: =COUNTIF($D$2:$D$72089,G2)
    • Copy the formula down the column (this may take a few seconds if you have many rows of data)

  • Under LogOutCount, enter the following formula:
    • =COUNTIF(the range of data in the TEXT_LoggedOutAtUTC,the value in the DateTime field)
    • In this example, the formula is: =COUNTIF($E$2:$E$72089,G2)
    • Copy the formula down the column (this may take a few seconds if you have many rows of data)

  • Under RSumLogIn, enter the following formula:
    • =SUM(the LogInCount range)
    • In this example, the formula in cell J2 is: =SUM($H$2:$H2)

    • Copy the formula down the column (this may take a few seconds if you have many rows of data)
      • As you move down the column the formula should change the second value to match the current row -- in this example, if I place my cursor in cell J22, the formual should read =SUM($H$2:$H22

  • Under RSumLogOut, enter the following formula:
    • =SUM(the LogOutCount range)
    • In this example, the formula in cell K2 is: =SUM($I$2:$I2)
    • Copy the formula down the column (this may take a few seconds if you have many rows of data)
      • As you move down the column the formula should change the second value to match the current row -- in this example, if I place my cursor in cell K22, the formual should read =SUM($I$2:$I22

  • Lastly, under LicenseConsumption, enter the following formula to subtract the RSumLogOut from the RSumLogIn:
    • =RSumLogIn-RSumLogOut
    • In this example, the formula in cell L2 is: =J2-K2
    • Copy the formula down the column (this may take a few seconds if you have many rows of data)

Using the License Consumption Data to Create Charts and Analyze the Information

In the previous section, we used the user Log In and Log Out times to determine how many licenses were consumed during those timestamps. We can now use the LicenseConsumption column to create Charts to further determine peak usage per day and per hour. Please Note, the peak usage per Minute is available on the License Admin page in Connect. See the Monitoring Float License Usage article for more information.

  • With the worksheet open from the previous excercise, highlight all of the following columns:
    • DateTime
    • LicenseConsumption
  • Copy the data > create a new Worksheet > paste the Values only into the new Worksheet
  • Rename the new Worksheet "DailyMAXFloat"
  • Right-click on the DailyMAXFloat worksheet tab > select Move or Copy... > ensure the DailyMAXFloat sheet is selected and check the Create a copy checkbox > select OK
  • Rename the new Worksheet "HourlyMAXFloat"
  • Return to the DailyMAXFloat worksheet 
  • Because the DateTime column was converted to TEXT previously, you will need to take the following steps to show only the Date without the timestamp:
    • Insert a column before the DateTime column and label it 'DateOnly'
    • In the first cell after the label, enter the following formula =TEXT(DateTime,"m/dd/yyyy") --- In this example the formula is =TEXT(B2,"m/dd/yyyy")

    • Copy the formula down the DateOnly column
    • Highlight the entire DateOnly column > Copy the data > Paste the values only (this will remove the formula so that we can remove the DateTime column for a cleaner worksheet)
    • Delete the DateTime column (you should now only have the DateOnly column and the LicenseConsumption column remaining)
  • Place your cursor in the DateOnly cell (should be cell A1)
  • Select the Data tab > select Subtotal under the Outline grouping
  • In the Subtotal modal enter the following:
    • At each change in: DateOnly
    • Use function: Max
    • Add subtotal to: check LicenseConsumption
    • Check Replace current subtotals
    • Check Summary below data
    • select OK
    • Please note, the Max subtotal function may take a few moments to run 

    • After the Subtotals finish > select 2 in the Outline section to the left of the worksheet
      • You will see a MAX total for each day in your data -- this is the maximum number of licenses consumed during that particular day

    • In order to better see the peak usage, create a chart from the MAX values as follows:
      • With Outline 2 selected showing the MAX values, place your cursor in the DateOnly label cell above the data
      • Select the Insert tab > select the Scatter with Straight Lines and Markers chart

    • The Scatter chart will appear next to your data
    • Hover over the markers at the peaks > review the tooltip to determine the associated Date and Licenses Consumed value
      • In this example, 5/11/22 is showing 52 licenses consumed as my peak date
      • Please note the last marker on the chart is the Grand Max number - you may ignore this

    • Select the HourlyMAXFloat worksheet, and we will repeat most of these steps with a slight alteration to the DateTime formatting:
      • Insert a sheet column before the DateTime column and label it 'DateHourOnly'
      • In the first cell after the label, enter the following formula =TEXT(DateTime,"m/dd/yyyy hh") --- In this example the formula is =TEXT(B2,"m/dd/yyyy hh")

      • Copy the formula down the DateHourOnly column
      • Highlight the entire DateHourOnly column > Copy the data > Paste the values only (this will remove the formula so that we can remove the DateTime column for a cleaner worksheet)
      • Delete the DateTime column (you should now only have the DateHourOnly column and the LicenseConsumption column remaining)
    • Place your cursor in the DateHourOnly cell (should be cell A1)
    • Select the Data tab > select Subtotal under the Outline grouping
    • In the Subtotal modal enter the following:
      • At each change in: DateHourOnly
      • Use function: Max
      • Add subtotal to: check LicenseConsumption
      • Check Replace current subtotals
      • Check Summary below data
      • select OK
      • Please note, the Max subtotal function may take a few moments to run
    • After the Subtotals finish > select 2 in the Outline section to the left of the worksheet
      • You will see a MAX total for each day's hour in your data -- this is the maximum number of licenses consumed during that particular hour in that particular day
    • In order to better see the peak usage, create a chart from the MAX values as follows:
      • With Outline 2 selected showing the MAX values, place your cursor in the DateHourOnly label cell above the data
      • Select the Insert tab > select the Scatter with Straight Lines and Markers chart
    • The Scatter chart will appear next to your data
    • Hover over the markers at the peaks > review the tooltip to determine the associated Date, Hour and Licenses Consumed value
      • In this example, 5/11/22 in the 1700 hour is showing the highest peak of 52 licenses consumed
      • Please note the last marker on the chart is the Grand Max number - you may ignore this
      • In this example, as I hover over the peak markers across the chart, I can see that the 1600 and 1700 hours tend to be when the most licenses are consumed across the 90 day period I am reviewing
    Optional: You may choose to repeat the entire process above for the Creator named licenses, or any other license type you wish. 

    Additional Note: The horizontal axis in the above charts shows the row count for the series of Max totals. After much troubleshooting, I was unable to show the DateOnly or the DateHourOnly labels on the horizontal axis instead of the row count. 
    If anyone is a super Excel wizard, please feel free to comment and share how this may be possible!
      0 comments
      55 views