Remove Unused Private Sheets *

Cadence Quarterly

Sites production w/ self-service

Benefits:

• Increase performance
• Reduce maintenance

Goal

In environments where self-service is enabled, i.e. users have the ability to create their own private sheets on published applications, with a high number of users, the number of private sheets can ultimately grow very large. The goal of this section is to illustrate how to put policies/practices in place to consistently keep the number of private sheets under control–allowing for a tidy/more performant site.

Table of Contents

• Suggested Prerequisites
• Audit Activity Log
• Operations Monitor
  • Confirm Operations Monitor is Operational
• Identification of Unused Private Sheets
• Suggested Actions
  • Retention Policy
  • Process
• Bulk Private Sheet Removal
  • Video Walk-Through
  • Script to Tag Unused Private Sheets
  • Script to Delete Tagged Sheets
    • Prerequisites
    • Steps

Suggested Prerequisites

• Remove/Quarantine Unused Apps

Audit Activity Log

As of the February 2019 release, an improvement was added to the product to log sheet usage at default log levels. This enables the ability to measure sheet adoption as well as manage the amount of sheets in the applications–keeping them trimmed to only what is being leveraged.

Ensure that the Audit Activity log level is set to Basic for every engine.

Operations Monitor

This page leverages the Operations Monitor. Please refer to the Operations Monitor page for an overview and relevant documentation links.

Confirm Operations Monitor is Operational

Navigate to the Monitoring apps and select the Details button (info icon) on the Operations Monitor application. Confirm that the application’s data is up-to-date.

If the Operations Monitor is not up-to-date, please refer to the Operations Monitor Documentation for configuration details and troubleshooting steps.

Identification of Unused Private Sheets

Open up the Operations Monitor application, inside of Monitoring Apps stream:

Navigate to the Sheet Usage sheet.

Sort by Unused Private Sheets descending.

Now ideally, any unused applications should be removed before this sheet pruning activity. This will allow for bulk removal of those private sheets as the entire app has been identified as unused. This example is intended to only remove private sheets from published applications that are used. Ensure that Remove/Quarantine Unused Apps has been followed first.

From the App Stream column, select Unpublished, and then select Select alternative so that all streams are selected (all published applications). Ensure that if a Quarantine stream exists, that it is also deselected – as there is no need to prune applications that are already marked for potential removal.

Now on this example server, the Operations Monitor application has been selected, and it is visible that there is a single unused prviate sheet. In a real-world environment, there would potentially be many, many more, but as this is taken from a rarely used testing environment, this simple example will suffice.

On the Sheet Usage table, select Private Sheet under the Sheet Type column.

In this scenario, the App Usage sheet has not been used in over one month but less than two. Ideally, private sheets should only be removed if they have not been used in > 90 days or more, but this needs to be defined by a policy decided internally. Please refer to the Retention Policy section below.

Suggested Actions

Retention Policy

It is highly suggested to have a corporate policy in place for unused private sheet retention. As the number of these sheets can grow very large and it can become quite difficult to manage manually, an automatic approach is suggested. In order to implement this style of policy, there should be notifications in place so that users are informed that action is needed to retain important assets.

Example Policy

• Unused sheets will be purged after > 90 days, run at the start of each business quarter.
• An email will go out to all Qlik users two weeks prior to the start of the quarter, letting them know that in order retain their assets, they must ensure that they have been accessed during the next two weeks. The simplest way to do this is is with a generic email to all Qlik users, however if Qlik NPrinting is available to the corporation, NPrinting could be used to customize the emails to include the specific sheets that would be expiring sent to each specific individual.
• A reminder email be sent one week following the initial email.

A Warning

Deleting sheets is a permanent operation. Ensure that every measure/precaution/warning has been taken so that users are well aware of the resulting action.

Process

1. Within the Operations Monitor on the Sheet Usage sheet, after following process for identifying unused sheets, export the Sheet Usage table to Excel. This output should only contain the private sheets which have been qualified by the aforementioned activity.

2. Either manually or programmatically tag the sheets with a desired tag in the QMC. Ensure that this tag is specific to this process, for example UnusedPrivateSheet. If programmatically tagging, follow the Script to Tag Unused Private Sheets section.

3. It is suggested to contact the owners of the private sheets so that they can access the sheets that they desire to retain (therefore rendering them no longer “unused”). Refer to the Retention Policy example above.

4. After a decided-upon amount of time has passed to allow users to access their content, the tag should then be removed from the sheets. The simplest way to achieve this is to delete the tag from the QMC, as that will remove it from all resources in one shot.

5. Ensure that the Operations Monitor has been reloaded, and then repeat steps 1 and 2, re-creating the tag.

6. Delete the remaining tagged sheets by following the Script to Delete Tagged Sheets section below.

Bulk Private Sheet Removal

The below script snippet requires the Qlik CLI for Windows.

The script below will tag any private sheets with the tag UnusedPrivateSheet. It expects an Excel file (XLSX) as an input, where the name of the column with the Sheet Id is specified. This allows for the Qlik Administrator to export a filtered down list from the Sheet Usage table in the Sheet Usage sheet of the Operations Monitor.

Video Walk-Through

Script to Tag Unused Private Sheets

# Function to tag private sheet ids from excel and tag them
# Assumes the ImportExcel module: `Install-Module -Name ImportExcel`
# Assumes tag exists, such as 'UnusedPrivateSheet'
# GUID validation code referenced from: https://pscustomobject.github.io/powershell/functions/PowerShell-Validate-Guid-copy/

