Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Please advise me to make my script more efficient and lean

Posted on 2014-11-22
6
177 Views
Last Modified: 2014-11-23
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
0
Comment
Question by:Steynsk
  • 3
  • 2
6 Comments
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 40459411
What I would do in this case is throw all of your results into an array using getrows.  Or if you prefer, just output all of the data to the browser by looping through.   Then pivot on the client using javascript.  See http://rwjblue.github.io/pivot.js/   

You will need to get your data to json.   You could do this by hand but there are some gotchas and I have had great success with the asptojson project https://code.google.com/p/aspjson/

The point is, using pivot.js opens up a lot of possibilities for you and you only have to output your data as a table.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40459412
0
 
LVL 33

Expert Comment

by:Big Monty
ID: 40459806
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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Closing Comment

by:Steynsk
ID: 40460520
Thanks this was exactly the advise I was looking for.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40460578
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.
0
 
LVL 1

Author Comment

by:Steynsk
ID: 40461131
Thanks Scott,

Good idea. I will make the first question tomorrow.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question