Solved

Vbscript  output help

Posted on 2016-08-19
10
40 Views
Last Modified: 2016-08-19
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!
0
Comment
Question by:Rob Shorts
[X]
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
  • 5
  • 5
10 Comments
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41762681
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
0
 

Author Comment

by:Rob Shorts
ID: 41762700
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

0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41762837
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
1
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rob Shorts
ID: 41762856
That's correct, thank you Bill!
0
 
LVL 54

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 41762893
Naturally I can't test this here easily, but give this a try.  Basically we add each group that we find the user in into the groupb dictionary as a comma delimited string associated with that dictionary key item.  We also pass this along to the inbnota dictionary during that matching process.  Then when we build the email we get the users as well as their groups and place that in the email.

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, groupb.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), ",")
        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
            Else
                ADUsers.add Fline, groupname
            End If
            count = count + 1
        END If
    Loop
    ofile.close
end Sub

Open in new window

~bp
0
 

Author Comment

by:Rob Shorts
ID: 41762938
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
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41762956
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
1
 

Author Comment

by:Rob Shorts
ID: 41763016
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!
1
 
LVL 54

Expert Comment

by:Bill Prew
ID: 41763027
Great, glad that was helpful, and thanks for the feedback!

~bp
0
 

Author Closing Comment

by:Rob Shorts
ID: 41763044
Could not offer a higher score, Bill Prew is a genius with scripting.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.
Suggested Courses

732 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