Generating Statistics and Uploading to Power BI

Another question over on the forum!  This time about generating statistics about how the system is being used.  I thought I might share something I played with earlier.

First I created a powershell script that generates a CSV file.  To add a row to the file, I search for the number of records registered every day between 1/1/2010 and today.  I track the registration counts separately per record type; and I don't track a day where 0 were registered for a given type.

My results look like this...


Once my extraction of metrics was completed, I created a free account on Power BI and uploaded the 20,000 row CSV file. I then created a few reports....

Here's one that includes filters the dataset to the previous two years and then displays the results in 3 different ways.  I can see year-over-year comparisons highlighting the growth of electronic records.  I could easily add ownership/business unit as an export metric too.


I don't have to have a report either.  They have an interactive "Ask a question" feature that lets you click you way into selecting from the dataset.


If you like this solution, give it a whirl!  Here's the powershell script I used to generate these metrics.

Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$db = New-Object HP.HPTRIM.SDK.Database
$startDate = (Get-Date -Year 2010 -Month 1 -Day 1)
$endDate = (Get-Date -Year 2017 -Month 12 -Day 31)
$totalDays = (New-TimeSpan -Start $startDate -End $endDate).Days
$x = 0
for($date = $startDate; $date -lt $endDate; $date = $date.AddDays(1))
    $dateString = ($date.ToShortDateString()) 
    Write-Progress -id 1 -Activity "Gathering Metrics" -Status "$($dateString)" -PercentComplete (($x/$totalDays)*100) 
    $recordTypes = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, RecordType
    $recordTypes.SearchString = "all"
    $y = 0
    foreach ( $recordType in $recordTypes ) 
        $recordsOfType = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, Record
        $recordsOfType.SearchString = "registeredOn:$($dateString) type:$($recordType.Uri)"
        if ( $recordsOfType.Count -gt 0 ) {
            $metric = new-object PSObject 
            $metric | add-member -membertype NoteProperty -name "Record Type Name" -Value $recordType.Name
            $metric | add-member -membertype NoteProperty -name "Date" -Value $dateString
            $metric | add-member -membertype NoteProperty -name "Count" -Value $recordsOfType.Count
            $metrics += $metric
$metrics | Export-Csv "E:\temp\metrics.csv" -NoTypeInformation

Once you've executed the script you login to Power BI and create a new dataset.  


Then I picked Files and selected the output from the Powershell


Then I created a new report and played with the interface until I got the dashboard in the original screenshot.  Once I was done I went back and updated my powershell script so that I can run it each day and it posts to the REST service.  I then scheduled the powershell to run daily and now have a dashboard with new metrics each day!.

If you want a script that does exactly what the original poster did, here's a script for that.  The approach is different, as it processes all system activity.  The first script simply checks each day for specific values.

Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$db = New-Object HP.HPTRIM.SDK.Database
$events = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $db, History
$events.SearchString = "objectType:Record date:this year"
$history = $events | where Record 
$metrics = @{}
foreach ( $event in $history ) 
    $recTypeName = ([HP.HPTRIM.SDK.History]$event).Record.RecordType.Name
    if ( $metrics.Contains($recTypeName) ) 
$metrics| Format-Table -AutoSize

I didn't really have any requirements so the output for this script is light at the moment.  The metrics are still interesting though.  The values represent how many actions have been performed by record type for the current year.  So there have been 40 "events" on Request for Information records so far this year.