Running SQL Queries against Active Directory for PRTG Monitoring

7 Jan

For my PRTG Network Monitoring Map (that I have displayed on it’s own screen to alert me of any issues), I wanted to included some data from Active Directory, specifically any locked out accounts and recent incorrect passwords.

I already have all network logins in a SQL database and so I also have a few basic .net pages that display the most recent logins and I then included this in the PRTG Map using the PRTG custom HTML element to include an iFrame (Recent Logins<iframe width=”500″ height=”800″ frameborder=”0″ src=”https://server/site/page.aspx”></iframe>)

Anyway, the first think was to add AD as a linked server, I followed the guide here for that.

ad1

Once I had my linked server I could then run some queries against it, such as:

SELECT * FROM OpenQuery ( 
  ADSI,  
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber 
  FROM  ''LDAP://DOMAIN.com/OU=users,DC=mydomain,DC=com'' 
  WHERE objectClass =  ''User'' 
  ') AS tblADSI
ORDORDER BY displayname

Once I had that working, I could then write my own views so I could call these from my basic .net page to display the specific data I wanted:

CREATE VIEW [dbo].[vw_lockedadaccounts]
AS
SELECT displayName AS ‘User’
FROM OPENQUERY(ADSI,
‘SELECT displayName
FROM  ”LDAP://domain.dom/DC=domain,DC=dom”
WHERE objectClass =  ”User” and lockoutTime>=1 and objectCategory=”Person” and displayName = ”*”
)
AS tblADSI
GO

Its not the easiest thing to query, but I now also list recently changed passwords, recently incorrect passwords and recently created accounts so I can keep an eye on that easily.

I’m pretty happy with my PRTG Map, here’s what it looks like right now (I’ve hidden anything confidential obviously)

prtgmap