################
## Parameters ##
################

# Assumes default credentials are used for the Qlik CLI for Windows Connection

# machine name
$computerName = '<machine-name>'
# leave empty if windows auth is on default VP
$virtualProxyPrefix = '/default'
# set the number of days back for the app created date
# fully qualified path to excel file with sheet ids
$inputXlsxPath = '<absolute file path>/<filename>.xlsx'
# column number of sheet id column in Excel file
$sheetIdColumnNumber = '9'
# the desired name of the tag to tag sheets with - it must exist in the QRS
$tagName = 'UnusedPrivateSheet'
# directory for the output file
$outFilePath = 'C:\'
# desired filename of the output file
$outFileName = 'tagged_private_sheets'

################
##### Main #####
################

# set the output file path
$outFile = ($outFilePath + $outFileName + '.csv')

# set the computer name for the Qlik connection call
$computerNameFull = ($computerName + $virtualProxyPrefix).ToString()

# if the output file already exists, remove it
if (Test-Path $outFile) 
{
  Remove-Item $outFile
}

# function to validate GUIDs
function Test-IsGuid
{
	[OutputType([bool])]
	param
	(
		[Parameter(Mandatory = $true)]
		[string]$ObjectGuid
	)

	[regex]$guidRegex = '(?im)^[{(]?[0-9A-F]{8}[-]?(?:[0-9A-F]{4}[-]?){3}[0-9A-F]{12}[)}]?$'
	return $ObjectGuid -match $guidRegex
}

# import sheet ids from excel
$data = Import-Excel $inputXlsxPath -DataOnly -StartColumn $sheetIdColumnNumber -EndColumn $($sheetIdColumnNumber + 1)

# validate GUIDs and only use those (handles nulls/choosing wrong column)
$sheetIds = $data | foreach { $_.psobject.Properties } | where Value -is string | foreach { If(Test-IsGuid -ObjectGuid $_.Value) {$_.Value} }

# connect to Qlik
Connect-Qlik -ComputerName $computerNameFull -UseDefaultCredentials -TrustAllCerts

# add headers to output csv
Add-Content -Path $outFile -Value $('SheetObjectName,SheetObjectSheetId,SheetObjectAppId,SheetObjectAppName')

# GET desired tag JSON
$tagsJson = Get-QlikTag -filter "name eq '$tagName'" -raw

# get the id of the tag
$tagId = $tagsJson.id

# if the tag exists
if($tagsJson) {

	# for each tag
	foreach ($sheetId in $sheetIds) {

		# GET the object, ensuring it is a private sheet
		$sheetObjJson = Get-QlikObject -filter "published eq false and approved eq false and id eq $sheetId" -full -raw

		# if the object exists and is a private sheet
		if ($sheetObjJson) {

			# set a flag to check if the tag is already assigned to the sheet
			$tagAlreadyThere = $false

			# get the current tags assigned to sheet, if any
			$currentTags = $sheetObjJson.tags

			# for each tag
			foreach ($tag in $currentTags) {

				# if the target tag is already there, set the flag to "true"
				if ($tagId -eq $tag.id) {
					$tagAlreadyThere = $true
					break
				}
				else {
					continue
				}
			}

			# get the sheet name, app id, and app name
			$sheetObjName = $sheetObjJson.name
			$sheetObjAppId = $sheetObjJson.app.id
			$sheetObjAppName = $sheetObjJson.app.name

			# if the tag isn't already there, add it
			if (!$tagAlreadyThere) {
				$sheetObjJson.tags += $tagsJson

				# convert to JSON for the PUT
				$sheetObjJson = $sheetObjJson | ConvertTo-Json

				# PUT the sheet with the new tag
				Invoke-QlikPut -path /qrs/app/object/$sheetId -body $sheetObjJson
			}

			# write output
			Add-Content -Path $outFile -Value $($sheetObjName + ',' + $sheetId + ',' + $sheetObjAppId + ',' + $sheetObjAppName)
		}

		# the sheet is not a community sheet
		else {
			$sheetId + ' is not a private sheet. Skipping.'
		}
	}
}

# the tag doesn't exist
else {
	"Tag: '" + $tagName + "' doesn't exist. Please create it in the QMC."
}

Once the script has been run above, and a review of the tagging has been confirmed as correct, the script below can be run to permanently delete these base/community sheets. This process cannot be reversed.

Script to Delete Tagged Sheets

In order to completely remove sheets from both an application and the repository database, the Qlik Engine JSON API must be used. To work with this API, the sample script leverages Enigma.js.

Prerequisites

• NodeJS

This process uses NodeJS to interact with the Qlik Engine JSON API. To confirm that NodeJS is installed and properly configured, run the following commands in cmd.exe:

• node --version
• npm --version

Steps

1. Download the following files from here and place them in a desired folder.
   • remove_tagged_private_sheets.js
   • package.json
2. Edit the following mandatory variables in remove_tagged_private_sheets.js
   • host
   • TAG_TO_SEARCH_FOR
3. Open a cmd prompt, and navigate to the folder from step 1.
4. Enter npm install
5. To execute the program, enter node remove_tagged_private_sheets.js
6. Refer to both log.txt and output.csv

Tags

#quarterly

#asset_management

#apps

#sheets

#operations_monitor