Mike's Realm

Automated User Creation & Device Group Security for WhatsUp Gold

This is the beginning of a little standalone identity management project me and Kevin are working on for IPSwitch’s WhatsUp Gold product.

Overview:

This “WhatsUp Enterprise New User System” aka WENUS enumerates a configured LDAP Group (and any nested groups) for user accounts–any users that do not exist in the WhatsUp are created automatically. Â It also reads the user account’s department attribute and creates access to the device group that has a matching device group description. Â In our case we have ~200 remote sites, at each of those sites there are up to 2 techs who use WhatsUp to monitor systems at each site. Â These remote sites are broken out into Device Groups, we populated all the device group description fields with the corresponding site/dept code–which is also populated in the user account’s LDAPÂ department attribute. Â This script can be run nightly or weekly to ensure the proper folks get access to only their respective area. Â Furthermore it outputs to the WhatsUp System Activity Log, so you can see new users being created, and any errors there.

This was written for WhatsUp Gold v15 but should work on older versions–perhaps requiring slight modifications.

WENUS Features:

Enumerates a LDAP Group (and any nested groups) to create WhatsUp User Accounts when additions are made to the group
Scopes WhatsUp Device Group Security based on Department LDAP attribute & Matching WhatsUp Device Group Description fields
Outputs to the WhatsUp System Activity Log – any Creates and any Errors are logged centrally.

SQL Server credentials are required to be put into this script, to prevent this from being accessible via clear-text you can use Microsoft’s Script Encoder to encode thisÂ VBScript so Username/Password won’t be easily readable. Â Download @ http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3375

This script was created to be adapted with minimal effort to other systems, feel free to let me know if you modify this to support other systems/products.

Disclaimer: Safety is not guaranteed, test this before rolling into production.

wenus.vbs
' =============================================================
' = (W)hatsUp (E)nterprise (N)ew (U)ser (S)ystem              =
' = W.E.N.U.S.                                                =
' =                                                           =
' = By: Michael Requeny - SDPBC - IT: SRC                     =
' =     (http://michael.requeny.com)                          =
' =     &amp; Kevin Ogonowski - SDPBC - IT: SRC                   =
' = V: 1.0                                                    =
' =                                                           =
' =============================================================
' =                                                           =
' = Features:                                                 =
' = * Enumerates a LDAP Group and creates WhatsUp User        =
' =    Accounts when additions are made to the group          =
' = * Scopes WhatUp Device Group Security based on            =
' =    Department LDAP attribute &amp; Matching Whatsup           =
' =    Device Group Description                               =
' = * Outputs to WhatsUp System Activity Log                  =
' =                                                           =
' =============================================================
' =                                                           =
' =  Change Log:                                              =
' =  1.0 - 11.22.2011                                         =
' =  Inital Release                                           =
' =                                                           =
' =============================================================

' =======================
'  Configuration Section
' =======================

' DN of Group to Enumerate
Const strTargetGroupDN = "LDAP://CN=WhatsUp Remote Users,OU=Groups,DC=domain,DC=lan"

' Device Group Rights to Assign
' 0 = No / 1 = Yes
Const bGroupRead = "1"
Const bGroupWrite = "0"
Const bDeviceRead = "1"
Const bDeviceWrite = "0"

' Rights Mask to Assign
'  You can figure out the rights mask by creating a user with the rights you want all
'   these users to have, then check the WebUser table and copy the nUserRightsMask
'   column for that user into this config variable
Const strRightsMask = "277414912,6"

' SQL Server
Const strSQLServer = ""

' SQL Database
Const strSQLDB = ""

' You can use Microsoft's Script Encoder to encode this
'  VBScript so Username/Password won't be easily readable
' Download @ http://www.microsoft.com/download/en/details.aspx?displaylang=en&amp;id=3375

' SQL Username
Const strSQLUser = ""

' SQL Password
Const strSQLPass = ""

' ===========================================
' =       DO NOT EDIT PAST THIS POINT       =
' ===========================================

' Engage VBS Safety Belt
'On Error Resume Next

i = 0
intErrors = 0
Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

' Connect to Whatsup Database
objConnection.Open "Provider=SQLOLEDB;Data Source=" &amp; strSQLServer &amp; ";Trusted_Connection=Yes;Initial Catalog=" &amp; strSQLDB &amp; ";User ID=" &amp; strSQLUser &amp; ";Password=" &amp; strSQLPass &amp; ";"

' Enumerate the Group
EnumNestedgroup strTargetGroupDN

' Pause for a second so the exitting log entry is at the end
wscript.sleep(1000)
' Log on end
LogIt "Add user script ran with " &amp; intErrors &amp; " errors"

' Disconnect from SQL Server
objConnection.Close

' ============================
' =    FUNCTION JUNCTION     =
' ============================

