topic Re: How to delete sheets(IDs available in external sheet) via repository API in Integration, Extension & APIs

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

rohitk1609 — Sat, 16 Nov 2024 18:47:53 GMT

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

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

sogloqlik — Thu, 23 Apr 2020 10:18:39 GMT

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

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

Levi_Turner — Mon, 27 Apr 2020 15:15:06 GMT

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

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

rohitk1609 — Mon, 27 Apr 2020 19:33:44 GMT

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

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

Levi_Turner — Mon, 27 Apr 2020 19:51:23 GMT

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.

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

rohitk1609 — Mon, 27 Apr 2020 20:23:57 GMT

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

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

Levi_Turner — Tue, 28 Apr 2020 00:11:58 GMT

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.

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

rohitk1609 — Tue, 28 Apr 2020 16:06:00 GMT

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

}

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

Levi_Turner — Tue, 28 Apr 2020 12:34:18 GMT

> 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.

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

rohitk1609 — Sun, 03 May 2020 14:03:18 GMT

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

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

rohitk1609 — Mon, 04 May 2020 20:32:28 GMT

Hi Levi,

I am about to acheieve my goal but stuck at last point where #npm install is failing.

I don't have internet at my Qlik server where I install node.js

This is the error I am getting:

Please guide me how to fix it ?

Thanks,

Rohit

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

Levi_Turner — Tue, 05 May 2020 00:22:20 GMT

> Please guide me how to fix it ?

This is a Node question, so there may be guides online for how to install node modules without internet connectivity. What I'd do? I'd use some machine with internet access (e.g. your laptop) to complete the npm install step then move the entire directory with the downloaded modules to the server.

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

Levi_Turner — Tue, 05 May 2020 00:25:49 GMT

> 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.

Yes, you cannot select null values. @Tyler_Waterfall : This would be an improvement for the Operations Monitor.

> 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.

Yes, the guide is intended to be a starter pack for guidance. It is expected to be adjusted to fit the organizational requirements. If your environment has apps where it isn't uncommon for some apps to be unused for months to quarters at a time, then don't remove them. This is relatively unusual but by no means unknown.

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

rohitk1609 — Tue, 05 May 2020 18:30:22 GMT

Hi Levi,

I have successfully tested your solution https://adminplaybook.qlik-poc.com/docs/asset_management/apps/flag_unused_base_community_sheets.html

and I pretty sure I would do https://adminplaybook.qlik-poc.com/docs/asset_management/apps/remove_unused_private_sheets.html

Thanks for this important admin doc. I am sure this solution will be a milestone in admin work.

Thanks,

Rohit