AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Steynsk
Steynsk
Flag for Netherlands asked on

Please advise me to make my script more efficient and lean

Hi Experts,

I’ve got this large classic ASP script that creates a pivot view on all ADS rights of a selected group of users. The script was, until now, a proof of concept showing it is possible to do this web enabled.  Below are the steps the script currently takes:

1. Selecting the scope

It lets the user select a group from their active directory.

2. Retrieval of members in scope

Then the script retrieves from all the members of that group the fields sAMAccountName,  department and memberof. Where memberof holds all groups that AD user a member of besides the initially selected group.

3. Processing retrieved data

The scripts splits the sub values stored in the memberof array and writes them separately in into my MS SQL table where . The table layout is included in this post.

4. Storing data

Then when all data is stored the script queries a distinct on all usernames and loads those values on to a variable.

5. Generate Pivot view

Then the script sends a pivot query using variable holding the usernames.

6. Presenting data

At the end the pivot view on the data gets presented as output and result.  Both query's are included in this post.As you can imagine the script is slow and consumes a lot of process power from my webserver. And for that reason I’d like to make it more efficient and quicker.
I’ve learned myself classic ASP (with the excellent help of this forum) but my knowledge does not reach very far yet. And besides that I'm not an English native speaker so sometimes it will take a little longer for me to understand a sollution.

What I would like to know is what steps you guys and girls would advise me to take to make the script more efficient and lean.
Important is to maintain the pivot view on the data in your suggested method . But please do not include in your advise the use of another programming language or the use of (third party) components.

Retrieval of users in scope (step2) 
SELECT sAMAccountName,department,memberof FROM 'LDAP://" & SDomain &"' WHERE memberof = '" & Group & "' ORDER by name

Open in new window


Table layout
CREATE TABLE membership(
	[memberID] [int] NOT NULL,
        [auteur] [nvarchar](50) NULL,
	[username] [nvarchar](50) NULL,
	[groupname] [nvarchar](50) NULL
)

Open in new window


Pivot query
SELECT * FROM (SELECT memberID, groupname, username FROM ADS_membership where auteur='"& Request.ServerVariables("AUTH_USER")&"') t PIVOT (MIN(memberID) FOR username IN ("&users&")) p

Open in new window


Complete script
<!--#INCLUDE FILE="_header.asp"-->
<%
Server.ScriptTimeout=800
'Set the server locale
Session.LCID = 1043
dim strToday, opteldagen
strToday = date
 
select case WeekDay( strToday )
        case 1     '-- sunday
        opteldagen = 8
        case 2     '-- monday
        opteldagen = 8
        case 3     
        opteldagen = 8
        case 4     
        opteldagen = 8
        case 5    
        opteldagen = 8
        case 6    
        opteldagen = 10
        case 7     
        opteldagen = 9
end select
zesdagen = DateAdd( "d", opteldagen, strToday )
response.write("<form name=""sending"" method=""POST"" action=""GroupPivot.asp"">")
Set CS = Server.CreateObject("ADODB.Connection")
CS.Open "Provider=SQLOLEDB;Data Source=BedrijfsbureauAppSectCP01.db.umcg.intra;Initial Catalog=BedrijfsbureauAppSectCP01; User ID=BBAPuser; Password=tU4abrat;"
Phrase = Request.Form("Phrase")
Group = Request.QueryString("Group") ' Friendly name of group
GroupName = Request.QueryString("GroupName") ' DistinguishedName of group
FuncADuser = "mydomain\myADSviewAccount"
FuncADpassword = "password"
 
' This approach to finding the domain does not have a dpenendency on authenticated user
Set objRootDSE = GetObject("LDAP://RootDSE")
sDomain = objRootDSE.Get("defaultNamingContext")
 
Set oCmd = Server.CreateObject("ADODB.Command")
 
' First step, collecting search term
if Phrase = "" and Group = "" Then
        oStatus = "Search"
        oButtonVal = "Search"
        response.write "<p align=""center"">Enter a ads groupname or a part of a groupname:<br><br><input name=""Phrase"" autofocus size=""22"">"
 
        ' Second step, displaying groups to choose from
ElseIf Phrase <> "" and Group = "" Then
        oStatus = "Select"
        response.write "<p align=""center"">Select ont of the found groups</p>"
        oCmd.CommandText = "SELECT Name,DistinguishedName FROM 'LDAP://" & SDomain &"' WHERE objectCategory = 'group' AND name ='*" & Phrase & "*' order by name"
 
        ' Final step, storing the found info into the database and Pivot it.
