is there any way to find list of all windchill users that are registered (only live)

[sandy007]

Version: Windchill 12.0

Use Case: find list of registered user in windchill system

Description:

hello all,

i tried to find windchill registered users list from SQL but it has given me over 5000+ entries which is not correct.

looking into our security group we have only 832 users in the system. is there any way to find the list of users in the windchill system.

thanks,

sandeep 

[Marco_Tosin]

You can use this query builder report

Report to list all active users and the time when they created

If you want to extract an SQL query from above report, you can read also this article

How to get the SQL statement of the Query Builder Report

[sandy007]

I tried this Report to list all active users and the time when they created but it is again throwing 2000+ entries which seems way more then what actually we have.

[Marco_Tosin]

Do you use ProjectLink by any chance?

I used the same report and noticed that it multiplies the users because it finds more values in the Full Name column

In my case if I filter for users that do NOT have the “@” character in the name (i.e. when the user matches the email address used by ProjectLink) the number decreases.

Are there other similar conditions that increase the number of users?

Using the filter described above, the number of my users matches those who have access to Windchill.

[avillanueva]

Just clarifying. @Marco_Tosin has some good suggestions about ProjectLink. Is your practice when a employee leaves the company to leave the account in the system and move them to a disabled users group? Temporarily Deactivating a User This would show that users never leave but in practice they are denied access. The active user report should give you a true count of all accounts that could potentially login. Not all may be assigned licenses. If you are not deleting the user account when that user has left or no longer needs access to Windchill, you might have to fold in some other reports and groups to arrive at your true count.

One report you can look into is the "List of users and their last login" which can show you accounts that may exist but that user has never logged in or has not in a very long time. Next I would look at license assignments and sum those up. If the user does not have a license, they might be able to log in but cannot do anything. I suspect that you might have to do some cleanup. 

[sandy007]

Hi @Marco_Tosin , we don't have project link installed.

@avillanueva  we have a custom report that helps us to pull disconnected users that we run every month. we delete those users from windchill by "delete from windchill" action. 

so when we delete the users from system, why would the report still show them? the users which were deleted previously, i tried to find them in participant table but nothing is showing up so the report that is showing up 2000+ entries, not all have the licenses assigned? they can just login as they are created in LDAP, am i correct? because i found one user who's license profile table and group table is empty.

Br,

Sandeep

[Marco_Tosin]

The reports cited by @avillanueva  are these

How to display user's last login time in a query builder report

https://community.ptc.com/t5/Windchill/DOES-ANYONE-KNOWS-QUERY-TO-CHECK-LAST-LOGIN-OF-A-USER-INACTIVE/td-p/399956

@sandy007 , do you use AD to connect users to Windchill?

We have another user management system that ensures that only those in AD who are part of a certain organization and a specific group can access the system.

This way we have more control over the users, and when a user no longer needs to log in, all we have to do is remove him from the group and the organization.

In this way users who are not on the two lists are not even presented with the login window to Windchill

[mmeadows-3]

I haven't looked at that report, but I'm assuming it is returning accounts with 'active' status and not 'disabled', 'disconnected', 'deleted', 'pending' (ProjectLink), or 'replicated' (packages).

A disconnected participant is one where the LDAP DN of the user does not match what Windchill thinks it is.  It is common for a user to be moved to a "terminated" DN and not actually be deleted from the LDAP.  If you do not have a defined LDAP filter to exclude the terminated DN, then when you delete them from Windchill and run a wildcard participant search again, the user comes back.

Group memberships are removed when a user is deleted.  The user that comes back is a new user id that has never been assigned licensing, context team roles, etc.

Considering the JNDI configuration, it sounds like Windchill is connected to a corporate LDAP.

If you use a filter in the LDAP JNDI Adapter(s) (adapterservice.json), you can limit who can access Windchill to known valid users.

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

When a filter is not used, we see everything in the company: users, computers, conference rooms, etc.

At a minimum, set the filter to identify the object classes you want Windchill to see: users and/or groups.  That should make everything that is not a valid user a disconnected participant.  Computers and conference rooms can't log in, so you can simply delete them.

To exclude terminated users: https://www.ptc.com/en/support/article/CS37358

Do you really want to grant everyone in the company the ability to log in?  If not, I would choose a deeper search base that still includes all your valid LDAP users and groups.
The tightest control is to define a 'Windchill Users' security group in AD and assign all the Windchill users as members of the group.  Each user assigned to the group gets the memberOf attribute pointing to the DN of the group.  The Windchill filter uses the memberOf attribute to identify valid users.

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

This is an administrative headache on the AD side, but it ensures no one is allowed into Windchill unless they are supposed to be in Windchill.

[avillanueva]

Deleting users does not remove them from the WTUser table. It just updates them as a disabled/deleted user. They should not show up on the active user list. It makes sense since fields like created by and modified by still need to show that some user existed at that time. If you are doing direct SQL queries to this table, you need to account for this.  

[sandy007]

Hello All,

Thanks for your help.

this is what i am getting using report How to display user's last login time in a query builder report

again it is showing 2000+ entries.

yes, we have AD linked to LDAP. when a user is added in a specific group, they get WC server access to login. can we add criteria based on grp name in the query builder so that all the users can be fetched from that particular security group?

Br,

Sandeep

[Marco_Tosin]

Obviously there are more than 2000 rows.

If you also enter the file name, which is different even if the user is the same, the rows multiply.

The above applies if the image you attached is correct

[mmeadows-3]

That image is a last modified report for CAD Documents.  That report should return as many rows as you have in EPMDocumentMaster, can be millions.

