Workspaces with their sizes

[jw_CS]

Version: Windchill 12.1

Use Case: List All workspaces with its size (and User / Context)

Description:

I want to have a list (per user) of all workspaces together with their sizes.

Over here there are some topics about it, but i followed this Support Article: https://www.ptc.com/en/support/article/CS412883

The initial part workout. I got now al list of all workspaces with their corresponding user and workspace. But i am unable to figure out how i can add the size per workspace to it. I have tried to add `EPMSupporting data` (`EPMSupporting data.Data`)and that looks more like the blobname or so.

Any ideas how to do this? If not plain "reportable item" workflow a little explenation help I'm not that deep in sql or java

[avillanueva]

I created this Query Builder report with this Table joins:

I modified it to include a count of workspace members but it has the effect of not displaying empty workspaces. I tried fiddling with the outer joins but it still shows only ones with content. Perhaps its the constraint. To match what you see on screen, you have to filter out baseline members that are EPMSepFamilyTable objects. See zip of QML below.

[jw_CS]

I have to check it out how this give me the size / storage space of an workspace. somehow i don't see the link in your solution and what i need...

[avillanueva]

You should see a zip for download of a QML file which you can import to your server. That should add the report. It will not give you storage size as I assumed you wanted object count. Not sure how storage size would help you any. On the server size, the workspaces are just like baselines of objects. Locally, yes, workspaces do have local copies of downloaded and modified sizes. Are you having space issues locally with user's workspaces? Here is information how you can managed that:

https://www.ptc.com/en/support/article/CS37763?source=search

But the local cache size is combined for all workspaces since that is how its managed. All files exist in a single folder whether downloaded (sid) or modified (lid). If you still need to get an estimate of filesize, you would need to add in joins to the baselinemember to EPMDocument then to Application Data which is where you can total up filesize for the objects in the workspace. Still confused how this report would be of any use.

[mmeadows-3]

@avillanueva is correct.

• Disk usage of server-side workspaces isn't very useful.  The workspace contents can only resolve to the existing content files stored in the file vaults/blobs.  It doesn't accurately identify what is in the user's local cache.
  • New objects in a workspace that have not been uploaded are not yet propagated to the database and won't appear in any reports.
  • Enabling Workspace Frames may also impact workspace size,  Not sure how that affects the reported workspace objects.
  • If a user deletes their local cache, the server still shows all the objects in the workspace, but their local cache is empty.
• Disk usage of local workspaces depends on what was downloaded/uploaded and local file size does not necessarily match what the database shows as file size: https://www.ptc.com/en/support/article/CS326831.
  • Differences in actual file size and size on disk. The value stored in the database is the actual size, but disk space is allocated by block so the occupied space on disk may be a bit larger for an individual file.

So, it would be a rare occurrence when a DB query/report of disk usage would match the actual consumed space of a user's local workspace.

What is the purpose of your report?

[jw_CS]

Hi @mmeadows-3 and @avillanueva

I'm only looking at the server side. Not sure how it is where you live but over here free storage doesn't exist!

I want just check what is the amount of storage is consumed, by who (and where). So i need to know what the storage sizes of workspaces are, what objects are inside i really don't care...

[avillanueva]

True, space is not free but workspace are meant to be temporary. The only case I can see on the server side of concern would be if users were uploading massive amounts of CAD data but not checking it in. This would be a misuse of workspaces as much of the data remains local to their client systems. When they check data in, it moves to commonspace so to speak and would be vaulted to the appropriate product or library. I have a query below that can help identify how much files are stored in each context. If you feel in your system workspaces are a source of lots of file storage, I suggest retraining users to check in and clear their workspaces more often.

SELECT pdm.nameContainerInfo, fv.name vault_name ,count(fv.ida2a2) file_count, ROUND((SUM(fileSize)/1048576),2) TOTAL_FILESIZE_IN_MB
FROM
(SELECT b.idA3A5 a5, b.idA2A2 id, b.filename fileName, b.filesize fileSize FROM ApplicationData b,HolderToContent c WHERE b.idA2A2 = c.idA3B5) ad,
HolderToContent htc,
FvItem fi,
FvMount fvm,
FvFolder ff,
FvVault fv,
(SELECT idA2A2, idA3masterReference From EPMDocument UNION ALL
SELECT idA2A2, idA3masterReference From WTDocument UNION ALL
SELECT idA2A2, idA3masterReference From WTPart) doc,
(SELECT idA2A2,nameContainerInfo FROM ExchangeContainer UNION ALL
SELECT idA2A2, nameContainerInfo FROM OrgContainer UNION ALL
SELECT idA2A2, nameContainerInfo FROM WTLibrary UNION ALL
SELECT idA2A2, nameContainerInfo FROM PDMLinkProduct UNION ALL
SELECT idA2A2, nameContainerInfo FROM Project2) pdm,
(SELECT idA2A2, idA3containerReference From EPMDocumentMaster UNION ALL
SELECT idA2A2, idA3containerReference From WTDocumentMaster UNION ALL
SELECT idA2A2, idA3containerReference From WTPartMaster) m
WHERE
fi.ida3a4 = ff.idA2A2 AND 
fvm.idA3A5= ff.idA2A2 AND
ff.idA3A5 = fv.idA2A2 AND 
ad.a5 = fi.idA2A2 AND
ad.id = htc.idA3B5 AND 
htc.idA3A5 = doc.idA2A2 AND
m.idA2A2 = doc.idA3masterReference AND 
pdm.idA2A2 = m.idA3ContainerReference
group by pdm.nameContainerInfo, fv.name

Note that this query runs under Oracle. It does not include DerivedImage data which can be another source of files and content.

[anursingh]

Hi @jw_CS,

I wanted to see if you got the help you needed.

If so, please mark the appropriate reply as the Accepted Solution. It will help other members who may have the same question.
Please note that industry experts also review the replies and may eventually accept one of them as solution on your behalf.
Of course, if you have more to share on your issue, please pursue the conversation. 

Thanks,
Anurag 

[LewisLawrence]

Attached a couple of QML files, one that returns all the workspaces and sums the filesizes in them, so one row per workspace. The other that just lists all the EPMDocuments and their filesizes, one row per Document per workspace.