Solved

Please advise me to make my script more efficient and lean

Posted on 2014-11-22
6
158 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
Comment Utility
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
Comment Utility
0
 
LVL 32

Expert Comment

by:Big Monty
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Closing Comment

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

Expert Comment

by:Scott Fell, EE MVE
Comment Utility
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
Comment Utility
Thanks Scott,

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now