How to Export License Usage and Calculate License Consumption in Excel

Katie Huckett
edited September 2022 in

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
image


  • 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
image


  • 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)
image


  • 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
image

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
image


  • 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)
image


  • 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)
image


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

    • 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
image


  • 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
image


  • 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)
image


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")
image


    • 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 
    image


    • 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
    image


    • 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
    image


    • 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
    image


    • 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")
    image


      • 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

    image


    • 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
    image

    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!
      Katie Huckett
      Senior Product Manager
      Jama Software

      Comments

      • Victor Cheung
        edited April 2023

        Hi Katie,

        Thank you for this article!

        I have some questions for the "DateTime" column instructions:

        • 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


        So after these 2 instructions, does that mean the DateTime column is twice as long as the initial data? (since we are pasting all the logged in and logged out times into one column?)
        Then the next instruction is to remove duplicates from this DateTime column.  This is where I'm not quite following the rationale. What is expected from the result of removing all duplicates?  Will the result be this column having equal amount of data (i.e. rows) as the original data?  I guess I'm not understanding what is the intent/meaning of this new DateTime column when it is composed of the unique set of logged in and logged out times -- especially when removing the duplicate values from this column will not necessarily result in parity with the original data.  

        Apologies, if I am misreading or misinterpreting the instructions!

        Thanks,
        victor

        victor
      • Katie Huckett
        edited April 2023

        @Victor Cheung - I apologize for the delayed response, I missed the email notification. Here are the answers to your questions below:

        So after these 2 instructions, does that mean the DateTime column is twice as long as the initial data? (since we are pasting all the logged in and logged out times into one column?)
        --
        Yes, the column would now be twice as long in length as the original row count

        Then the next instruction is to remove duplicates from this DateTime column.  This is where I'm not quite following the rationale. What is expected from the result of removing all duplicates? 
        -- The expected result is that there is only 1 unique value for any given login or logout date/time. There is a chance the original data does have multiple people logging in or out at the exact same date/time, but in order to obtain a correct calculation we only want 1 unique entry for any date/time value.


        Will the result be this column having equal amount of data (i.e. rows) as the original data?  
        -- The result would probably not have equal amount of data (i.e. rows) as the original data. This would only occur if you had exact number of logins/logouts happening simultaneously which is not very likely.

        I guess I'm not understanding what is the intent/meaning of this new DateTime column when it is composed of the unique set of logged in and logged out times -- especially when removing the duplicate values from this column will not necessarily result in parity with the original data.  
        -- The intent is to only have 1 data entry per date/time. If you had three entries for say, 4/14/2023 10:11 AM, then in the corresponding LogInCount or LogOutCount (whichever it falls under) would receive extra counts which would inflate your Running Sum numbers making it appear more licenses were consumed than in actuality.
        -- It might be rare, but if your users never did overlap in login or logout date/times then when you go to remove duplicates there may be none. But the step is there to ensure there aren't any duplicate values that could inflate your counts. 

        I hope that helps clarify this step. If not, please feel free to reach out again or you can email me at khuckett@jamasoftware.com.

        Katie Huckett
        Senior Product Manager
        Jama Software