After a few drinks with some friends last week we got talking about how it would be nice to monitor some Active Directory groups and find out if any changes have been made, I surmised that it would be pretty easy with PowerShell using either the Quest tools or the new Posh 3 AD cmdlets and compare-object to know when changes had been made. Before I started playing with PowerShell ISE I did my normal Google search and most impressively Francois-Xavier Cat (The LazyWinAdmin – http://www.lazywinadmin.com/) has wrote just that and with fancy reports and history far greater than I would ever do. The post is here :

http://www.lazywinadmin.com/2013/10/powershell-monitor-and-report-active.html

So still with my drunken hat on, I thought why do these things in PowerShell when we have the always versatile SQL Server. As I’ve posted before XP_LOGININFO (https://sqlnotesfromtheunderground.wordpress.com/2013/12/10/troubleshoot-user-logins/) can return information about AD Group members, and using the EXCEPT command, we can compare results.

Anyway, its not finished and would need adding some code to send an email with the results, but if anyone is interested in doing this sort of thing within SQL it certainly seems possible

CREATE TABLE #Usergroup ( names VARCHAR(100) )
INSERT  INTO #Usergroup
( names )
VALUES  ( 'BADSEEDS\Stephen' )
INSERT  INTO #Usergroup
( names )
VALUES  ( 'BADSEEDS\Timmy' )
CREATE TABLE #currentGroup
(
accountName VARCHAR(100) ,
type VARCHAR(100) ,
priviege VARCHAR(100) ,
mappedLoginName VARCHAR(100) ,
permission VARCHAR(100)
)
INSERT  INTO #currentGroup
EXEC xp_logininfo 'badseeds\SysAdminsGroup', 'members'
DECLARE @changes INT

SELECT  @changes = COUNT(*)
FROM    ( SELECT    accountName
FROM      #currentGroup
EXCEPT
SELECT    names
FROM      #Usergroup
) a
IF ( @changes > 0 )
PRINT 'we got issues email statement here! '
ELSE
PRINT 'we are all good'
DROP TABLE #currentGroup
DROP TABLE #Usergroup
Advertisements