How to delete sheets(IDs available in external sheet) via repository API

[rohitk1609]

Hi techies,

I have a use case where I need to delete Qlik app sheets which are unused since last one year. I have collected the concern sheet ids from operation monitoring app. Lets say concern sheets are 500 from 1 app. I am looking for a way that I delete such sheets from   repository API by picking sheet ids from external sheet which has sheet id exported from operation monitoring app.

I have found a document which has information how to delete single sheet in one request but I need to send request for all 500 sheets in one go, like picking sheet id one by one and send request to QSR database via API.

https://support.qlik.com/articles/000055464

Please help me to achieve my goal

Thanks in Advance

Rohit

[sogloqlik]

Facing the same issue.
Help would be appreciated.
(Or much more modern management console if someone from QLIK is reading this post)

[Levi_Turner · Accepted solution]

See this piece of documentation that my team has created: https://adminplaybook.qlik-poc.com/docs/asset_management/apps/remove_unused_private_sheets.html

[rohitk1609]

Thanks Levi. You have achieved the goal. I am raising this concern since months. I am not sure you picked my request especially and desgined this important article but I am pretty sure this article will be famous across Qlik community. I will promote it best as I can.

Thanks,

Rohit

[Levi_Turner]

I'd love to say yes, but no, this is a general problem that we've seen demand for and thus put together some documentation for. Glad you found success with it. The whole project (http://adminplaybook.qlik-poc.com/) will be promoted more widely in the upcoming weeks.

[rohitk1609]

Hi Levi,

I am reading your document https://adminplaybook.qlik-poc.com/docs/asset_management/apps/flag_unused_base_community_sheets.html

My question is, once we tag certain sheets which are unused, shall we delete them from QMC

instead of going to second document https://adminplaybook.qlik-poc.com/docs/asset_management/apps/remove_unused_private_sheets.html

Thanks,
Rohit

[Levi_Turner]

Deleting sheets in the QMC uses the Qlik Repository API. This does not remove the actual sheet from the binary on disk as the Qlik Repository Service does not operate on this level.

We've intentionally not provided an example script to do the same process for Base + Community sheets. Since these sheets are available across many users, this process should be done with caution, intentionally, and ideally using the Hub interface.

[rohitk1609]

It am shocked to know that Deleting Sheet or App object from QMC doesn't delete the sheet from binary on disk Physical file.

Can you please tell me what else stays at back ground when we delete it from QMC, i.e. apps, image ?

Second as you said QMC uses Repository API which couldn't reach to binary on disk, in same manner POSTMAN also uses Repository API so it doesn't delete the physical data(Sheet) from binary on disk ? 

I found another solution which has two things to do

1. Install Qlik certificates which sends API request

2. Powershell script which deletes the sheet present in .csv file in disk.

I am not sure it uses Repository API or Engine API. Can you please look into below code and help me to know that which repository it uses ?

I tried to add it but due to its extension I can't add it here so here is the powershell code:

$hdrs = @{}
$hdrs.Add("X-Qlik-xrfkey","1234567890123456")
$hdrs.Add("X-Qlik-User","UserDirectory=Internal;UserId=sa_api")
$cert = "77b23bde29c31f0c8ebd52a99"
$url = "https://host:4242/qrs/app/object/"
$xrf = "?xrfkey=1234567890123456"

foreach($line in [System.IO.File]::ReadLines("c:\powershell\TestSheets.txt"))
{

add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Ssl3, [Net.SecurityProtocolType]::Tls, [Net.SecurityProtocolType]::Tls11, [Net.SecurityProtocolType]::Tls12

Invoke-RestMethod -Uri $url$line$xrf -Method Delete -Headers $hdrs -CertificateThumbprint $cert

}

[Levi_Turner]

> Can you please tell me what else stays at back ground when we delete it from QMC, i.e. apps, image ?

As far as I am aware app.objects are the primary thing which is affected. Apps are removed from disk. Images are removed from disk. 

> Second as you said QMC uses Repository API which couldn't reach to binary on disk, in same manner POSTMAN also uses Repository API so it doesn't delete the physical data(Sheet) from binary on disk ? 

Correct.

> I am not sure it uses Repository API or Engine API. Can you please look into below code and help me to know that which repository it uses ?

It uses the Repository API. The path that is called is /qrs/ and the port that it accesses is 4242 which is the Repository port.

[rohitk1609]

Hi Levi,

I have started testing solution. I will give you my findings which would help you to add new points to your docs.

This is what I read today:

https://adminplaybook.qlik-poc.com/docs/asset_management/apps/remove_quarantine_unused_apps.html

Here are my findings:

1. You have asked, In the App Details table object, sort by Last Accessed field and scroll to old dates or null dates.

Sort of Last Accessed column will rearrange table with such entries on top which has dates. On other hand, can you please suggest how to filter such records which has null in Last Accessed field. Where I am struggling, selection is not possible of null value in any field of table chart and filter is not allowing me to do so.

2.  On Priority 2, Look for applications that are Published and have not been used for a long time.

Checking published date of any app is not enough to make the app go to Quarantine state, Published date data is available since you keep your log data, in my case I keep log data for last 3 months and centralized logging is off too . There are few apps whose Last Accessed Date is null this is possible due to Qlik migration issue or logging is off. Apps whose Last Accessed field value is null could have Last Reload date is lately. Perhaps Full load data extraction apps which usually run once in a while, these kind of apps have no visual to show, so people usually don't open it unless there is any need. So my point is we should take care of Last Reload time too before sending any app to Quarantine Stream.

Suggestion: Duplicate the App Details chart, remove Last Activity Status default filter from App, and add one new in App Details chart as 

=aggr(if( Date(date#([App Last Reload Time],'YYYY-MM-DD hh:mm:ss'),'YYYYMMDD') < Date(addmonths(now(),-12),'YYYYMMDD') and (len([App Last Accessed])=0 or Date(date#([App Last Accessed],'YYYY-MM-DD hh:mm:ss'),'YYYYMMDD') < Date(addmonths(now(),-12),'YYYYMMDD')) ,'Unused','Used'),[App Name])

Filtered apps should consider for quarantine. We can change filter parameters  per Orgs need.

Please correct me if my inputs are not worth.

Thanks,

Rohit