Function EnumNestedgroup(strGroupDN)
	Set objGroup = GetObject(strGroupDN)
	For Each objMember in objGroup.Members
		If (LCase(objMember.Class) = "group") Then
			EnumNestedgroup objMember.AdsPath
		Else
			' Check if user exists
			objRecordSet.Open "SELECT nWebUserID FROM WebUser WHERE sUserName=N'" &amp; objMember.sAMAccountName &amp; "'", _
				objConnection, adOpenStatic, adLockOptimistic
				'wscript.echo "Checking User: " &amp; objMember.sAMAccountName
				Do Until objRecordSet.EOF
				i = 1
				objRecordSet.MoveNext
			Loop
			objRecordSet.Close
			If (i = 0) Then
				' LOL User doesn't exist
				strUser = objMember.sAMAccountName
				If (objMember.department &gt; 0) Then
					strDept = DeptLookup(objMember.department)
					If (strDept &gt; 0) Then
						LogIt "Creating User:  " &amp; strUser &amp; " &amp; Dept: " &amp; objMember.department &amp; " &amp; GroupID: " &amp; strDept
						CreateWUGUser strUser, strDept
					Else
						intErrors = intErrors + 1
 						LogIt "Failed to create user: " &amp; strUser &amp; " &amp; Dept: " &amp; objMember.department &amp; " &amp; GroupID: " &amp; strDept &amp; " NO GROUP ID"
					End If
				End If
			End If
			i = 0
		End If
	Next
	Set objGroup = Nothing
End Function

Function DeptLookup(strDept)
	objRecordSet.Open "SELECT nDeviceGroupID FROM DeviceGroup WHERE sNote LIKE '" &amp; strDept &amp; "'", _
		objConnection, adOpenStatic, adLockOptimistic
	Do Until objRecordSet.EOF
		For Each x In objRecordSet.Fields
			DeptLookup = x
		Next
		objRecordSet.MoveNext
	Loop
	objRecordSet.Close
End Function

Function Logit(strLogTxt)
	sDate = Now()
	' Write event to Whatsup SystemActivityLog
	objConnection.Execute "INSERT INTO SystemActivityLog (nType, dDateTime, sCategory, sSource, sData)VALUES (4,'" &amp; sDate &amp; "',N'WENUS',N'WENUS',N'" &amp; strLogTxt &amp; "')"
End Function

Function CreateWUGUser(strUser, strDeviceGroupID)
	' Create WebUser
	objConnection.Execute "INSERT INTO WebUser (sUserName,nAuthenticationType,nUserRightsMask,nHomeDeviceGroupID,nLanguageID) VALUES (N'" &amp; strUser &amp; "',2,N'" &amp; strRightsMask &amp; "'," &amp; strDeviceGroupID &amp; ",1033)"

	' Lookup New WebUserID
	objRecordSet.Open "SELECT nWebUserID FROM WebUser WHERE sUserName=N'" &amp; strUser &amp; "'", _
		objConnection, adOpenStatic, adLockOptimistic
	Do Until objRecordSet.EOF
		For Each x In objRecordSet.Fields
			strUserID = x
		Next
		objRecordSet.MoveNext
	Loop
	objRecordSet.Close

    ' Grant Access to DeviceGroup
	objConnection.Execute "INSERT INTO PivotWebUserToDeviceGroup (nWebUserID, nDeviceGroupID, bGroupRead, bGroupWrite, bDeviceRead, bDeviceWrite) VALUES (" &amp; strUserID &amp; "," &amp; strDeviceGroupID &amp; ", " &amp; bGroupRead  &amp; ", " &amp; bGroupWrite &amp; ", " &amp; bDeviceRead &amp; ", " &amp; bDeviceWrite &amp; ")"
End Function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

wenus.vbs

' =============================================================

' = (W)hatsUp (E)nterprise (N)ew (U)ser (S)ystem =

' = W.E.N.U.S. =

' = =

' = By: Michael Requeny - SDPBC - IT: SRC =

' = (http://michael.requeny.com) =

' = & Kevin Ogonowski - SDPBC - IT: SRC =

' = V: 1.0 =

' = =

' =============================================================

' = =

' = Features: =

' = * Enumerates a LDAP Group and creates WhatsUp User =

' = Accounts when additions are made to the group =

' = * Scopes WhatUp Device Group Security based on =

' = Department LDAP attribute & Matching Whatsup =

' = Device Group Description =

' = * Outputs to WhatsUp System Activity Log =

' = =

' =============================================================

' = =

' = Change Log: =

' = 1.0 - 11.22.2011 =

' = Inital Release =

' = =

' =============================================================

' =======================

' Configuration Section

' =======================

' DN of Group to Enumerate

Const strTargetGroupDN = "LDAP://CN=WhatsUp Remote Users,OU=Groups,DC=domain,DC=lan"

' Device Group Rights to Assign

' 0 = No / 1 = Yes

Const bGroupRead = "1"

Const bGroupWrite = "0"

Const bDeviceRead = "1"

Const bDeviceWrite = "0"

' Rights Mask to Assign

' You can figure out the rights mask by creating a user with the rights you want all

