PRTG Active Users and Computers

1 Feb

PRTG Active Users and Computers

Only because I always have a fairly rough idea of how many users and computers there are on the network but I figured it would be nice to have an exact answer to that question at any time.

I already have SQL talking to AD via an LDAP query and so I created a few views in SQL for getting this information.

Active Users

SELECT *
FROM
OPENQUERY(ADSI, 'select Name FROM ''LDAP://server.domain/OU=Company,DC=this,DC=that'' 
  WHERE objectClass =  ''User'' AND ''userAccountControl:1.2.840.113556.1.4.803:''<>2')

Disabled Users

SELECT *
FROM
OPENQUERY(ADSI, 'select Name FROM ''LDAP://server.domain/OU=Company,DC=this,DC=that''  
  WHERE objectClass =  ''User'' AND ''userAccountControl:1.2.840.113556.1.4.803:''=2')

Active Computers

SELECT *
FROM
OPENQUERY(ADSI, 'select Name FROM ''LDAP://server.domain/OU=Company,DC=this,DC=that'' 
  WHERE objectClass =  ''Computer'' AND ''userAccountControl:1.2.840.113556.1.4.803:''<>2')

Disabled Computers

SELECT *
FROM
OPENQUERY(ADSI, 'select Name FROM ''LDAP://server.domain/OU=Company,DC=this,DC=that''  
  WHERE objectClass =  ''Computer'' AND ''userAccountControl:1.2.840.113556.1.4.803:''=2')

I then created 4 SQL files to select all the rows for each view and placed them in: C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql

I added 4 SQL sensors, selected my .sql files for each and set the data processing to count table rows.

I could also put limits in place for these sensors if I wanted of course.