How to identify where SharePoint App instances are used on SharePoint 2013 site collection?

One of the most existing new features for SharePoint 2013 is SharePoint Apps. After configure an environment for apps for SharePoint, users will install apps including Apps from SharePoint App store, Access Apps, and your customized Apps to their site collections. As SharePoint farm administrator, we would like to identify where all the different apps are used on SharePoint 2013 site collection. We would like to have a report to display the app name, site page use the app, the access database name if this is Access app. There is no documentation from Microsoft how to achieve this and here are different approaches. There are multiple ways to identify where SharePoint App instances are used on SharePoint 2013 site collection. Here are the details.

1. The first way is for site collection owner. You could go to Site Settings-> Site Collection App Permissions and you will see all the apps the site collection has permission to access. Although this is only display the apps site collection could access, if you do not delete the permission, this will display all the apps on the site.  I also noticed that is you delete the app permission, there is not an easy to add back the permission. As a result, users will not able to access the app from this site.

2. The second way is for IT developers and infrastructure team. You could run the following database query on the content DB that hold the site collection to generate the app usage report. 

 

SELECT DISTINCT TOP 1000

      AIP.[Value]

      ,(SELECT [WSS_Content_P2].[dbo].[AppInstallationProperty].[Value]

        FROM [WSS_Content_P2].[dbo].[AppInstallationProperty]

        WHERE  [WSS_Content_P2].[dbo].[AppInstallationProperty].[ValueKey] = 'DbName'

        and [WSS_Content_P2].[dbo].[AppInstallationProperty].[InstallationId] = AIP.[InstallationId] ) as DbName

      ,AP.[LaunchUrl]

      ,AIP.[SiteId]

      ,AIP.[InstallationId]

        

  FROM [WSS_Content_P2].[dbo].[AppInstallationProperty] as AIP 

  INNER JOIN [WSS_Content_P2].[dbo].[AppInstallations] as AP

  on AIP.[InstallationId] = AP.[Id]

  and AIP.[ValueKey] = 'containerWebName'

As you can see from the query result, this way you could not only report all the apps used on the site, but also indicate whether the app is a access app. If this is access app, it will also display the database name. You could enhance this query to include site URL or other data. This is not recommended to run production that will violate the Microsoft support contract!

3. The third way we could use powershellto display App instances on each site.  You have to pass different URL to get all the Apps installed in the farm. If you look at the LaunchUrl for each app, you will notice ALL Access Apps will have value "~appWebUrl/default.aspx" and other apps will have "{StandardTokens}". This is the quick way to identify the Access apps.

The command is:


Get-SPAppInstance -Web http://spsbx15/

The code snnipt to get all app inside the farm is listed below.


$webApps = Get-SPWebApplication;

foreach($webApp in $webApps)
{
        foreach($site in $webApp.Sites)
        {
                foreach($web in $site.AllWebs)
                {
                    $appInstances = Get-SPAppInstance -Web $web.Url -ErrorAction SilentlyContinue;

                    foreach($appInstance in $appInstances)
                    {
                        if($appInstance.LaunchUrl -like "*StandardToken*" -and $appInstance.Status -eq "Installed")
                        {
                            $rows += New-Object -TypeName PSObject -Property @{     "Site Url" = $site.Url
                                                                    "Web Url" = $web.Url
                                                                    "App Name" = $appInstance.Title
                                                                    "App Id" = $appInstance.Id
                                                                    "Status" = $appInstance.Status
                                                                    "App Type" = "SharePoint App"                                                
                                                                    } | Select-Object "Site Url", "Web Url", "App Name", "App Id", "Status", "App Type";
                        }
                        elseif($appInstance.Status -eq "Installed")
                        {
                            $rows += New-Object -TypeName PSObject -Property @{     "Site Url" = $site.Url
                                                                    "Web Url" = $web.Url
                                                                    "App Name" = $appInstance.Title
                                                                    "App Id" = $appInstance.Id
                                                                    "Status" = $appInstance.Status    
                                                                    "App Type" = "Access App"                                            
                                                                    } | Select-Object "Site Url", "Web Url", "App Name", "App Id", "Status", "App Type";
                        }
                        elseif($appInstance.Status -ne "Installed")
                        {
                            $rows += New-Object -TypeName PSObject -Property @{     "Site Url" = $site.Url
                                                                    "Web Url" = $web.Url
                                                                    "App Name" = $appInstance.Title
                                                                    "App Id" = $appInstance.Id
                                                                    "Status" = $appInstance.Status    
                                                                    "App Type" = "Unknown"                                            
                                                                    } | Select-Object "Site Url", "Web Url", "App Name", "App Id", "Status", "App Type";
                        }
                    }
                }
            }
}
 
4. The forth way are still researching that should use SharePoint API to do the similar search as listed in two ways. However, we are still in research stage and will keep you posted whence it has been done. If you have a way using API to get this information, please share.