Last login reports show usernames.

If a user is not in the Last Login report, they either never logged in or haven't logged in since the last audit purge.

If purged audit record were saved to file (WTDocument), you can take those files into something like Splunk to evaluate older history.

[sandy007]

Hi,

I am still clueless on this topic. nobody has template to fetch the users ?

sandeep

[Marco_Tosin]

Have you read the comment above of @mmeadows-3 ?

It's impossible that the image you have attached is taken from template of article 

How to display user's last login time in a query builder report

Because no CAD table was selected in that template.

[sandy007]

Hi @Marco_Tosin 

i am just pressing Generate button with all attributes ON.

i am getting this result, how i supposed to filter user list? please guide

[Marco_Tosin]

It is impossible that you are using the report we told you to use because the one in the PTC support article has only TWO attributes (see item 3 in the attached image).

Yours has SIX attributes so you can't have just hit the button generate.

[sandy007]

Right. it should have 2 options. i used below QML link and downloaded the file by cntrl+S

downloaded file is like below 

when i imported the same in report management utility, it shows me 6 attributes.

am i doing anything wrong?

[avillanueva]

The report linked in that article appears wrong. We should alert PTC. That report you are using is meaningless. Should be obvious that is just lists document set states. The one you want is actually "here"

Further up on the article. Try that one.

[mmeadows-3]

Looks like PTC has attached the wrong QML.  I pinged the authors.

Try these articles:

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

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

[sandy007]

Thanks for providing the correct link. now it shows 297 entries.

it is fetching all the users in the system? or only those who has logged in the specified period?

[mmeadows-3]

The last login results will be limited by the the date range and the last stored login in the audit tables.  To see everything, I will give it a long date range like the last 10k days or 1990-2025.  If the audit tables have been purged (e.g. everything before 2024), then there won't be any last logins older than the last purge timeframe.  I don't believe any of the PTC provided queries include users who have never logged in.

[sandy007]

from license utility, i fetched the report from last week which shows as below 

the registered users shows as 17286. which i guess fetching the data from corporate AD. is there any way to correct it and point it to the correct group so that it show the right value?

[mmeadows-3 · Accepted solution]

I'm guessing you are right.  The "Registered Users" is every user entry in the database.  These are created when someone performs a wildcard participant search in Windchill and the search base is set to the root of the DN structure, or when there isn't a defined search filter in the JNDI Adapter.  These responses are specific to Active Directory.  Attributes like memberOf will change if using OpenDJ or another LDAP.

Always do cleanup in a non-production environment first.

I would clean up the 354 Disconnected Users first.  Clear and delete their workspaces, undo check outs, reassign open tasks, and finally delete the disconnected participants.  Once cleanup is complete, define the following JNDI Adapter mappings, purge participant cache, and restart Windchill.  If I am right, you should get well over 10k disconnected participants.  It is safe to delete them if they have never touched the system.  Otherwise, go through the cleanup process on them too.  User reporting to find disconnected participants who own workspaces, checked out objects, and open task.

The correct answer for your environment depends on how your LDAP is structured and where all potential Windchill users reside in the LDAP.  There are four properties that can help reduce the number of wtUser entries in the database.

1. {adapter name}.searchBase=dc=company,dc=com

Starting point for finding user accounts for this JNDI Adapter.

There can be more than one JNDI Adapter, especially for organization/site mapping and when Windchill users belong to a few unique search bases.

Search Bases must be unique across all JNDI Adapters and cannot be nested.

2. {adapter name}.searchScope=SUBTREE

Determines if the search for user accounts is recursive (SUBTREE) or flat (ONELEVEL).

3. {adapter name}.windchill.mapping.user.objectClass=user

Excludes conference rooms, groups, and other non-user entries.

4. {adapter name}.windchill.mapping.user.filter=(memberOf={DN of 'filter' group})

This group includes all Windchill users.

Users who are members of this group must have the memberOf attribute pointing to the DN of this group.

Windchill searches for users with the memberOf attribute with a value matching the DN of this group.

The LDAP Administrators (IT) must add Windchill users to this security group before Windchill can see them.

Note: each JNDI Adapter also maps users to organizations.  If you have more than one organization, use these properties to map JNDI Adapter members to a specific Windchill organization.  The user filter above does not apply to organization mapping.  Any user under the search base will be mapped according to these LDAP mapping attributes.

{adapter name}.windchill.mapping.user.o=organization

This maps the organization name to the organization attribute value in the corporate LDAP.

The attribute value can be different for every user.

A blank value in the LDAP attribute will map the user to the Site.

{adapter name}.windchill.mapping.usersOrganizationName={organization name}

This sets the organization name for all members of the JNDI Adapter to a single value.

A blank value will map all users found under the JNDI Adapter to the Site.

Excluding these organization mapping attributes will default JNDI Adapter participants to the Site context.

Note: Some companies also use the user filter on Apache to prevent logins.  Others just don't assign licensing and the users can't get in anyway.

To summarize, the cleanest solution is to use a 'filter' security group and have IT add only the Windchill users to that group.

That approach may not be practical with thousands of active users and a constantly changing list of Windchill users.

So consider all these mapping attributes to find the correct integration for your environment.

[BryanK]

Would it not be more useful to analyse the apache log files? 

I use goaccess

https://goaccess.io/get-started

you can look at access over time or if you just use the log files for the day you can see a daily result. 

It gives quite a lot of interesting information but the for the purposes of this discussion it may give you what you are looking for. 

See screen grabs below. 

Let me know if you need more info and I'm happy to help you out. 

Regards,