Subscribing a MicroStrategy Report to be delivered to Content Manager

As users work with MicroStrategy they will create records, right?  In this post I'll detail one approach for routinely getting those records into Content Manager with no user effort....

First create a new file transmitter named Content Manager:

Then create a new Content Manager device:

Select the new Content Manager transmitter type

Note the File Location is dynamic, based on the User's ID

Then update the user so that they have an address on the new device:

Note that user addresses can be automatically attached via a command manager script

Also need to ensure that the user can subscribe to a file (Content Manager is of type File):

Now the user can subscribe a report to a file:

This one will be scheduled to run every day at 5am:

When the schedule runs the PDF will be placed within a sub-folder unique for this user:

The Recipient ID in the folder name doesn't really help me.  The script will need to know the login of the user so that it can be registered within CM on their behalf.  I can lookup the login by using the command manager, as shown below.

If I execute the command manager from within powershell I won't get a nice table.  Instead I'll get some really ugly output:

I don't want the results file to include everyone.  I just need those who have addresses pointing to my Content Manager staging devices.  But I also want to know the name of the report and have it formatted to be more easily worked-with inside powershell.  I'll have to use a command manager procedure....

DisplayPropertyEnum iProperty = DisplayPropertyEnum.EXPRESSION;
ResultSet oUserProperties = executeCapture("LIST ALL PROPERTIES FOR USERS IN GROUP 'EVERYONE';");
ResultSet oPropertySet = null;
oUserProperties.moveFirst();
while (!oUserProperties.isEof()) {
    String sUserLogin = oUserProperties.getFieldValueString(DisplayPropertyEnum.LOGIN);
    String sID = oUserProperties.getFieldValueString(DisplayPropertyEnum.ID);
    String sUserName = oUserProperties.getFieldValueString(DisplayPropertyEnum.FULL_NAME);
    ResultSet oUserAddresses = (ResultSet)oUserProperties.getFieldValue(DisplayPropertyEnum.DS_ADDRESSES_RESULTSET);
    oUserAddresses.moveFirst();
    while (!oUserAddresses.isEof()) {
        String sAddressName = oUserAddresses.getFieldValueString(DisplayPropertyEnum.DS_ADDRESS_NAME);
        if (sAddressName.contains("Content Manager")) {
            ResultSet oProjects = executeCapture("LIST ALL PROJECTS;");
            oProjects.moveFirst();
            while (!oProjects.isEof()) {
                String sProjectName = oProjects.getFieldValueString(DisplayPropertyEnum.NAME);
                ResultSet oSubscriptions = executeCapture("LIST ALL SUBSCRIPTIONS FOR RECIPIENTS USER '" + sUserName + "' FOR PROJECT '" + sProjectName + "';");
                oSubscriptions.moveFirst();
                while (!oSubscriptions.isEof()) {
                    String sContent = oSubscriptions.getFieldValueString(DisplayPropertyEnum.CONTENT);
                    String sSubscriptionType = oSubscriptions.getFieldValueString(DisplayPropertyEnum.SUBSCRIPTION_TYPE);
                    if (sSubscriptionType.contains("File")) {
                        ResultSet oRecipientList = (ResultSet)oSubscriptions.getFieldValue(RECIPIENT_RESULTSET);
                        oRecipientList.moveFirst();
                        while (!oRecipientList.isEof()) {
                            String sRecipientAddress = oRecipientList.getFieldValueString(RECIPIENT_ADDRESS);
                            if (sRecipientAddress.contains(sAddressName)) {
                                printOut("||" + sID + ",\"" + sUserLogin + "\",\"" + sContent+"\"");
                            }
                            oRecipientList.moveNext();
                        }
                    }
                    oSubscriptions.moveNext();
                }
                oProjects.moveNext();
            }
        }
        oUserAddresses.moveNext();
    }
    oUserProperties.moveNext();
}

Executing this yields the following content within the log file:

Note that I included two pipe characters in my log file, so that I can later find & parse my results.  In powershell I'll invoke the procedure via the command manager, redirect the output to a file, load the file, find the lines with the double pipes, extract the data, and convert it from CSV. 

