Number count of prt, asm, & drw files in Windchill 11.2.1.0

[jschutte]

Is there a fast, easy way to get the exact number of only the latest revisions of every prt, asm, & drw files in Windchill 11.2.1.0?   This is actually Intralink 11.2.1.0 which we are planning to upgrade soon.

Thanks,

Joe S.  

[avillanueva]

Easiest is write a SQL query and look at the EMPDOCUMENT table. There is a column which indicates if it is the latest iteration of a particular version. Do you have access to run queries against DB?

[jschutte]

I have full access to the server.  However, I have no clue how to write or run a SQL query.    I actually suspected this would be the recommendation.  I was hoping that there was a tool somewhere in Windchill I could use.  

Any suggestions on how to write & run this query?

[Marco_Tosin]

You can also use query builder to get number of objects in Windchill.

This is Knowledge HUB article of Query Builder Reports from support.

This in an example to fetch number of Objects in each Product (taken from above article)

https://www.ptc.com/en/support/article/CS149816

[jschutte]

Thank you both for your replies.   It does not look like I will be climbing this learning curve & getting the numbers by myself.

As I said, I was afraid this w0uld be the solution.   We only have 5 seats of Creo & Windchill requires little to no administration so there's never been a need for me to delve deeper into Windchill.  I already have another full time job.   

I need these numbers very quickly, so I will have to find someone who already knows how to write & execute this SQL query.  

Thanks.  

[RandyJones · Accepted solution]

Since you only want know the number of latest Revisions you can simply query the epmdocumentmaster table:

select count(*) Num_Prts from epmdocumentmaster where cadname like '%.prt';
select count(*) Num_Asms from epmdocumentmaster where cadname like '%.asm';
select count(*) Num_Drws from epmdocumentmaster where cadname like '%.drw';

[jschutte]

Thanks Randy.   I attempted to run just that first line for the .prt & this is the error I got:

The term '*' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

[RandyJones]

These are actual sql queries that you run "in the database" not from a terminal or command prompt. If you have oracle you can login to the database using sqlplus (on the database host) or download sqldeveloper.

[jschutte]

Okay... thanks!   I will have to get a guy from our IT department to do this.  He runs sql queries "10 times a day" but not in Windchill.   This is not Oracle anymore, used to be a couple versions ago.  

[MikeLockwood]

If you only have 5 CAD seats, you may have an amount of data that can be determined using a simple Windchill search.

Search filter for latest Iteration, and the preference can be set for latest Version (Revision) only.

It will stop at 2000 by default, but you can hit Next, Next until the search completes, then simply see how many.

Can filter the search each time, one each for for Document Category = Part, Assembly, Drawing.

[jschutte]

Thanks Mike,

Yes, only 5 seats.  But there are files from back before we deployed Intralink 1.0.  Our first seat was purchased in 1996. 

That search idea is great & I had attempted that already I just can't seem to locate how to set for the latest Version (Revision).    I'm getting all of revs.  The only Criteria I can find is Name, Number, Last Modified, & Created On.  

[MikeLockwood]

Here is an article about latest version search preference.

https://www.ptc.com/en/support/article/cs157314?language=en&posno=7&q=windchill%20preference%20latest%20version&source=search

Quick Links / My Settings / Preferences / Search / Latest Version

Will work for you, unless for some reason, it is set to All versions and also locked at Site or Org level (unexpected).

[jschutte]

Wow, okay, thanks Mike.  I never saw those options before.  It works, Revisions are only the latest, but I am still getting versions, like c1, c2, c3, c4.  This may have to be good enough at this point.  

[jschutte]

RandyJones...!   Yes, your script worked.  I got the numbers, all of them.   Once I got a hold of the correct person on our IT department, he logged on to our server & ran the queries for us. 

Thanks!

[RandyJones]

Awesome!

[jschutte]

Thanks everyone who replied.   Problem solved.   This reminded me of our old user group!

-joe