asked on

Vbscript output help

I have a script, that compares users in 3 groups against a list in a sharepoint site.
Then I have it emailing all members not listed in the groups vs. members in the sharepoint site and vice versa to sync up.
What I'm trying to do is have each user that is listed in the groups emailed along with what group they are in.
So the email would read something like for example:
User: 700155 Group: A_Group1
User: 714455 Group: A_Group2
User: 724221 Group: A_Group3

Any expert advice is appreciated!

Bill Prew

Not sure exactly what you are looking for? Are you just looking for info on how to email from a VBS script? Or something more than that, looking for logic to compare the lists or identify certain populations, etc?

~bp

Ron Shorts

ASKER

The script already sends and email with the user in it for any differences compared from the group to the sharepoint site. However, it doesn't list specifically which group the user is in, in the email it generates, only the user. The user info is gathered into a collection among 3 groups. I wanted to break it down by specifying which user in which group is not in sharepoint, etc.,

Const ForReading = 1, ForWriting = 2
emailfrom = "group@comcast.net"
emailto = "moderator@comcast.net"

Set WshShell = CreateObject("WScript.Shell")
Set groupb = CreateObject("Scripting.Dictionary")
Set groupa = CreateObject("Scripting.Dictionary")
Set inanotb = CreateObject("Scripting.Dictionary")
Set inbnota = CreateObject("Scripting.Dictionary")
Set objfso = CreateObject("Scripting.FileSystemObject")
tempfile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") &"Tickets.txt"
Set objfile = objfso.OpenTextFile(tempfile, ForWriting, True)
sp="http://sharepointsite/AllItems.aspx"
Set spsite = WScript.GetObject(sp)    

While spsite.readystate <> "complete" 
     WScript.Sleep 200
Wend    
strtofind1=" target=_self>"  
strtofind2="</A></DIV>"
strtofind2="</A><"
groupnameB="A_Group1"
groupnameC="A_Group2"
groupnameD="A_Group3"
objfile.writeline spsite.documentElement.outerHTML
objFile.Close

Set objFile = objFSO.OpenTextFile(tempfile, ForReading)
Do Until objFile.AtEndOfStream
	strline = objFile.ReadLine
	pos=instr(strline,strtofind1)
	if pos > 0 Then
		tmp=split(strline,strtofind1)
		dnsname = trim(tmp(1))
		tmp=split(dnsname,strtofind2)
		username=ucase(tmp(0))
		if left(username,2) = "70" or left(username,2) = "71" or left(username,2) = "72" or left(username,2) = "73" or left(username,2) = "74" or left(username,1) = "75" or left(username,2) = "76" Then 
			If Not groupa.Exists(username) Then
				groupa.add username , 0
			End If
		end if
	end if
loop
objFile.Close

	getuser groupb,groupnameB,tempfile	
	getuser groupb,groupnameC,tempfile
	getuser groupb,groupnameD,tempfile

	For Each element In groupa
		if not groupb.exists(element) then
			inanotb.Add element , 0
		end if
	Next

	For Each element In groupb
		if not groupa.exists(element) then
			inbnota.Add element , 0
		end if
	Next

	emailbody=""
	for each member in inanotb
		emailbody=emailbody & vbCrLf & member
	next
	if emailbody <> "" then
		emailbody = sp & vbCrLf & emailbody
		sendemail "Users in sharepoint site but not A_xxx groups" ,emailfrom,emailto,emailbody,emailattachment
	end if

	emailbody=""
	for each member in inbnota
		emailbody=emailbody & vbCrLf & member
	next
	if emailbody <> "" then
		emailbody = sp & vbCrLf & emailbody
		sendemail "Users in A_xxx groups but not sharepoint site",emailfrom,emailto,emailbody,emailattachment
	end if

wscript.quit

sub SendEmail(emailSubject,emailfrom,emailto,emailbody,emailattachment)

	Dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = emailsubject
	objMessage.From = emailfrom
	objMessage.To = emailto
	objMessage.TextBody = emailbody
	if emailattachment <> "" then
		objMessage.AddAttachment emailattachment
	End if

	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.comcast.net"
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
	objMessage.Configuration.Fields.Update
	objMessage.Send
	emailsubject = ""
	emailbody = ""
End sub 

Sub getuser(ADUsers,groupname,strpath)
          runline =  "cmd.exe /c dsquery group dc=comcast,dc=net -name "&groupname&" | dsget group -members > " & Chr(34) & strpath & Chr(34)
          WShshell.Run  runline,0,TRUE
          set ofile=objfso.opentextfile(strpath,1)
          count=0
          Do While Not oFile.AtEndOfStream
                   Fline = oFile.Readline
                   if mid(fline,2,3)="CN=" then
                             endofline=instr(fline,",")-1
                             fline=Left(fline,endofline)
                             fline=UCase(mid(fline,5,len(fline)))
                             If Not ADUsers.Exists(Fline) Then
                                      ADUsers.add Fline , 0
                             End If
                             count=count+1
                   END if
          loop
          ofile.close
end Sub

Open in new window

Bill Prew

Okay, I think I am understanding. Just to be sure, this change would only affect the second email sent, "Users in A_xxx groups but not SharePoint site" right? Since the first email contains accounts that were not in any of the three groups?

~bp

Ron Shorts

ASKER

That's correct, thank you Bill!

ASKER CERTIFIED SOLUTION

Bill Prew

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Ron Shorts

ASKER

It's returning the members in the group, but the group is reading 0

