Solved

Vbscript  output help

Posted on 2016-08-19
10
27 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
  • 5
  • 5
10 Comments
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
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
Comment Utility
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 51

Expert Comment

by:Bill Prew
Comment Utility
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
 

Author Comment

by:Rob Shorts
Comment Utility
That's correct, thank you Bill!
0
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 total points
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Rob Shorts
Comment Utility
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 51

Expert Comment

by:Bill Prew
Comment Utility
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
Comment Utility
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 51

Expert Comment

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

~bp
0
 

Author Closing Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Unlike scripting languages such as C# where a semi-colon is used to indicate the end of a command, Microsoft's VBScript language relies on line breaks to determine when a command begins and ends. As you can imagine, this quickly results in messy cod…
Hello again, all.  For those of you that have been following along, you'll know that this is my third article on this topic (though it is not Part III).  This article is sort of remedial, and probably the topic with which I should have started the s…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

771 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

7 Experts available now in Live!

Get 1:1 Help Now