function Get-MstrCMUserSubscriptions {
    $command = 'EXECUTE PROCEDURE ListContentManagerUserSubscriptions();'
    $outFile = New-TemporaryFile
    $logFile = New-TemporaryFile
    $userSubscriptions = @("ID,Name,Report")
    try {
        Add-Content -Path $outFile $command
        $cmdmgrCommand = "cmdmgr -n `"$psn`" -u `"$psnUser`" -f `"$outFile`" -o `"$logFile`""
        iex $cmdmgrCommand$results += $_ }}
        $results = Get-Content -Path $logFile | Where-Object { $_ -like "*||*" }
        foreach ( $result in $results ) {
            $userSubscriptions += ($result.split('|')[2])
        }
    } catch {
    }
    if ( (Test-Path $outFile) ) { Remove-Item -Path $outFile -Force }
    if ( (Test-Path $logFile) ) { Remove-Item -Path $logFile -Force }
    return $userSubscriptions | ConvertFrom-CSV
}

That effort yields an array I can work with...

Last step, iterate the array and look for reports in the staging area.  As I find one, I submit it to Content Manager via the Service API and remove it from disk.  

$userSubscriptions = Get-MstrCMUserSubscriptions
foreach ( $userSubscription in $userSubscriptions ) {
    $stagingPath = ("$stagingRoot\\$($userSubscription.ID)")
    $reports = Get-ChildItem $stagingPath -Filter "*$($userSubscription.Report)*"
    foreach ( $report in $reports ) {
        New-CMRecord -UserLogin $userSubscription.Name -Report $userSubscription.Report -File $report.Name
        Remove-Item $report.Name -Force
    }
}

My New-CMRecord function includes logic that locates an appropriate folder for the report.  Yours could attach a schedule, classification, or other meta-data fetched from Microstrategy.  

Ensuring Records Managers can access Microstragegy

I've got a MicroStragtegy server environment with a Records Management group.  I'd like to ensure that certain new CM users always have access to MSTR, so that they have appropriate access to dashboards and reports.  For simplicity of the post I'll focus just on CM administrators.

Within MicroStrategy I'd like to create a new user and include them in a "Records Management" group, like so:

This implementation of MSTR does not have an instance of the REST API available, so I'm limited to using the command manager.  My CM instance is in the cloud and I won't be allowed to install the CM client on the MSTR server.  To bridge that divide I'll use a powershell script that leverages the ServiceAPI and Invoke-Expression cmdlet.  

First I need a function that gets me a list of CM users:

function Get-CMAdministrators {
    Param($baseServiceApiUri)
    $queryUri = ($baseServiceApiUri + "/Location?q=userType:administrator&pageSize=100000&properties=LocationLogsInAs,LocationLoginExpires,LocationEmailAddress,LocationGivenNames,LocationSurname")
    $AllProtocols = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'
    [System.Net.ServicePointManager]::SecurityProtocol = $AllProtocols
    $headers = @{ 
        Accept = "application/json"
    }
    $response = Invoke-RestMethod -Uri $queryUri -Method Get -Headers $headers -ContentType "application/json"
    Write-Debug $response
    if ( $response.TotalResults -gt 0 ) {
        return $response.Results
    }
    return $null
}

Second I need a function that creates a user within MSTR:

function New-MstrUser {
    Param($psn, $psnUser, $psnPwd, $userName,$fullName,$password,$group)
    $command = "CREATE USER `"$userName`" FULLNAME `"$fullName`" PASSWORD `"$password`" ALLOWCHANGEPWD TRUE CHANGEPWD TRUE IN GROUP `"$group`";"
    $outFile = ""
    $logFile = ""
    try {
        $outFile = New-TemporaryFile
        Add-Content -Path $outFile $command
        $logFile = New-TemporaryFile
        $cmdmgrCommand = "cmdmgr -n `"$psn`" -u `"$psnUser`" -f `"$outFile`" -o `"$logFile`""
        iex $cmdmgrCommand
    } catch {
        
    }
    if ( (Test-Path $outFile) ) { Remove-Item $outFile -Force }
    if ( (Test-Path $logFile) ) { Remove-Item $logFile -Force }
}

Last step is to tie them together:

$psn = "MicroStrategy Analytics Modules"
$psnUser = "Administrator"
$psnPwd = ""
$recordsManagemenetGroupName = "Records Management"
$baseUri = "http://10.0.0.1/HPECMServiceAPI"
$administrators = Get-CMAdministrators -baseServiceApiUri $baseUri
if ( $administrators -ne $null ) {
    foreach ( $admin in $administrators ) {
        New-MstrUser -psn $psn -psnUser $psnUser -psnPwd $psnPwd -userName $admin.LocationLogsInAs.Value -fullName ($admin.LocationSurName.Value + ', ' + $admin.LocationGivenNames.Value) -password $admin.LocationLogsInAs.Value -group $recordsManagemenetGroupName
    } 
}

After running it, my users are created!

Automating movement to cheaper storage

Now that I have an Azure Blob store configured, I want to have documents moved there after they haven't been used for a while.  I've previously shown how to do this manually within the client, but now I'll show how to automate it.

The script is very straight-forward and follows these steps:

1. Find the target store in CM
2. Find all documents to be moved
3. Move each document to the target store

Here's an implementation of this within powershell:

Clear-Host
Add-Type -Path "D:\Program Files\Hewlett Packard Enterprise\Content Manager\HP.HPTRIM.SDK.dll"
$LocalStoreName = "Main Document Store"
$AzureStoreName = "Azure Storage"
$SearchString = "store:$($LocalStoreName) and accessedOn<Previous Year"
$Database = New-Object HP.HPTRIM.SDK.Database
$Database.Connect()
#fetch the store and exit if missing
$Tier3Store = $Database.FindTrimObjectByName([HP.HPTRIM.SDK.BaseObjectTypes]::ElectronicStore, $AzureStoreName)
if ( $Tier3Store -eq $null ) {
    Write-Error "Unable to find store named '$($AzureStoreName)'"
    exit
}
#search for records eligible for transfer
$Records = New-Object HP.HPTRIM.SDK.TrimMainObjectSearch -ArgumentList $Database, Record
$Records.SearchString = $SearchString
Write-Host "Found $($Records.Count) records"
$x = 0
#transfer each record
foreach ( $Result in $Records ) 
{
    $Record = [HP.HPTRIM.SDK.Record]$Result
    $record.TransferStorage($Tier3Store, $true)
    Write-Host "Record $($Record.Number) transfered"
	$x++
}

I ran it to get the results below.  I forced it to stop after the first record for demonstration purposes, but you should get the idea.