Attribute VB_Name = "Module1" 'Sample Usage Log Visualizer Macro ' 'This is an example of a macro you can write in Excel to visualize the cleanlog usage data. 'You can modify the Macro in order to display additional information from the cleanlog, such 'as usage by cost center or End User. Since disconnected license file usage is not 'contained in the cleanlog, the Usage Log Visualizer cannot include this data in 'the graph. This macro was intended for use in Excel 2007. ' 'Important Information about What This Graph Represents: Each point on the graph represents 'the number of unique combinations of users and computers that used a particular license during 'a given period of time. For a non-concurrent user-based license, this number equals the number 'of licenses used in the time period. For all other license types, this number is greater than or 'equal to the number of license seats actually used during the time period. For example, if two 'different users launch and then exit an NI application at non-overlapping times during a time 'period, and they use a concurrent license, then only one license seat will actually be used in 'this time period. However, this graph reports two - the number of different user/computer 'combinations that used the license during that time period. To minimize this, enter a small 'number of hours between each point on the Time axis when prompted. Note that a large number 'of hours will increase the processing time and memory requirements for this Macro. ' 'To use the Usage Log Visualizer macro: '1. Parse the usage log with UsageLogUtility. '2. Launch Microsoft Excel 2007 and open the cleanlog file. Choose all the default 'values in the Text Import Wizard (You can click Finish). '3. Press Alt - F11 to open the Visual Basic Editor. '4. Click File " Import File and open UsageLogVisualizer.bas in the Editor. '5. Click Run " Run Macro to run the UsageLogVisualizer macro. If prompted, select 'the UsageLogVisualizer macro and press Run. If the usage log contains more than a few 'months of data, or there are many licenses in your license file, it may take several 'minutes for Excel to process. '6. Switch to the Excel Window and select the worksheet entitled "Usage Activity." 'Customizing Your Graph ' 'To customize your graph in Excel 2007, right-click on the Usage Activity graph and 'choose Select Data. ' 'The worksheets named for each license in your license file, located to the right of 'the Histogram worksheet, contain the number of times a unique user/computer combination 'used the license in a given time period. You can create your own custom graphs from this data. '*********************************************************************************** 'Constants you can modify for custom graphs Const STARTCOL = 7 '(Unlikely to change) Start column of date in log file Const ENDCOL = 8 '(Unlikely to change) End column of date in log file Const DEFAULTNUMLICENSES = 50 'Assume no more than 50 different types of licenses in the license file Const NUMTICKS = 30 'Desired number of ticks on the x-axis of the graph Const NUMWORKSHEETS = 2 'Number of worksheets that should be located in the Workbook to the ' left of the License Worksheets 'Global Variables Dim BeginStartDate As Date '*********************************************************************************** 'Sub UsageLogVisualizer() 'Pre: Assumes the first Worksheet in the WorkBook is a cleanlog. For more information about a cleanlog, ' see National Instruments KnowledgeBase article 49U88E8M. 'Post: Cleanlog is graphed on a worksheet entitled "Usage Activity". One Histogram ' worksheet is created that summarizes the number of unique user/computer combinations ' that used a license at each time bin of the cleanlog. A Licensing worksheet ' is created for each ' license in the cleanlog that summarizes the how many times a unique user/computer ' combination used a license during that time bin. '*********************************************************************************** Sub UsageLogVisualizer() Dim minsPerBin As Long 'number of minutes worth of data represented in one point of the graph Dim HistLength As Long 'number of bins required by minsPerBin given the cleanlog start and stop time Dim NumLicenses As Long 'the number of unique licenses listed in the cleanlog 'If more Worksheets than just the cleanlog are present, prompt user for deletion and then delete If ClearWorkbook(Sheets.Count) = False Then Exit Sub End If misPerBin = 0 'Prompt the User for the number of hours represented by a point, and convert it to minutes Call PromptForHoursPerBin(minsPerBin) 'If the user hit the Cancel button If minsPerBin = 0 Then Exit Sub End If 'Warn the user about the long wait time If MsgBox("After you press Enter, the window may freeze for up five minutes." _ & vbNewLine & vbNewLine & "(40 second wait on a Pentium 4 CPU 3GHz, 3 GB RAM " _ & "with a cleanlog of 6500 entries and 22 licenses).", _ 49, "Expected Wait") = vbCancel Then Exit Sub End If 'Turn off screen updating and auto calculation to improve performance Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'Create a new sheet to record the data to graph, and name it Histogram Sheets.Add After:=Worksheets(Worksheets.Count) Worksheets(Worksheets.Count).Name = "Histogram" 'Bin usage from entire cleanlog for each license and store it one License Worksheet per unique license Call BinLicenseUsage(HistLength, minsPerBin, NumLicenses) 'Add the date stamps to Histogram and Licensing Worksheets, for use in the graph and for reference Call AddDateStamps(HistLength, minsPerBin) 'Count how many users were using a given license in a given time bin (with no regard to how often 'they used it during that time bin) and put the number in the Histogram worksheet for the final graph Call CalculateLicenseUsagePerTimeBin(HistLength, NumLicenses) 'Graph license usage from the Histogram Call GraphLicenseUsage(HistLength, NumLicenses) 'Inform the user that they should switch back to the Excel window to see the graph MsgBox Prompt:="Graphing complete. Switch to the " _ & "Microsoft Excel window to examine the graph.", Buttons:=64, Title:="Success" 'Renable spreadsheet updating Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub '*********************************************************************************** 'Function NumBins(StartDate As Date, CurDate As Date, minsPerBin As Long) As Double 'Pre: StartDate - a Date ' CurDate - a Date that comes after or is the same as StartDate ' minsPerBin - number of minutes per bin 'Post: NumBins returns the number of bins between StartDate and CurDate, given the ' bin length of minsPerBin '*********************************************************************************** Function NumBins(StartDate As Date, CurDate As Date, minsPerBin As Long) As Double Dim diff As Double diff = DateDiff("s", StartDate, CurDate) 'Use Floor because usage for part of a bin is already represented in the graph by usage for the entire bin. NumBins = Application.WorksheetFunction.Floor((diff / 60) / minsPerBin, 1) End Function '*********************************************************************************** 'Function LicenseExists(Licenses() As String, License As String, NumLicenses As Long, LicenseIdx As Long) As Boolean 'Pre: Licenses - array of unique licenses read so far ' License - License for which to check ' NumLicenses - Number of licenses in the License array ' LicenseIdx - Output Parameter - index of License in Licenses. Undefined if LicenseExists ' is false 'Post: LicenseExists returns whether 'License' exists in the array 'Licenses'. '*********************************************************************************** Function LicenseExists(Licenses() As String, License As String, NumLicenses As Long, LicenseIdx As Long) As Boolean If NumLicenses = 0 Then LicenseExists = False Exit Function End If For Idx = 0 To NumLicenses - 1 If StrComp(Licenses(Idx), License, vbTextCompare) = 0 Then LicenseIdx = Idx LicenseExists = True Exit Function End If Next Idx LicenseExists = False End Function '*********************************************************************************** 'Function ColExists(SearchArea As Range, License As String, ColIdx As Long) As Boolean 'Pre: SearchArea - Area to search ' License - License to look for ' ColIdx - Output Parameter. The Column of the Worksheet in which the User/Comp Combination ' should be stored. 'Post: ColExists returns whether or not 'License' exists in the SearchArea 'Columns'. ' Also, it sets the output parameter ColIdx - see above. '*********************************************************************************** Function ColExists(SearchArea As Range, License As String, ColIdx As Long) As Boolean Dim NumCols As Long NumCols = SearchArea.Columns.Count For Idx = 1 To NumCols If StrComp(SearchArea.Cells(1, Idx).Value, License, vbTextCompare) = 0 Then ColIdx = Idx ColExists = True Exit Function End If Next Idx ColIdx = NumCols + 1 'returns the number of columns in the input Range If SearchArea.Cells(1, 1).Value2 = Empty Then ColIdx = 1 End If ColExists = False End Function '*********************************************************************************** 'Sub BinLicenseUsage(HistLength As Long, minsPerBin As Long, NumLicenses As Long) 'Pre: HistLength - Output Parameter. Number of Rows in the Histogram worksheet. ' minsPerbin - Number of minutes in a time bin. Expected to be an integer > 0. ' NumLicenses - Output Parameter. Number of unique licenses in the cleanlog. 'Post: A Licensing worksheet is created for each ' license in the cleanlog. It summarizes how many times a unique user/computer ' combination used a license during each time bin. '*********************************************************************************** Sub BinLicenseUsage(HistLength As Long, minsPerBin As Long, NumLicenses As Long) Dim LogLength As Long Dim CleanLog As Range Set CleanLog = Worksheets(1).UsedRange LogLength = CleanLog.Rows.Count Dim Row As Long Dim UserCompKey As String 'temporary key variable Dim License As String 'temporary License variable Dim Col As Integer 'temporary column variable for columns in the cleanlog file Dim Licenses() As String ReDim Licenses(DEFAULTNUMLICENSES) 'Array of unique Licenses Dim UserCompIdx As Long 'index of user/computer in the Licensing Worksheet Dim LicenseIdx As Long 'index of the license matching the current row of the cleanlog 'Initialize Variables HistLength = 0 BeginStartDate = CleanLog.Cells(1, STARTCOL) NumLicenses = 0 'number of unique licenses read so far For Row = 1 To LogLength 'for each line of the cleanlog UserCompKey = "" License = "" 'store the user/computer combination (located in the first and second columns of the 'cleanlog) in a String with a delimiter in between the user and computer. UserCompKey = CleanLog.Cells(Row, 1).Value & "|" & CleanLog.Cells(Row, 2).Value 'gather the license name For Col = 3 To 5 'including the version column here would be important if 'there were inactive seats broken out of the contract, but it also makes separate plots 'for upgrades of a license, so we remove it here. Set curCell = CleanLog.Cells(Row, Col) License = License & curCell.Value & " " Next Col 'Have we already seen this license before? If so, then what License index 'are we recording data under? If Not LicenseExists(Licenses, License, NumLicenses, LicenseIdx) Then Call AddLicense(CleanLog, Licenses, License, NumLicenses, Row) LicenseIdx = NumLicenses - 1 End If 'If it doesn't already exist, add the user/computer combination as a column 'to the appropriate License Worksheet 'LicenseIdx + 1 because it is zero indexed, and the worksheets are one-indexed. If Not ColExists(Worksheets(LicenseIdx + 1 + NUMWORKSHEETS).UsedRange, UserCompKey, UserCompIdx) Then 'add it as a column to the License Worksheet Worksheets(LicenseIdx + 1 + NUMWORKSHEETS).Cells(1, UserCompIdx + 1).Value = UserCompKey End If Call IncrementUsageValues(CleanLog, Row, minsPerBin, HistLength, LicenseIdx, UserCompIdx) Next Row Set CleanLog = Nothing End Sub '*********************************************************************************** 'Sub AddLicense(CleanLog As Range, Licenses() As String, License As String, NumLicenses As Long, Row As Long) 'Pre: CleanLog - comes from running the utility posted on National Instruments KnowledgeBase ' article 49U88E8M. The utility must be run on a proper usage log from NI VLM. ' Licenses() - Input/Output Parameter. Array of unique licenses in the cleanlog ' License - new license to add ' NumLicenses - Input/Output Parameter. Number of licenses in the Licenses() array ' Row - row that we are presently reading from the cleanlog 'Post: A worksheet is created to store the usage info for the new license. Also, the number ' of licenses is incremented, and the new license is added to the Licenses() array. '*********************************************************************************** Sub AddLicense(CleanLog As Range, Licenses() As String, License As String, NumLicenses As Long, Row As Long) Licenses(NumLicenses) = License NumLicenses = NumLicenses + 1 'Add the License to the Histogram sheet as a column label Worksheets("Histogram").Cells(1, NumLicenses + 1).Value = License '+1 to acct for date column 'Add a Licensing Worksheet for this new license Sheets.Add After:=Worksheets(Worksheets.Count) 'Cut the length to the maximum worksheet name length Worksheets(Worksheets.Count).Name = Left(CleanLog.Cells(Row, 3).Value, 7) & Left(CleanLog.Cells(Row, 4).Value, 5) & Left(CleanLog.Cells(Row, 5).Value, 17) & NumLicenses End Sub '*********************************************************************************** 'Sub IncrementUsageValues(CleanLog As Range, Row As Long, minsPerBin As Long, HistLength As Long, LicenseIdx As Long, UserCompIdx As Long) 'Pre: CleanLog - comes from running the utility posted on National Instruments KnowledgeBase ' article 49U88E8M. The utility must be run on a proper usage log from NI VLM. ' Row - row that we are presently reading from the cleanlog ' minsPerBin - Number of minutes in a time bin. Expected to be an integer > 0. ' HistLength - Output Parameter. Number of Rows in the Histogram worksheet. ' LicenseIdx - License index that is correlated to the License worksheet we are updating ' UserCompIdx - Column number we are going to update in the License worksheet. ' Correlates to the specific user/computer combination whose usage we will ' increment 'Post: Finds the time bin indecies that correspond to checkout/checkin times of the Row of the cleanlog. ' Increments the usage values in the correct license worksheet for the rows in between these ' two time bins. '*********************************************************************************** Sub IncrementUsageValues(CleanLog As Range, Row As Long, minsPerBin As Long, HistLength As Long, LicenseIdx As Long, UserCompIdx As Long) Dim CurStartDate As Date Dim CurEndDate As Date Dim FirstHistIdx As Long Dim LastHistIdx As Long Dim TempSheet As Range Dim HistRow As Long 'index of row to increment usage data 'Get current start time CurStartDate = CleanLog.Cells(Row, STARTCOL) 'Get current end time CurEndDate = CleanLog.Cells(Row, ENDCOL) 'Based on the time, calculate which bin(s) this usage data should be recorded in FirstHistIdx = NumBins(BeginStartDate, CurStartDate, minsPerBin) LastHistIdx = NumBins(BeginStartDate, CurEndDate, minsPerBin) 'If the number of bins in the histogram will now grow, store the new count. If LastHistIdx + 2 > HistLength Then 'Why +2? +1 for 0-indexed bin array and +1 for license name HistLength = LastHistIdx + 2 End If 'now update the usage count for each affected time bin on the License Worksheet Worksheets(LicenseIdx + 1 + NUMWORKSHEETS).Activate Set TempSheet = ActiveSheet.UsedRange For HistRow = FirstHistIdx + 2 To LastHistIdx + 2 '+1 for 0-indexed array and +1 for UserComp label 'In TempSheet, not UserCompIdx + 1 because ActiveSheet.UsedRange is returning the second column as the first, 'since the first column is empty. ActiveSheet.Cells(HistRow, UserCompIdx + 1).Value = TempSheet.Cells(HistRow, UserCompIdx).Value + 1 Next HistRow Set TempSheet = Nothing End Sub '*********************************************************************************** 'Sub AddDateStamps(HistLength As Long, minsPerBin As Long) 'Pre: HistLength - Number of Rows in the Histogram worksheet. ' minsPerBin - Number of minutes in a time bin. Expected to be an integer > 0. 'Post: Adds date stamps for each time bin to the Histogram summary worksheet and each ' License worksheet. Needed for graphing. '*********************************************************************************** Sub AddDateStamps(HistLength As Long, minsPerBin As Long) Dim i As Long Dim k As Long Dim TempArray As Variant 'Store Date Stamps in a temporary array for efficiency. If HistLength > 3 Then ReDim TempArray(HistLength - 2, 0) TempArray(0, 0) = Format(DateAdd("n", minsPerBin, BeginStartDate), "mm/dd/yyyy hh:nn") For i = 1 To HistLength - 3 TempArray(i, 0) = DateAdd("n", minsPerBin, TempArray(i - 1, 0)) Next i End If 'Add Date Stamps to each Worksheet For k = 2 To Worksheets.Count 'Because of Excel formatting issues, apply the first date directly instead of with the 'TempArray Worksheets(k).Cells(2, 1).Value = Format(BeginStartDate, "mm/dd/yyyy hh:nn") If HistLength > 3 Then Worksheets(k).Range("A3").Resize(HistLength - 2, 1) = TempArray End If Worksheets(k).Columns(1).AutoFit Next k Set TempArray = Nothing End Sub '*********************************************************************************** 'Sub CalculateLicenseUsagePerTimeBin(HistLength As Long, NumLicenses As Long) 'Pre: HistLength - number of Rows in the Histogram worksheet. ' NumLicenses - Number of unique licenses in the cleanlog. 'Post: Count how many users were using a given license in a given time bin ' (with no regard to how often they used it during that time bin) and put ' the number in the Histogram worksheet for the final graph '*********************************************************************************** Sub CalculateLicenseUsagePerTimeBin(HistLength As Long, NumLicenses As Long) Dim Usage As Integer Dim j As Long Dim Count As Long Dim TempSheet As Range Dim TempArray As Variant 'store in a temporary variable for efficiency ReDim TempArray(HistLength - 2, NumLicenses - 1) 'for each license/License Worksheet For i = 3 To Worksheets.Count 'for each time bin Set TempSheet = Worksheets(i).UsedRange For j = 2 To HistLength 'for each UserComp combination Usage = 0 Count = TempSheet.Columns.Count For k = 2 To Count If Not TempSheet.Cells(j, k).Value2 = Empty Then Usage = Usage + 1 End If Next k 'Set the usage value in the histogram for the license at the given time bin TempArray(j - 2, i - 3) = Usage Next j Next i Worksheets(2).Range("B2").Resize(HistLength - 1, NumLicenses) = TempArray Set TempSheet = Nothing Set TempArray = Nothing End Sub '*********************************************************************************** 'Sub GraphLicenseUsage(HistLength As Long, NumLicenses As Long) 'Pre: HistLength - number of Rows in the Histogram worksheet. ' NumLicenses - Number of unique licenses in the cleanlog. 'Post: Graphs the Histogram worksheet (the summary of license usage). ' Inserts the graph after the first worksheet. '*********************************************************************************** Sub GraphLicenseUsage(HistLength As Long, NumLicenses As Long) Dim HistCols As Long 'Create the Chart Charts.Add After:=Worksheets(1) 'There is a bug in Excel 2007 (not 2003) where a LineMarkers graph only draws the 'lines on the first series. To work around this bug, we first make it a LineStacked graph 'and then convert it back to LineMarkers. With Charts(1) .Name = "Usage Activity" .ChartType = xlLineStacked End With With Charts(1) .ChartType = xlLineMarkers End With 'Excel 2007 (but not 2003) will automatically create an incorrect 'graph after the above call Charts.Add. This section removes the graph. For Each s In Charts(1).SeriesCollection s.Delete Next s 'Plot the Histogram Worksheets("Histogram").Activate For HistCols = 2 To NumLicenses + 1 'NumLicenses + 1 bcs we reserve the first column for the date With Charts("Usage Activity").SeriesCollection.NewSeries .Name = ActiveSheet.UsedRange.Columns(HistCols).Range("A1") .Values = ActiveSheet.UsedRange.Columns(HistCols).Range(Cells(2, 1), Cells(HistLength, 1)) .XValues = ActiveSheet.UsedRange.Columns(1).Range(Cells(2, 1), Cells(HistLength, 1)) End With Next HistCols 'Fine-Tune Graph 'Only after the histogram is graphed will it let us put the title on the chart Charts("Usage Activity").Activate With ActiveChart .HasTitle = True .ChartTitle.Text = "NI Volume License Manager Software Usage Activity" .Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale .Axes(xlCategory).TickLabels.NumberFormat = "m/dd hh:mm;@" .Axes(xlCategory).TickMarkSpacing = Application.WorksheetFunction.Ceiling(HistLength / NUMTICKS, 1) .Axes(xlCategory).TickLabelSpacing = Application.WorksheetFunction.Ceiling((HistLength / NUMTICKS), 1) .Axes(xlCategory).TickLabels.Orientation = 90 .Axes(xlCategory).TickLabels.Font.Size = 11 .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time" .Axes(xlValue).HasTitle = True .Axes(xlValue).AxisTitle.Caption = "Number of Unique User/Computer Combinations that Used a License" .Axes(xlValue).AxisTitle.Font.Bold = False .Axes(xlCategory).AxisTitle.Font.Bold = False End With End Sub '*********************************************************************************** 'Function ClearWorkbook(CurNumSheets As Integer) As Boolean 'Pre: CurNumSheets - number of worksheets currently in the Workbook 'Post: If more than one worksheet is present in the workbook, ask user if he/she ' wants to delete the other worksheets. If the user says yes, then delete them. '*********************************************************************************** Function ClearWorkbook(CurNumSheets As Integer) As Boolean Dim SheetCount As Integer Dim SheetArray() As Integer If CurNumSheets > 1 Then If MsgBox("Continuing will delete all but the leftmost worksheet. If you need the data in any other worksheet, save your work in another workbook before proceeding.", 33, _ "Delete?") = vbOK Then ReDim SheetArray(CurNumSheets - 2) ' -2 because we are keeping one sheet and bcs VB variable arrays are zero-indexed For SheetCount = 0 To CurNumSheets - 2 ' -2 because we aren't deleting the first Worksheet, and because it is zero-indexed SheetArray(SheetCount) = SheetCount + 2 Next SheetCount Sheets(SheetArray).Select 'Disable Alerts so that Excel doesn't ask the user again if they want to delete the worksheet Application.DisplayAlerts = False 'Delete the worksheet ActiveWindow.SelectedSheets.Delete 'Reenable alerts Application.DisplayAlerts = True Else 'Return that the user selected no ClearWorkbook = False MsgBox ("Exiting Macro") Exit Function End If End If 'Return that we should continue with the macro execution ClearWorkbook = True End Function '*********************************************************************************** 'Sub PromptForHoursPerBin(minsPerBin As Long) 'Pre: minsPerBin - Output Parameter. Number of minutes in a time bin. ' Expected to be an integer > 0. 'Post: User enters the number of hours they desire between two points on the graph. ' Converts to minutes (see minsPerBin above). '*********************************************************************************** Sub PromptForHoursPerBin(minsPerBin As Long) Dim HoursPerBin As Single Do HoursPerBin = Application.InputBox(Prompt:="Enter the number of hours of usage activity between two points on the Time axis " _ & "(decimals permitted).", Title:="Graph Granularity", Default:=4, Type:=1) 'Error Checking (InputBox already validates that it is a number) If HoursPerBin = 0 Then minsPerBin = 0 Exit Sub ElseIf HoursPerBin < 0 Then MsgBox ("You must enter a positive decimal for the number of hours of usage activity between two points on the Time axis.") End If Loop Until HoursPerBin > 0 'Calculate the number of minutes per bin minsPerBin = Application.WorksheetFunction.Ceiling(60 * HoursPerBin, 1) End Sub