How to identify and clean-up inactive user accounts in Spotfire.

How to identify and clean-up inactive user accounts in Spotfire.

book

Article ID: KB0138516

calendar_today

Updated On:

Products Versions
Spotfire Server 14.7.0 and above

Description

By pulling data directly from the Spotfire Server database (USERS table), we can extract the LAST_LOGIN timestamp associated with each unique username. This allows us to instantly check the exact last time a user account established a session with the Spotfire Server.

Execute the following SQL query against your Spotfire Server database to extract a complete list of Inactive users.

Environment

All

Resolution

 
This query identifies inactive Spotfire users by calculating the exact number of days since they last logged in—either via Analyst or Web Player and lists only those who have been inactive for 90 or more days. 
 
SQL:
-----------------------
SELECT 
    USER_NAME AS [User_Name],
    -- The official column name in the Spotfire schema is LAST_LOGIN
    LAST_LOGIN AS [Last_Login_Time],
    DATEDIFF(day, LAST_LOGIN, GETUTCDATE()) AS [Days_Since_Last_Login],
    -- Status calculation
    CASE 
        WHEN LAST_LOGIN IS NULL THEN 'Never Logged In'
        WHEN DATEDIFF(day, LAST_LOGIN, GETUTCDATE()) >= 90 THEN 'Inactive > 90 days'
        ELSE 'Active'
    END AS [Account_Status]
FROM 
    USERS
WHERE 
    -- Filters for users inactive for 90+ days OR users who have never logged in
    LAST_LOGIN IS NULL 
    OR DATEDIFF(day, LAST_LOGIN, GETUTCDATE()) >= 90
ORDER BY 
    [Last_Login_Time] ASC;
-----------------------

Note that the above query was tested on the MS SQL Server Spotfire database. 

Disclaimer: The content of this article is for informational purposes only. The subject material may change in any new versions with no notice and there is no responsibility by CSG to maintain or support future access to this internal application content. Modification of any internal application content is not recommended and can lead to an unsupported configuration.  It is not intended to be used "As Is" in a Production environment. Always test in a Development environment.
 
To permanently remove users and their records from your Spotfire implementation, delete them. However, if you want to deny users access to Spotfire but keep their records in the system, you can disable their accounts instead. 
 
There are three ways to achieve this:
 
1. Deleting users via the Web-server Admin UI.
2. Using the command line (delete-user).
3. Disabling the Inactive users. 

Note: If you use an external directory service like LDAP, deleted users may be automatically re-created or only "disabled" unless you also update your synchronization settings.

 

Issue/Introduction

Over time, users leave the organization or change roles, but their Spotfire accounts remain active, taking up valuable licenses (Analyst, Business Author, Consumer). This audit helps admins identify who hasn't logged in for a specified period (e.g., 90+ days) so they can disable or delete them.

Additional Information

Doc: delete-user -

Doc: Deleting users from the system -

Doc: Disabling user accounts -

 

Powered by Wolken Software