All Forums >> [Scripting] >> WSH & Client Side VBScript >> Trying to find % of total active user home folders per server... Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!
A while ago the senior admins gave me a scripting task. We have three servers that store users' home folders, and my task is to write a script that determines what percentage of active users' folders is being stored on each server. Here's my strategy so far:
- Grab the AD homeDirectory attribute from the users' profile - Grab the server name out of the homeDirectory attribute by using InStr to search for the position of the server name at 0 (or 2, if allowing for "\\") and using that in an "if" statement - Store it in a dictionary object, with the key being the SAM account name and the item being the server name - Use the .Count property to get the number of pairs in the dictionary object (separate dictionary object for each server name, since there doesn't seem to be a way to tally specific items in a dictionary object) - Run those against total number of (active) users for % on each server - Output results to text file
Since I'm only interested in active users, I've found that I can use ADS_UF_ACCOUNTDISABLE to get the status of a user account. The main stumbling point I'm running into is ensuring that I'm only querying for active user accounts - assuming for the moment that I'm not worried about picking up service accounts. I don't want to query the whole of the AD domain; the query will be confined to a few OUs in which user accounts are stored. Then, once I have number of total active users, if the .Count property of those dictionary objects gives me straight integers, I'm thinking I can just use simple arithmetic functions to get the percentages I need.
Anyway, that's my half-arsed little plan so far. Am I making anything needlessly complicated? Bear in mind that I'm a total, total novice to VBScript and may even be misunderstanding some fundamental concepts.
Well, it's not really a question of code syntax - and I'd be embarrassed to post what I have since it's only a few lines (far from complete) and I know some of it's wrong. I can work out the code itself eventually; what I'm really looking for is just strategic guidance, you know, how I should approach it.
The task itself is simple in concept. We have three servers that hold our users' home folders. What I need to do is find how many active users have their folders on each server, and then compare that to the total number of active users/folders for percentages.
What I was planning to do was create a script that looks in certain OUs for active users, and then, from those active users, read their homeDirectory attribute (to get the name of the server they're on). Then, I would associate the server names with some keys (say, maybe the corresponding usernames), and pair up the keys/items in dictionary objects. There would be a separate dictionary object for each server, so there would be three dictionary objects, though I guess I could use arrays too. I'd run a count of how many pairings were in each array/dictionary object, and then compare each count against the total number of active users to get the percentages I needed.
I'm trying to find an elegant way to query for just active user accounts - to take care of that at the beginning of the script, because disabled accounts won't play a part in this at all. I also put a few more details in my previous post. Does my strategy look sound or am I totally off the mark?
I'm not familar with the dictionary object in scripting, but in my last job, (and even now in my current job), I was tasked with many similar scripting tasks. (ex, user disk usage statistics, printing statistics, etc.)
Probably since my lack of dictionary object knowledge, I found myself exported my vbscripts to MS Access Tables.
I found that sending your scripting output to Microsoft Access tables, enabled me to play with the data much more easier, than doing within side a script. (Since most times I found that once I presented the data, my boss would turn around and ask for the output in a different format. (which MS Access made it much easier to do).
If you like this idea and/or approach, I can post some very basic scripts that output information to an Access database which I think you can easily change to fit your needs.
Yes, I'd be interested in that - I just chose a text file output as a default, really, but I'm sure an Access/Excel format would be much more flexible.
I can't see any glaring problems with what you plan to do. If however the end goal is to generate three numbers, % of users on Server A, % of users on Server B and % of users on server C, then I think you are making it overly complicated. I would just go through the AD query and keep 3 running totals nServerAUsers, nServerBUsers, and nServerCUsers. Then as you go through the results of the AD queries, simply increment the appropriate counter. At the end:
I posted this before in the "post a script" section, but now added the "account disabled" property from the user object.
One note, this will only export up to 1000 users. If you want more, you'll have to use a different database provider, (which I don't have code for that in vbscript, only vb.net). A quick way to get more than 1000, would be to run the script more than once and change the objDomain variable to a different LDAP path in your directory. (right now it binds to the top of your domain).
I've tried to comment the code as best as possible so you can get an understanding of how to work with the Excel com object.
-Eric
< Message edited by edavis6678 -- 1/27/2006 5:15:56 AM >
Thanks for all the help so far. I've finally gotten a script "finished" but, you guessed it, it won't work. Here's the piece of code that seems to be giving me trouble (the rest is all boilerplate AD connection and variable-declaring stuff):
objCommand.CommandText = _ "SELECT homeDirectory FROM 'LDAP://dc=domain,dc=com' WHERE objectClass='user'" ' Not sure whether objectClass=user is correct to use Set objRecordSet = objCommand.Execute
' Steps through user records, plucks out home directory path, creates dictionary objects according to the results
objRecordSet.MoveFirst Do Until objRecordSet.EOF strHomeDirectory = objRecordSet.Fields("homeDirectory").Value strUsername = objRecordSet.Fields("userPrincipalName").Value
For Each strHomeDirectory In objRecordSet.Fields If InStr(0, strHomeDirectory, "server01") > 0 then Group1.Add "server01," strUsername Else If InStr(0, strHomeDirectory, "server02") > 0 then Group2.Add "server02," strUsername
objRecordSet.MoveNext Loop
I'm not at work right now, so I can't connect to the domain to test, but I remember that the error being given is referring to the first "If Instr..." line. WSH told me it was expecting an "end of statement."
Any ideas on what I'm missing? I've been over this a million times and I can't figure it out for the life of me. The point of this particular piece is to examine the homeDirectory attribute of all users to see what server they're on, and then create a couple of dictionary objects corresponding to each server (each object would be filled with a list of users on each server, so two in this case).
< Message edited by Exidy -- 2/6/2006 3:21:48 AM >
Line: 41 Char: 25 Error: Expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error
I don't know whether WSH counts the first line of the script as line 0 or line 1, so line 41 refers to either
If InStr(0, strHomeDirectory, "mkcsint08") > 0 then
or
Group1.Add "mkcsint08," strUsername
I've looked over the syntax repeatedly and I just can't figure out where it's erroring out. All I want to do is grab the homeDirectory attribute of all users to see what server they're on (they can be on one of two servers), then take that attribute and stick it in a dictionary object alongside the userPrincipalName.
The weird thing is that character 25 in both of those lines lands right in the middle of "strhomeDirectory" or "strUsername." The only line on which that doesn't happen is
For Each strHomeDirectory In objRecordSet.Fields
In that case, character 25 lands right after "strHomeDirectory." I'm thinking there's just something really basic that I'm missing here.
Here's the whole code, pathetic misshapen thing that it is:
On Error Resume Next
' Define various variables and objects
Dim Group1 ' Create variable for first dictionary object (server01) Dim Group2 ' Create variable for second dictionary object (server02) Dim Count1 Dim Count2 Set Group1 = CreateObject("Scripting.Dictionary") ' Create the actual dictionary object Set Group1 = CreateObject("Scripting.Dictionary") ' Create the actual dictionary object Set Count1 = Group1.Count Set Count2 = Group2.Count
' Establish connection to Active Directory
Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active Directory Provider" Set objCommand.ActiveConnection = objConnection objCommand.Properties("Page Size") = 1000 objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE objCommand.CommandText = _ "SELECT homeDirectory FROM 'LDAP://dc=mycompany,dc=com' WHERE objectClass='user'" ' Not sure whether objectClass=user is correct to use Set objRecordSet = objCommand.Execute
' Steps through user records, plucks out home directory path, creates dictionary objects according to the results
objRecordSet.MoveFirst
Do Until objRecordSet.EOF strHomeDirectory = objRecordSet.Fields("homeDirectory").Value strUsername = objRecordSet.Fields("userPrincipalName").Value For Each strHomeDirectory In objRecordSet.Fields If InStr(0, strHomeDirectory, "server01") > 0 then Group1.Add "server01", username Else If InStr(0, strHomeDirectory, "server02") > 0 Then Group2.Add "server02", username objRecordSet.MoveNext End If Loop
' Gets percentages of users on each server
strTotalUsers = objRecordSet.RecordCount Dim Percent1 Dim Percent2 Percent1 = Count1 / strTotalUsers Percent2 = Count2 / strTotalUsers
' Output results to text file
strPath = "C:\HomeFolderStats.txt" Set objfso = CreateObject("Scripting.FileSystemObject") Set strFile = objfso.CreateTextFile(strPath, True) strFile.WriteLine("Number of users on server01: " & Group1.Count) strFile.WriteLine("Number of users on server02: " & Group2.Count) strFIle.WriteLine("Total number of users: " & strTotalUsers) strFile.WriteLine("Percentage of users on server01: " Percent1) strFile.WriteLine("Percentage of users on server02: " Percent2) strFile.Close
< Message edited by Exidy -- 2/7/2006 6:17:07 AM >
First I will start by saying that On Error Resume Next at the top of your script is an evil construct. It should only be used to trap expected errors at the place in the code where you expect the error to occur not globally. Now I will get off that soap box since that is not what is causing your issue right now and get on the soap box that is related to your problem. Always block indent your code. Proper block indentation would make your problem glaringly obvious (well at least a problem that would cause symptoms like what you have described). I can't run your code so I can't be sure it fixes all your problems, but see the below explaination:
Here is a chunk of your code:
Do Until objRecordSet.EOF strHomeDirectory = objRecordSet.Fields("homeDirectory").Value strUsername = objRecordSet.Fields("userPrincipalName").Value For Each strHomeDirectory In objRecordSet.Fields If InStr(0, strHomeDirectory, "server01") > 0 then Group1.Add "server01," username Else If InStr(0, strHomeDirectory, "server02") > 0 then Group2.Add "server02," username objRecordSet.MoveNext Loop
Here is the same chunk of code properly indented:
Do Until objRecordSet.EOF strHomeDirectory = objRecordSet.Fields("homeDirectory").Value strUsername = objRecordSet.Fields("userPrincipalName").Value For Each strHomeDirectory In objRecordSet.Fields If InStr(0, strHomeDirectory, "server01") > 0 then Group1.Add "server01," username Else If InStr(0, strHomeDirectory, "server02") > 0 Then Group2.Add "server02," username objRecordSet.MoveNext Loop
Do you see where you start the For Each loop? It should be pretty obvious because the code below it is indented further. Now do you see a Next statement with the same level of indentation indicating the end of your For Each block? You do not see one because there isn't one. There should be one. Next, do you see the If-Then statement? It should be able to spot because the code below it is indented further. Now, do you see the Else line? Again it should be easy to spot due to the indentation. Now, do you see an End If statement that is indented to the same level as the If-Then and Else statements indicating that it closes the If-Then construct? You won't see one becuase it is not there. This problem was spotted in less than 10 seconds once the code was indented properly. Below is the code with the corrections:
Thanks for the advice on indentation, I will be following it in the future. (I knew about it in the back of my head, but I was so focused on the error message itself that it never occurred to me that proper indentation might actually aid troublehooting, heh.)
The "Expected end of statement" error itself remains. According to what I've Googled, that message refers to a simple syntax error - a misplaced comma, ampersand, period, etc.
I still can't find where the heck such an error could be... as far as I can tell, I'm using the "If Instr" command correctly, just telling it to search for a substring in a string. Am I missing something really obvious here?
Oh man, that's what I get for following English language conventions rather than programming conventions! Thanks once again. Now it gave me an "Unexpected Next," so I removed the Next, and then it claimed "Loop without Do" even though there's clearly a "Do Until" at the beginning of the loop. Sometimes this debugger really makes my head spin. Anyway, I'll look at it and see if I can puzzle it out.
Ok, here is the code from #11 with some fixes. I didn't use code tags so that I could used colored text to show the changes:
On Error Resume Next
' Define various variables and objects
Dim Group1 ' Create variable for first dictionary object (server01) Dim Group2 ' Create variable for second dictionary object (server02) Dim Count1 Dim Count2 Set Group1 = CreateObject("Scripting.Dictionary") ' Create the actual dictionary object Set Group1 = CreateObject("Scripting.Dictionary") ' Create the actual dictionary object Set Count1 = Group1.Count Set Count2 = Group2.Count
' Establish connection to Active Directory
Set objConnection = CreateObject("ADODB.Connection") Set objCommand = CreateObject("ADODB.Command") objConnection.Provider = "ADsDSOObject" objConnection.Open "Active Directory Provider" Set objCommand.ActiveConnection = objConnection objCommand.Properties("Page Size") = 1000 objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE objCommand.CommandText = _ "SELECT homeDirectory FROM 'LDAP://dc=mycompany,dc=com' WHERE objectClass='user'" ' Not sure whether objectClass=user is correct to use Set objRecordSet = objCommand.Execute
' Steps through user records, plucks out home directory path, creates dictionary objects according to the results
objRecordSet.MoveFirst
Do Until objRecordSet.EOF strHomeDirectory = objRecordSet.Fields("homeDirectory").Value strUsername = objRecordSet.Fields("userPrincipalName").Value For Each strHomeDirectory In objRecordSet.Fields If InStr(0, strHomeDirectory, "server01") > 0 then Group1.Add "server01", username Else If InStr(0, strHomeDirectory, "server02") > 0 Then Group2.Add "server02", username End If 'All If-Then statements must have a corresponding End If (The exception is single line if-thens but we won't address those here) End If Next 'All For-Next statements must have a corresponding Next statement (no exceptions to this one) objRecordSet.MoveNext 'I moved this here because where you had it the recordset would have only moved to the next record if strHomeDirectory did not have server01 in it and did have server02 in it Loop
' Gets percentages of users on each server
strTotalUsers = objRecordSet.RecordCount Dim Percent1 Dim Percent2 Percent1 = Count1 / strTotalUsers Percent2 = Count2 / strTotalUsers
' Output results to text file
strPath = "C:\HomeFolderStats.txt" Set objfso = CreateObject("Scripting.FileSystemObject") Set strFile = objfso.CreateTextFile(strPath, True) strFile.WriteLine("Number of users on server01: " & Group1.Count) strFile.WriteLine("Number of users on server02: " & Group2.Count) strFIle.WriteLine("Total number of users: " & strTotalUsers) strFile.WriteLine("Percentage of users on server01: " Percent1) strFile.WriteLine("Percentage of users on server02: " Percent2) strFile.Close
Much appreciated, ebgreen - I guess it would've helped if I'd remembered the basic logic for if-then and for-next statements, huh?
I caught some missing ampersands in the last section of my code. The output itself is '0' for both numbers of users, and the percentages don't even exist, but I should be able to troubleshoot that.
Printable Version
Trying to find % of total active user home folders per ... - 