User: 702555 Group: 0
User: 712555 Group: 0
User: 722555 Group: 0

Bill Prew

Okay, let's try adding these debug display statements to the console and see if we can get a clue...

Const ForReading = 1, ForWriting = 2
emailfrom = "group@comcast.net"
emailto = "moderator@comcast.net"

Set WshShell = CreateObject("WScript.Shell")
Set groupb = CreateObject("Scripting.Dictionary")
Set groupa = CreateObject("Scripting.Dictionary")
Set inanotb = CreateObject("Scripting.Dictionary")
Set inbnota = CreateObject("Scripting.Dictionary")
Set objfso = CreateObject("Scripting.FileSystemObject")
tempfile = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Tickets.txt"
Set objfile = objfso.OpenTextFile(tempfile, ForWriting, True)
sp = "http://sharepointsite/AllItems.aspx"
Set spsite = WScript.GetObject(sp)

While spsite.readystate <> "complete"
    WScript.Sleep 200
WEnd
strtofind1 = " target=_self>"
strtofind2 = "</A></DIV>"
strtofind2 = "</A><"
groupnameB = "A_Group1"
groupnameC = "A_Group2"
groupnameD = "A_Group3"
objfile.writeline spsite.documentElement.outerHTML
objFile.Close

Set objFile = objFSO.OpenTextFile(tempfile, ForReading)
Do Until objFile.AtEndOfStream
    strline = objFile.ReadLine
    pos = instr(strline, strtofind1)
    If pos > 0 Then
        tmp = split(strline, strtofind1)
        dnsname = trim(tmp(1))
        tmp = split(dnsname, strtofind2)
        username = ucase(tmp(0))
        If left(username, 2) = "70" Or left(username, 2) = "71" Or left(username, 2) = "72" Or left(username, 2) = "73" Or left(username, 2) = "74" Or left(username, 1) = "75" Or left(username, 2) = "76" Then
            If Not groupa.Exists(username) Then
                groupa.add username, 0
            End If
        end If
    end If
Loop
objFile.Close

getuser groupb, groupnameB, tempfile
getuser groupb, groupnameC, tempfile
getuser groupb, groupnameD, tempfile

For Each element In groupa
    If Not groupb.exists(element) Then
        inanotb.Add element, 0
    end If
Next

For Each element In groupb
    If Not groupa.exists(element) Then
Wscript.Echo "Main(groupb) - Key:" & element & ", Data:" & groupb.Item(element)
        inbnota.Add element, groupb.Item(element)
Wscript.Echo "Main(inbnota) - Key:" & element & ", Data:" & inbnota.Item(element)
    end If
Next

emailbody = ""
For Each member In inanotb
    emailbody = emailbody & vbCrLf & member
Next
If emailbody <> "" Then
    emailbody = sp & vbCrLf & emailbody
    sendemail "Users in sharepoint site but not A_xxx groups", emailfrom, emailto, emailbody, emailattachment
end If

emailbody = ""
For Each member In inbnota
    For Each group In Split(inbnota.Item(member), ",")
Wscript.Echo "EMail(inbnota) - Key:" & element & ", Data:" & inbnota.Item(element) & ", Group:" & group
        emailbody = emailbody & vbCrLf & "User: " & member & " Group: " & group
    Next
Next
If emailbody <> "" Then
    emailbody = sp & vbCrLf & emailbody
    sendemail "Users in A_xxx groups but not sharepoint site", emailfrom, emailto, emailbody, emailattachment
end If

wscript.quit

Sub SendEmail(emailSubject, emailfrom, emailto, emailbody, emailattachment)

    Dim objMessage
    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = emailsubject
    objMessage.From = emailfrom
    objMessage.To = emailto
    objMessage.TextBody = emailbody
    If emailattachment <> "" Then
        objMessage.AddAttachment emailattachment
    End If

    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.comcast.net"
    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
    objMessage.Configuration.Fields.Update
    objMessage.Send
    emailsubject = ""
    emailbody = ""
End Sub

Sub getuser(ADUsers, groupname, strpath)
    runline = "cmd.exe /c dsquery group dc=comcast,dc=net -name " & groupname & " | dsget group -members > " & Chr(34) & strpath & Chr(34)
    WShshell.Run  runline, 0, True
    Set ofile = objfso.opentextfile(strpath, 1)
    count = 0
    Do While Not oFile.AtEndOfStream
        Fline = oFile.Readline
        If mid(fline, 2, 3) = "CN=" Then
            endofline = instr(fline, ",") - 1
            fline = Left(fline, endofline)
            fline = UCase(mid(fline, 5, len(fline)))
            If ADUsers.Exists(Fline) Then
                ADUsers.Item(FLine) = ADUsers.Item(FLine) & "," & groupname
Wscript.Echo "GetUser(upd) - Key:" & Fline & ", Data:" & ADUsers.Item(FLine)
            Else
                ADUsers.Add Fline, groupname
Wscript.Echo "GetUser(add) - Key:" & Fline & ", Data:" & ADUsers.Item(FLine)
            End If
            count = count + 1
        END If
    Loop
    ofile.close
end Sub

Open in new window

~bp

Ron Shorts

ASKER

I think I had something remarked out the first time, your initial response did exactly what I wanted. Thank you so much for your help, you are a genius with scripting!

Bill Prew

Great, glad that was helpful, and thanks for the feedback!

~bp

Ron Shorts

ASKER

Could not offer a higher score, Bill Prew is a genius with scripting.