ElseIf Phrase = "" and Group <> "" Then
        oStatus = "Finish"
        oButtonVal = "Diffrent group?"
        response.write "<p align=""center"">This are the members of the selected group <strong>"& GroupName &"</strong>:</p>"
        oCmd.CommandText = "SELECT sAMAccountName,department,memberof FROM 'LDAP://" & SDomain &"' WHERE memberof = '" & Group & "' ORDER by name "
        ' Just in case something weird happens
Else
        response.write "<p>Invalid Page Access</p>"
        oButtonVal = "Different group?"
        oStatus = "Search"
End If
 
' If this isn't the first step, process
If oStatus <> "Search" Then
        Set Conn = Server.CreateObject("ADODB.Connection")
        Set RS = Server.CreateObject("ADODB.Recordset")
        Conn.Provider = "ADsDSOObject"
        Conn.Properties("User ID") = FuncADuser
        Conn.Properties("Password") = FuncADpassword
        Conn.Properties("Encrypt Password") = True
        strConn = "Active Directory Provider"
        Conn.Open strConn , FuncADuser, FuncADpassword
        Set oCmd.ActiveConnection = Conn
        Set oRS = oCmd.Execute
        dim sql
        sql3="delete from ADS_membership where auteur='"&Request.ServerVariables("AUTH_USER")&"';"
        Set RS3 = Server.CreateObject("ADODB.Connection")
        RS3.Open CS
        RS3.Execute sql3
        RS3.Close
        response.write "<table class=""breed"" align=""center"" border=0>"
        if not oRS.EOF Then
                  count =0
                  oRS.MoveFirst
                  Do Until oRS.EOF
                          If oStatus = "Select" Then
                                        response.write "<tr><td><a href=""GroupPivot.asp?Group=" & oRS.Fields("DistinguishedName") & "&GroupName=" & oRS.Fields("Name") &""">" & oRS.Fields("Name") & "</td></tr>"
                                        URL = "GroupPivot.asp?Group=" & oRS.Fields("DistinguishedName") & "&GroupName=" & oRS.Fields("Name")
                                        count=count+1
                          Else
                                        membership=oRS.Fields("memberof")
                                        For each group in membership
                                                newgroup=split(group,"=")
                                                groupname= left(newgroup(1), len(newgroup(1))-3)
                                                groupname = Replace (groupname, "\", "\\'")
                                            groupname = Replace (groupname, "'", "''")
                                               
                                                sql2= sql2 & "insert INTO ADS_membership (auteur, username, groupname) VALUES ('" & Request.ServerVariables("AUTH_USER") & "','" & oRS.Fields("sAMAccountName") & "','" & groupname & "');"
                                        Next
                                        sql2 = left(sql2,Len(sql2)-1)
                                        Set RS1 = Server.CreateObject("ADODB.Connection")
                                        RS1.Open CS
                                        RS1.Execute sql2
                                        RS1.Close 
                          End If
                          oRS.MoveNext
                  Loop
                  if count=1 then
                        response.redirect(URL)
                  end if
                  oRS.Close
        If oStatus <> "Select" Then
                SQL4 = "SELECT DISTINCT username FROM ADS_membership where auteur='"& Request.ServerVariables("AUTH_USER")& "'"
                Set RS4 = Server.CreateObject("ADODB.Recordset")
                RS4.Open SQL4, cs
                users=""
                count=0
                Do While Not RS4.EOF
                        users= users & "["&RS4("username") & "],"
                        count=count+1
                        RS4.MoveNext
                Loop
 
                RS4.Close
                users = left(users,Len(users)-1)
                SQL5 = "SELECT * FROM (SELECT memberID, groupname, username FROM ADS_membership where auteur='"& Request.ServerVariables("AUTH_USER")&"') t PIVOT (MIN(memberID) FOR username IN ("&users&")) p"
                Set RS5 = Server.CreateObject("ADODB.Recordset")
                RS5.Open SQL5, cs
                Response.Write "<table class=""breed"" align=""center"" border=1><tr>"
                i=0
                For Each objField in rs5.Fields
                        if i =0 then
                                Response.Write "<TH colspan=3 align=""right"">Edit -></TH>"
                        else
                                Response.Write "<td><a href=""user_edit.asp?username="&objField.Name&"&maingroup="&GroupName&""" target=""_blank""><img src='images/edit.png'style=""border-style: none""'></a></td>"
                        end if
                        i=i+1
                Next
                Response.Write "</tr><tr>"
               
                i=0
                For Each objField in rs5.Fields
                        if i =0 then
                                Response.Write "<th></th><th class=""vertical""><- Edit</th><th>" & objField.Name & "</th>"
                        else
                                Response.Write "<th class=""vertical"">"&objField.Name&"</th></td>"
                        end if
                        i=i+1
                Next
                Response.Write "</tr>"
                count=1
                Do While Not RS5.EOF
                        strExceptions = "APSWEBProxyGG, usersPST01, users01, users02, users03, users04, users05, users06, users07, users08, users09, users10, users11, users12, users13, users14, users15, users16, users17, users18, users19, users20, users21, users22, users23, users24, users25, users26, users27, users28, users29, users30, users31, users32, users33, users34, users35, users36, users37, users38, users39, users40, users41, users42, users43, users44, users45, users46, users47, users48, users49, users50, users51, users52, users53, users54, users55, users56, users57, users58, users59, users60, users61, users62, users63, users64, users65, users66, users67, users68, users69, users70, users71, users72, users73, users74, users75, users76, users77, users78, users79, users80, users81, users82, users83, users84, users85, users86, users87, users88, users89, users90, users91, users92, users93, users94, users95, users96, users97, users98, users99, DWSusersGG"
                        if InStr(strExceptions, RS5("groupname")) = 0 then
                        Response.Write "<tr>"
                        i=0
                        For Each objField in rs5.Fields
                                if i =0 then
                                        if left(rs5(objField.Name),3)="APW" OR left(rs5(objField.Name),3)="Res" OR left(groupname,3)="RES" OR left(rs5(objField.Name),3)="APS" OR left(rs5(objField.Name),3)="PF " then
                                                Response.Write "<td>"&count&"</td><td><a href=""http://bbato.sectorc.umcg.nl/ads/group_edit.asp?groupname="&rs5(objField.Name)&""" target=""_new""><img src='images/edit.png'style=""border-style: none""></a></td>"
                                                edit = "True"
                                        else
                                                Response.Write "<td>"&count&"</td><td></td>"
                                                edit = "False"
                                        end if
                                        Response.Write "<td><a href=""http://bbato.sectorc.umcg.nl/ads/group_members.asp?Phrase="&rs5(objField.Name)&""" target=""_new""><b>"&rs5(objField.Name)&"</b></a></td>"
 
                                        count=count+1
                                else
                                        if rs5(objField.Name)<>"" then
                                                if edit= "True" then
                                                Response.Write "<td align=""center""><a href=""http://bbato.sectorc.umcg.nl/ads/incidenteel_edit.asp?id="&rs5(objField.Name)&""" target=""_new""><b><font color=""red"">X</font></b></a></td>"
                                                else
                                                Response.Write "<td align=""center""><b><font color=""red"">X</font></b></td>"
                                                end if
                                        else
                                                Response.Write "<td></td>"                                     
                                        end if
                                end if
                                i=i+1
                               
                        Next
                        Response.Write "</tr>"
                        end if
                        RS5.MoveNext
                Loop
                RS5.Close
        end if
Else
        oButtonVal = "Diffrent group?"
        oStatus = "Once more?"
End If
Set rs = Nothing
Conn.Close
End If
 
' Only display the button when it is relevant
If oStatus <> "Select" Then
  response.write "<p align=""center""><input type=""submit"" value=""" & oButtonVal & """ name=""Send""></p>"
End If
set oCmd = Nothing
%>
</form>
</body>
</html>

Open in new window


Kind regards,

Steynsk
ASPWeb Languages and Standards
Avatar of undefined
Last Comment
Steynsk
8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Fell
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott Fell
Big Monty
if the data doesn't change too often, and/or you don't need to provide real time data, you should look into caching techniques. Then you could run a vbs script every x amount of days to pull the latest data and build that data into a pre-existing page that your asp app could link to.

if that's not an option, you may want to look into paging the data, meaning only grab a specified size chunk of the data and outputting it to the screen, 4Guys has a great article on how to do that:

http://www.4guysfromrolla.com/webtech/042606-1.shtml
Steynsk
ASKER
Thanks this was exactly the advise I was looking for.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott Fell
Thank you Steynsk, do you want to provide some sample output and let's try to make this work for you.  Or take it in steps, perhaps make one new question to output your data using asptojson, then a 2nd question for taking the json code to pivot.js in the javascript threads.
Steynsk
ASKER
Thanks Scott,

Good idea. I will make the first question tomorrow.
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent