We are upgrading from 10.6 to 15.03 and in our test environment we are trying to reproduce a report that shows us our staff users.
In 10.6 all users were in the USERS table, so the report was simply a list of active users in that table. In 15 users are now in the USERS table and in the USERMAIN table (and evidently in other tables as well), but we do not see how to get a count of casual vs full users. Also the USERMAIN contains users who are no longer iMIS users but do log into the web. Has anyone figured this out?
I am very surprised that ASI does not include an easy way to report your staff users as part of the package especially since the new licensing is based on named users. Their response was "Have your service provider design a report" and my point is that this is such a basic piece of information needed to track your licenses that ASI should include it in their product.
- Jean B
Listing users
At the bottom of the System Setup screen, you can find used and available counts for Full and Casual Named Licensed users. e.g.
Full Named Licensed Users 53 of 100
Casual Named Licensed Users 5 of 50
In IQA, this standard query: $/Common/Queries/SecurityAdministration/UserAdministration/Users
... shows Public, Casual, and Full status for the selected users. You can make a copy and add a filter to exclude the public users.
Finally, here's a revised version of the SQL query behind the counts on the System Setup screen.
select ID, FULL_NAME from Name na
INNER JOIN UserMain um on na.ID = um.ContactMaster
INNER JOIN Users u ON um.UserId = u.UserId
WHERE
u.IsCasualUser = 0
AND um.UserId NOT IN ('MANAGER', 'ADMINISTRATOR') AND ISNULL(um.ProviderKey, '') <> ''
AND um.IsDisabled = 0 AND ISNULL(um.EffectiveDate, CAST('19000101' AS DATETIME)) <= GETDATE()
AND ISNULL(um.ExpirationDate, CAST('30000101' AS DATETIME)) > GETDATE()
Change u.IsCasualUser = 0 to 1 to list casual users.
For 15.1, add 'GUEST' to the excluded names list.