Generating CM 2012 AppStore Deeplinks using Powershell

When working with the Application Catalog in CM 2012, there may be situations where you want to automate the approvals of install requests. While this task itself is worthy of discussion (will cover in a future post), this post will focus on a small part of that process, generating Deeplinks for applications.

Deeplinks are used for linking directly to an application detail page in the App Catalog. So, lets assume that you have your automated approvals solution in place, once you approve an application install, you want to send your end user a link to that application so they can install it. Great, so whats so hard about that?

The issue is that your deeplink looks something like this:


Ahhhhh!!!…..Where do all these random numbers and letters at the end of the link come from?!? Well surely its stored somewhere in the CM database? Nope. Maybe there’s a CM Powershell cmdlet to get this value? Nope. Well how about the in the CM SDK? Nope, not there either.

It turns out that this long string is a Base 64 encoding of the ModelName of the application you want to link to. This means that all we need to do is find that charset that was used and we should be able to encode/decode deeplinks. Problem is, none of the default charsets were used. UTF-8, UTF-16, Unicode, ASCII…..None of these charsets returned desired results.

So what now? Well, we know the decoded and encoded value and we know that it is a Base 64 encoding. This means that every 4 characters in the encoded string correlates to 1 character on the decoded string. Using this knowledge we can generate the charset and throw it into hash tables that we can then use for encoding and decoding. The below script contains those hash tables and the functions that are used to encode and decode the strings.

#Dictionary values for encoding/decoding
$codes = @{}
$codes.Add('/', '2F00')
$codes.Add('_', '5F00')
$codes.Add('-', '2D00')
$codes.Add('0', '3000')
$codes.Add('1', '3100')
$codes.Add('2', '3200')
$codes.Add('3', '3300')
$codes.Add('4', '3400')
$codes.Add('5', '3500')
$codes.Add('6', '3600')
$codes.Add('7', '3700')
$codes.Add('8', '3800')
$codes.Add('9', '3900')
$codes.Add('a', '6100')
$codes.Add('b', '6200')
$codes.Add('c', '6300')
$codes.Add('d', '6400')
$codes.Add('e', '6500')
$codes.Add('f', '6600')
$codes.Add('o', '6F00')
$codes.Add('p', '7000')
$codes.Add('i', '6900')
$codes.Add('l', '6C00')
$codes.Add('n', '6E00')
$codes.Add('t', '7400')

$upperCodes = @{}
$upperCodes.Add('A', '4100')
$upperCodes.Add('B', '4200')
$upperCodes.Add('C', '4300')
$upperCodes.Add('D', '4400')
$upperCodes.Add('E', '4500')
$upperCodes.Add('F', '4600')
$upperCodes.Add('I', '4900')
$upperCodes.Add('S', '5300')

function encode([string]$AppID)
	$encoded = $null
    #foreach char in string, get encoded value from dictionary
	foreach ($char in [Char[]]$AppID)
        #if char is uppercase look in uppercase dictionary
		if ([char]::IsUpper($char)) { $encoded = $encoded + $upperCodes.Get_Item($char.ToString()) } 
		else { $encoded = $encoded + $codes.Get_Item($char.ToString()) }
	return $encoded

function lookUpKey($code)
    $returnVal = $null
    #look in lowercase dictionary for code
    if ($codes.ContainsValue($code)) 
        foreach ($key in ($codes.GetEnumerator() | Where-Object {$_.Value -eq $code}))
            $returnVal = $key.Name
    #if not there look in uppercase dictionary
    elseif ($upperCodes.ContainsValue($code))
        foreach ($key in ($upperCodes.GetEnumerator() | Where-Object {$_.Value -eq $code}))
            $returnVal = $key.Name
    return $returnVal

function decode([string]$AppIDSig)
    $i = 0
    $Code = $null
    $decoded = $null

    foreach ($char in [Char[]]$AppIDSig)
        #if less then 4 characters in code grab next character
        if ($i -le 3)
            #build 4 char codes
            $code = $code + $char
        #if 4 characters are in code lookup the decoded value
            $decoded = $decoded + (lookUpKey $code)
            #reset code and start building 4 char code again
            $code = $char
            $i = 1
    #foreach statement does not iterate the last time to get last code, look up value for last code here
    $decoded = $decoded + (lookUpKey $code)

    return $decoded