' these users to have, then check the WebUser table and copy the nUserRightsMask

' column for that user into this config variable

Const strRightsMask = "277414912,6"

' SQL Server

Const strSQLServer = ""

' SQL Database

Const strSQLDB = ""

' You can use Microsoft's Script Encoder to encode this

' VBScript so Username/Password won't be easily readable

' Download @ http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3375

' SQL Username

Const strSQLUser = ""

' SQL Password

Const strSQLPass = ""

' ===========================================

' = DO NOT EDIT PAST THIS POINT =

' ===========================================

' Engage VBS Safety Belt

'On Error Resume Next

i = 0

intErrors = 0

Const adOpenStatic = 3

Const adLockOptimistic = 3

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

' Connect to Whatsup Database

objConnection.Open "Provider=SQLOLEDB;Data Source=" & strSQLServer & ";Trusted_Connection=Yes;Initial Catalog=" & strSQLDB & ";User ID=" & strSQLUser & ";Password=" & strSQLPass & ";"

' Enumerate the Group

EnumNestedgroup strTargetGroupDN

' Pause for a second so the exitting log entry is at the end

wscript.sleep(1000)

' Log on end

LogIt "Add user script ran with " & intErrors & " errors"

' Disconnect from SQL Server

objConnection.Close

' ============================

' = FUNCTION JUNCTION =

' ============================

Function EnumNestedgroup(strGroupDN)

Set objGroup = GetObject(strGroupDN)

For Each objMember in objGroup.Members

If (LCase(objMember.Class) = "group") Then

EnumNestedgroup objMember.AdsPath

Else

' Check if user exists

objRecordSet.Open "SELECT nWebUserID FROM WebUser WHERE sUserName=N'" & objMember.sAMAccountName & "'", _

objConnection, adOpenStatic, adLockOptimistic

'wscript.echo "Checking User: " & objMember.sAMAccountName

Do Until objRecordSet.EOF

i = 1

objRecordSet.MoveNext

Loop

objRecordSet.Close

If (i = 0) Then

' LOL User doesn't exist

strUser = objMember.sAMAccountName

If (objMember.department > 0) Then

strDept = DeptLookup(objMember.department)

If (strDept > 0) Then

LogIt "Creating User: " & strUser & " & Dept: " & objMember.department & " & GroupID: " & strDept

CreateWUGUser strUser, strDept

Else

intErrors = intErrors + 1

LogIt "Failed to create user: " & strUser & " & Dept: " & objMember.department & " & GroupID: " & strDept & " NO GROUP ID"

End If

i = 0

End If

Set objGroup = Nothing

End Function

Function DeptLookup(strDept)

objRecordSet.Open "SELECT nDeviceGroupID FROM DeviceGroup WHERE sNote LIKE '" & strDept & "'", _

objConnection, adOpenStatic, adLockOptimistic

Do Until objRecordSet.EOF

For Each x In objRecordSet.Fields

DeptLookup = x

objRecordSet.MoveNext

Loop

objRecordSet.Close

End Function

Function Logit(strLogTxt)

sDate = Now()

' Write event to Whatsup SystemActivityLog

objConnection.Execute "INSERT INTO SystemActivityLog (nType, dDateTime, sCategory, sSource, sData)VALUES (4,'" & sDate & "',N'WENUS',N'WENUS',N'" & strLogTxt & "')"

End Function

Function CreateWUGUser(strUser, strDeviceGroupID)

' Create WebUser

objConnection.Execute "INSERT INTO WebUser (sUserName,nAuthenticationType,nUserRightsMask,nHomeDeviceGroupID,nLanguageID) VALUES (N'" & strUser & "',2,N'" & strRightsMask & "'," & strDeviceGroupID & ",1033)"

' Lookup New WebUserID

objRecordSet.Open "SELECT nWebUserID FROM WebUser WHERE sUserName=N'" & strUser & "'", _

objConnection, adOpenStatic, adLockOptimistic

Do Until objRecordSet.EOF

For Each x In objRecordSet.Fields

strUserID = x

objRecordSet.MoveNext

Loop

objRecordSet.Close

' Grant Access to DeviceGroup

objConnection.Execute "INSERT INTO PivotWebUserToDeviceGroup (nWebUserID, nDeviceGroupID, bGroupRead, bGroupWrite, bDeviceRead, bDeviceWrite) VALUES (" & strUserID & "," & strDeviceGroupID & ", " & bGroupRead & ", " & bGroupWrite & ", " & bDeviceRead & ", " & bDeviceWrite & ")"

End Function

2 Responses

WhatsUp Guru (Jason Williams) Says:
November 29th, 2011 at 3:44 pm
Great utility Mike. Thanks for taking the time to develop and then share both here and on the community site.
Michael Requeny Says:
November 29th, 2011 at 5:41 pm
Thanks for the kind works Jason!

Automated User Creation & Device Group Security for WhatsUp Gold

Overview:

WENUS Features:

2 Responses

Leave a Comment

Tags