User Inventory: Retrieving All Users through SQL Queries

Objective

This article provides SQL Query by which administrators can get a list of different types of users in FogBugz and also their counts. This will be very useful for the administrators in managing the accounts of different types of users in FogBugz. 

Type of Users

In FogBugz, we have the following user types - 

User Type	Description
Normal	Normal FogBugz users have access to Cases, Wikis, Discussion groups, and Reports as determined by permissions.
Administrator	Administrators have site-wide administration capabilities and FogBugz also lets administrators designate any user as an administrator over a project. There must be at least one Administrator user on a FogBugz account at any given time. Any user can be promoted by one of the existing administrators.
Virtual	A virtual user cannot log in and does not consume a license. It is a free user type, and Its purpose is to allow assigning of cases to a group of people instead of an individual.
Community	Community users are free user accounts that are created to partially (limited access)open up FogBugz externally.
Anonymous	Anonymous user is also free and does not consume a license. These users are created when the public is allowed to open wikis or create cases.

For more details on different types of users in FogBugz and their access, check this article on 'User Types in FogBugz'.

Data to Retrieve

The list of Queries can be used to retrieve - 

1. Current List of all users.
2. Count of Active users.
3. Count of Inactive users.

SQL Query Script

Script to retrieve the list of all users

/****** Script to retrieve list of users and active/inactive user count  ******/
use [trial1] -- Change as needed

SELECT [ixPerson]
      ,[sFullName]
      ,[sEmail]
      ,[fAdministrator]
      ,[fDeleted]
      ,[fCommunity]
      ,[fConfirmed]
      ,[fVirtual]
      ,[fFogBugzUser]
      ,[fKilnUser]
      ,[nType]
  FROM [Person]

Script to retrieve the list of active users only

/****** Script to retrieve list of users and active/inactive user count  ******/
use [trial1] -- Change as needed

SELECT p.[ixPerson]
      ,p.[sFullName]
      ,p.[sEmail]
      ,p.[fAdministrator]
      ,p.[fDeleted]
      ,p.[fCommunity]
      ,p.[fConfirmed]
      ,p.[fVirtual]
      ,p.[fFogBugzUser]
      ,p.[fKilnUser]
      ,p.[nType]
      ,p.dtLastActivity
      ,p.dtRegistered
FROM [Person] p
where p.fdeleted = 0

Understanding the type of user (nType)

In the above query nType = 0 means "administrator" users, and nType = 1 means "normal" users. All other user types (virtual and community) are ignored towards the license count.

Script to retrieve the count of active/inactive user

/****** Script to retrieve active/inactive user count  ******/
use [trial1] -- Change as needed

  select count(ixPerson) as Active
  FROM [Person]
  WHERE fDeleted = 0 and fConfirmed = 1
  AND nType in (0,1)

  select count(ixPerson) as Inactive
  FROM [Person]
  WHERE fConfirmed = 1 and (fDeleted = 1 or fFogBugzUser = 0)
  AND nType in (0,1)

FogBugz on-Premises: free administrator

For FogBugz On-Premises, the main Administrator does not count toward the no of licenses, meaning that the final number of the required licenses is equal to the "Active" users count minus 1 (the free Administrator).

User Inventory via API

This information can also be retrieved through API. For the Step by Step process, follow the article User Inventories: Retrieving a Complete List of Active Users and learn how to achieve this via API call (resultant data will be in JSON format).

 

Related Articles

User Account Management in FogBugz 

User Inventories: Retrieving a Complete List of Active Users