Use SQL to get Intralink stats direct

I had prepared a detailed article about using SQL to remove unwanted servers from Intralink - but I've decided not to publish it since the commands could cause severe database problems if used incorrectly.  For instance, you could inadvertently remove your primary fileserver without warning (I'm surprised there's no safeguard against this, like there is for attempting to remove used clusters).

Anyway - here's a less dangerous way to use SQL - get database statistics from Intralink at the command line...  There was a question about it on PTC/USER, and I've recently formulated some scripts for server stats, so here goes...

Login as normal to SQL (see previous post for more) - then try these lines:

select count(*) from PDM.PDM_PRODUCTITEM;
select count(*) from PDM.PDM_PRODUCTITEMVERSION;
select count(*) from PDM.PDM_USER;

They'll return totals for your Product Items (PIs), Product Item Versions (PIVs) and Users.  You can count folders and storage clusters like this:

select count(*) from PDM.PDM_FOLDER;
select count(*) from PDM.PDM_POOL;

So far, they're all simple totals, here's a query to list all your users, complete with date created, created by and notification of Admin rights.

select USERNAME||' | '||CREATEDBY||' | '||CREATEDON||' | '||
decode (USERTYPE, 0, '', 1, 'Admin')
from PDM.PDM_USER
order by USERNAME;

Some time back, I came across some SQL reports written by someone more familiar with the Intralink-SQL structure, and picked up a lot of tips from the way queries can be written.  These led me to create a short report that I've scheduled weekly against 13 dataservers, giving the latest stats and information, including lists of items, folders, users, attributes.  These are presented in a web format and it's usually quicker to search the web page than go into the particular database to look for items, etc...

The plan was to incorporate these in a future version of CADminTools (under Intralink 3.x tools) - but that's all on hold for the time being...

Hopefully this has given you a flavour of what's possible?  I certainly find the command line approach better for some types of information gathering.