Tables
Collector.ADGroupMember
Stores the collections of Active Directory group membership data.
| Column | Type | Description |
| ID | bigint | Primary key row identifier. |
| ExecutionDateTime | datetime | The execution date and time, common to the run of a collection for a single instance. |
| GroupName | nvarchar | The name of the AD group. |
| ObjectName | nvarchar | The name of the AD group (same as GroupName), or of a member of group GroupName. Or, a special notifier for Valid values: Group or member name Minion: Not found in AD Minion: Empty Group |
| IsGroup | bit | Indicates whether the record is a group, or not. |
| GroupMember | varchar | Not currently in use. |
| LastLogon | datetime | The date and time at which the account last logged in (according to Active Directory). |
| BadLogonCount | bigint | A count of the number of bad login attempts for this account (that is, attempts with incorrect credentials). |
| PasswordNeverExpires | bit | Whether the password expires for this account. |
| PasswordNotRequired | bit | Whether the password is required for this account. |
| PermittedLogonTimes | varchar | Times when the account can logon. |
| PermittedWorkstations | varchar | The list of workstations that the account can log in to. |
| LastPasswordSet | datetime | The last date and time that the password was set for this account. |
| LastBadPasswordAttempt | nchar | The last date and time that a logon attempt was made using a bad password (for this account). |
| UserCannotChangePassword | nchar | Whether the user can change the password for this account. |
| Description | nvarchar | Description of the account. (NOTE: Like almost everything else in this table, Description is collected from Active Directory, not assigned within Minion Enterprise.) |
| DelegationPermitted | bit | Whether the account may be delegated. |
| AccountExpirationDate | datetime | The date and time the account will expire. |
| AccountLockoutTime | datetime | The date and time the account was locked out. |
| EmailAddress | nvarchar | The email address for this account. |
| Enabled | bit | Whether this account is enabled for authentication. |
| EmployeeID | nvarchar | Employee ID for the account user. |
| VoiceTelephoneNumber | varchar | Phone number for the account user. |
| DistinguishedName | nvarchar | The distinguished name for the account user. |
| DisplayName | nvarchar | The display name for the account user. Example: ‘Sean McCown’. |
| SurName | nvarchar | The surname (last name) for the account user. |
| MiddleName | nvarchar | The middle name for the account user. |
| GivenName | nvarchar | The given name (first name) for the account user. |
| Name | nvarchar | The name for the account user. Example: ‘Sean McCown’. |
| GUID | varchar | The GUID associated with the the account. |
| SID | varchar | The Security ID associated with the the account. |
| SmartcardLogonRequired | nchar | Whether a smartcard is required to log on to this account. |
| HomeDirectory | nvarchar | The home directory for this account. |
| HomeDrive | nvarchar | The home drive for this account. |
| AllowReversiblePasswordEncryption | Bit | Whether reversible password encryption is enabled for this account. |
Special notes on the ObjectName column:
- Minion: Not found in AD – If a Windows login exists in SQL Server, but doesn’t exist in Active Directory, it means that the entry has been removed from AD. Minion Enterprise specifically calls out so that DBAs can easily see when a login is no longer valid.
- Minion: Empty Group – If a Windows group exists, but has no members, ME will show the group name as “Minion: Empty Group”.
Stored Procedures
Collector.ADGroupListGet
Returns a list of Active Directory groups from the Logins module table Collector.Logins. The Logins module data gives Minion Enterprise a starting place to query Active Directory.
Warning: This procedure does not return a comprehensive list of all AD groups. The ADGroupsGet.exe script handles the recursive calls to make sure that nested AD groups are processed.
Important: This procedure is meant to be used by automated collectors; we recommend against using it manually. Therefore, the parameter set is only documented internally.
Collector.ADGroupMemberInsert
The script calls this procedure to perform the insert into the Collector.ADGroupMember table.
Important: This procedure is meant to be used by automated collectors; we recommend against using it manually. Therefore, the parameter set is only documented internally.
Report procedures
If you have Windows groups as logins in SQL Server, you traditionally have no idea who's in there, because you can't see into Active Directory groups.
Now you can, and quite easily, with the following stored procedures:
Report.ADAcctsInSQLAll
Gets an expanded list of Active Directory groups for all servers.
For more information, see the related article.
Report.ADAcctsInSQLByApp
Gets an expanded list of Active Directory groups for a specific app. Parameter: @AppID (int).
Report.ADAcctsInSQLByEnviro
Gets an expanded list of Active Directory groups for a specific environment. Parameter: @Enviro (varchar).
Report.ADAcctsInSQLByID
Gets an expanded list of Active Directory groups for a specific instance. Parameter: @ID (bigint).
Report.ADAcctsInSQLByServerName
Gets an expanded list of Active Directory groups for a specific server name. Parameter: @ServerName (sysname).
Report.ADAcctsInSQLBySLA
Gets an expanded list of Active Directory groups for a specific service level. Parameter: @ServiceLevel (varchar).
Views
Collector.ADGroupMemberCurrent
Provides the most recent collection of Active Directory group membership data.
Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
- ServerName
- ServiceLevel
- Version
- Edition
- Descr
Collector.ADGroupMemberPrevious
Provides the next-to-most recent collection of Active Directory group membership data.
Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:
- ServerName
- ServiceLevel
- Version
- Edition
- Descr
Jobs
CollectorADGroupsGet
Calls the executable ADGroupsGet.exe for all servers, to retrieve Active Directory group membership information.
Executables
ADGroupsGet.exe
Perform the AD group membership data collection for managed servers. Log the results to Collector.ADGroupMember.
Input parameters: MoreData.
The MoreData parameter specifies a number for the type of extra data you want to get back.
Normally, the collector only gets group members for groups that have users in SQL, but when you use this parameter you can get other users as well.
Below is the list of parameter values and the data they'll retrieve.
1 - Gets all users from the 'Domain Users' group in AD. This will help with getting data for those windows users that have access directly into SQL.
Example execution:
Get default data
C:\MinionByMidnightDBA\Collector\ADGroupsGet.exe
Get default data and domain users
C:\MinionByMidnightDBA\Collector\ADGroupsGet.exe 1
https://minionware.desk.com/customer/portal/articles/2572234-ad-group-members-module