CM 2012 Application Store Reporting

SCCM 2012 brought a lot of great new features to the fold. One of the most notable is the Application Catalog which allows users to install/request software through a web interface.

Awesome, right?!…… Yes, but what if we want to track how many requests are coming through the App Catalog? Or, get the install status of these requests? There are no out of box reports provided that give us this amount of detail, so, we have to create our own.

Should be easy enough, but, this is where it gets complicated. There are two types of requests that come through the App Catalog, those that require approval, and those that do not. These requests are tracked in two different locations, UserAppModelSoftwareRequest and v_UserAppRequests respectively. This means that if we want to report on every request we need to combine the data in these two locations. Additionally, we want to join some other tables to get some supplemental data such as Username, machine name, install state, name of the application, etc. This other information can be gathered from the below locations:

  • User Info: v_R_User
  • Machine Info: v_R_System
  • Install State: vAppDeploymentResultsPerClientUser
  • Application Info: fn_ListApplicationCIs(1033)

So now that we know where all our information is located, we can combine it to our desired output. The below query does just that.

Declare @tempTable table (ReqID nvarchar(256), UserResourceID int, MachineResourceID int, ModelName nvarchar(512), DisplayName nvarchar(512), RequestDate DateTime)

Insert Into @tempTable
  select app.RequestID as 'ReqID',
       (select top 1 ResourceID from v_r_user where Unique_User_Name0 = umr.UniqueUserName) as 'UserResourceID',
       app.AppID as 'ModelName',
       (select DisplayName from fn_ListApplicationCIs(1033) where ModelName = app.AppID AND IsLatest = 1) as 'DisplayName',
       app.CreationTime as 'RequestDate'
  from UserAppModelSoftwareRequest app
    join UserMachineRelation umr on app.RelationshipResourceID = umr.RelationshipResourceID

Insert Into @tempTable
  select  app.RequestGuid as 'RecID',
       (select ResourceID from v_r_User where Unique_User_Name0 = app.Unique_User_Name0) as 'UserResourceID',
       sys.ResourceID as 'MachineResourceID',
       app.LastChanged as 'RequestDate'
  from v_UserAppRequests app
    join v_r_System sys on app.Netbios_Name0 = sys.Netbios_Name0
  where CurrentState = 4;

WITH Combined as (select distinct(app.ReqID),
                         'Status' = CASE WHEN dep.EnforcementState >= 1000 and dep.EnforcementState < 2000 THEN 'Success'
                         WHEN dep.EnforcementState >= 2000 and dep.EnforcementState < 3000 THEN 'InProgress'
                         WHEN dep.EnforcementState >= 3000 and dep.EnforcementState < 4000 THEN 'RequirementsNotMet'
                         WHEN dep.EnforcementState >= 4000 and dep.EnforcementState < 5000 THEN 'Unknown'
                         WHEN dep.EnforcementState >= 5000 and dep.EnforcementState < 6000 THEN 'Error' END
                  from @tempTable app
                    join vAppDeploymentResultsPerClientUser dep on app.UserResourceID = dep.ResourceID and dep.Descript = app.DisplayName)

First, we declare a temp table and insert our requests from each respective location. Then, using this temp table as our base dataset, we join the other listed tables to gather our supplemental data. At this point we have all the data we need and we just need to decide how we want to present it. If you want to just get an overview of the requests and their install statuses you can add this:

Select 'Application Requests',
       (select count(*) from Combined) as 'Total',
       (SUM(CASE WHEN Status = 'Success' then 1 else 0 end)) as 'Success',
       (SUM(CASE WHEN Status = 'InProgress' then 1 else 0 end)) as 'InProgress',
       (SUM(CASE WHEN Status = 'RequirementsNotMet' then 1 else 0 end)) as 'RequirementsNotMet',
       (SUM(CASE WHEN Status = 'Error' then 1 else 0 end)) as 'Error',
       (SUM(CASE WHEN Status = 'Unknown' then 1 else 0 end)) as 'Unknown'
from Combined

Otherwise, if we want the detailed info for each request you can simply use:

Select * from Combined