Solved

Can this be done with VB Scripts?

Posted on 2007-04-02
29
269 Views
Last Modified: 2010-04-16
Hi All,
I just need to know if the following is possible.  Every month we perform specific tasks.  One task I have is just plain annoying.  I have to manually compare my AD user lists vs Assentor user list.  Assentor is made by Computer Associates.  It is a SQL db that will need to be queried.
I would like to create a VBS that I can double click, and do this task for me.  So for example, if User A is in AD, but not in Assentor, I'd like for the VBS to dislpay a page with all the users that are NOT in both tables..
So when the script checks AD and Assentor, both users MUST appear in the list so that it doesn't show on the report.  If a user only exists in 1 table, that will then show up on the page at the end of the VBS.
Anyone know if this can be done?  I would imagine its possible, but don't know where to start.

Thanks for any input.
0
Comment
Question by:jsctechy
  • 15
  • 14
29 Comments
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
ID: 18836233
I'm sure it can be done if the Assentor is simply a SQL db.

You'd create an ado reference to the SQL db
http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts/enterprise/databases/
http://www.15seconds.com/issue/000323.htm

Then I would probably poll specifically for that 'name' (presumably it's the logon name?) in AD:

Here's a sample script from the scripting guys...

On Error Resume Next

Const ADS_SCOPE_SUBTREE = 2

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 Name FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user'"   'Here you would adjust the query to read "Where...AND SamAccountName='" & strUser & "'" ' where strUser is the logon name pulled from Assentor
Set objRecordSet = objCommand.Execute

'Here - if the recordset is empty - no record was found...
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18836291
Sir,
Although this is confusing to me- I will sit down w/ my DBA and try to work it out with him.

I'll post back ASAP.

Thanks
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18836321
Okay - you'll need the connection string to the SQL - he can probably help you with that bit...then it's just a matter of looping through the recordset. :^)
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18836398
Yeah, he should be able to help when he gets back.  Although he doesn't know anything about vbscripting.  But he might be able to make sense of it.
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18838284
Sir,
Why do I need the following?
Const ADS_SCOPE_SUBTREE = 2

0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18838336
So that it searches below the starting path.
If that wasn't included - it'd simply look for the users right in the domain container...where they won't (or shouldn't) be...with the subtree, it'll scour your OU containers as well...
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18838448
The
Const ADS_SCOPE_SUBTREE = 2
Is telling the script to search subtrees?  What is the 2 value?

Tell me if I am right-

On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")  Will set the word 'objConnection' so you don't have to type out 'createobject("adodb.command") everytime you need that function?
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"                    Uses 'adsdsoobject' too open the connection?
objConnection.Open "Active Directory Provider"            Opening AD DB?
Set objCommand.ActiveConnection = objConnection       No idea

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE              set search to search sub OUs

objCommand.CommandText = _  
    "SELECT Name FROM 'LDAP://dc=fabrikam,dc=com' WHERE objectCategory='user'"   'Here you would adjust the query to read "Where...AND SamAccountName='" & strUser & "'" ' where strUser is the logon name pulled from Assentor                                                     specifies the query
Set objRecordSet = objCommand.Execute                               No idea

'Here - if the recordset is empty - no record was found...

If you have a few minutes- please let me know.


Thanks again
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18838521
The actual 'value' that the script 'needs' is 2.
What 2 means to you and me is - "search subtrees".
It could just as easily have been

Const DIG_UNDER_GROUND = 2

as long as you reference that same constant instead - when you set up the parameters for the scope of your query.


Set objConnection = CreateObject("ADODB.Connection")  Will set the word 'objConnection' so you don't have to type out 'createobject("adodb.command") everytime you need that function?

"sort-of"...In actuallity - this goes beyond merely setting a variable - you're setting an object reference. So, think of that object reference as containing the entire package of goodies for an ADODB.Connection.  It means that you can reference all of the methods that the connection would interpret by simply using objConnection.<Method> (Open for instance).

objConnection.Provider = "ADsDSOObject"                    Uses 'adsdsoobject' too open the connection?
This is merely the access mechanism we're using to query the data.  It uses ADSI (DSO is Data Source Object)

objConnection.Open "Active Directory Provider"            Opening AD DB?
In a manner of speaking, yes..

Set objCommand.ActiveConnection = objConnection       No idea
:)  This is just stating that the connection to be used for our 'command' (the query) is to use the one we've outlined in the objeConnection reference.

...specifies the query (yes)

Set objRecordSet = objCommand.Execute                               No idea
Sets another object reference to initiate (or execute) the command that's been setup (the sql query above)

Hope that helps.
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18838766
Yes that helps.  So if I do the following, this will search my domain (win 2000) for all the users and display their names?

Const SearchSub = 2
Set objConnection = CreateObject("ADODB.Connection")
'sets objConnection to use ADODB.Connection options by only typing objConnection
Set objCommand = CreateObject("ADODB.Command")
'sets objCommand to use all the ADODB.Command options by only typing objCommand

objConnection.Provider = "ADsDSOObject"
'What will be used to query

objConnection.Open = "Active Directory Provider"
'Open the AD DB - Sort of

Set objConnection.ActiveConnection = objConnection
'States the connection to be used for the Query (command) is to use what was outlined in the objConnection reference

objCommand.Properties("page size") = 1000
' Sets the page size property of objCommand (ADODB.Command) to 1000

objCommand.Properties("searchscope") = SearchSub
' Sets the Search Scope property of objCommand to a value of 2 (see Const)

objCommand.CommandText _
"select Name from Ldap://dc=jscinc,dc=com where objectcatagory='user'"
'The Query

Set objRecordSet = objCommand.Execute
' Uses the Execute command of 'objCommand'



0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18838805
Well, yes and no...

objCommand.CommandText _
"select Name from Ldap://dc=jscinc,dc=com where objectcatagory='user'"
'The Query

Once this is executed (via the line below it in your last post) - then the Recordset will 'contain' the results...
Recordsets are by nature, stingy.  They won't let you know what they've got, unless you poke and prod them about it... :^)
So, you'll need to loop through the results using something like:

Do while not objRecordSet.EOF 'Start looping and continue until EOF is encountered

Now we're "in" a loop...What do you want to do while you're here?
Want to simply 'show' the names of the users?  Accomplish that using

wscript.echo objRecordSet.Fields("Name").Value '(or you can use Fields(0) - since you're only pulling back the "Name" field in your query
Then, you'll need to move the pointer using
objRecordSet.MoveNext
Don't forget to close the Do/Loop with the last line:

Loop

'Condensed version:
Do while not objRecordSet.EOF
  wscript.echo objRecordSet.Fields("Name").Value
  objRecordSet.MoveNext
Loop

0
 
LVL 1

Author Comment

by:jsctechy
ID: 18838851
Okay-
Something like this, just to see all my users in AD-

Const SearchSub = 2
Set objConnection = CreateObject("ADODB.Connection")
'sets objConnection to use ADODB.Connection options by only typing objConnection
Set objCommand = CreateObject("ADODB.Command")
'sets objCommand to use all the ADODB.Command options by only typing objCommand

objConnection.Provider = "ADsDSOObject"
'What will be used to query

objConnection.Open = "Active Directory Provider"
'Open the AD DB - Sort of

Set objConnection.ActiveConnection = objConnection
'States the connection to be used for the Query (command) is to use what was outlined in the objConnection reference

objCommand.Properties("page size") = 1000
' Sets the page size property of objCommand (ADODB.Command) to 1000

objCommand.Properties("searchscope") = SearchSub
' Sets the Search Scope property of objCommand to a value of 2 (see Const)

objCommand.CommandText _
"select Name from Ldap://dc=josephstevens,dc=com where objectcatagory='user'"
'The Query

Loop

'Condensed version:
Do while not objRecordSet.EOF
  wscript.echo objRecordSet.Fields("Name").Value
  objRecordSet.MoveNext
Loop
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18838870
Only think that's standing out is that your commandtext doesn't appear to be assigned...

objCommand.CommandText ="select Name from Ldap://dc=josephstevens,dc=com where objectcatagory='user'"

But that should work, yes..
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18838999
hey-
Thanks, I got an error, like 25 chr 1
error:  'loop' without 'do'
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839035
Yep, there was another loop up there...
Const SearchSub = 2
Set objConnection = CreateObject("ADODB.Connection")
'sets objConnection to use ADODB.Connection options by only typing objConnection
Set objCommand = CreateObject("ADODB.Command")
'sets objCommand to use all the ADODB.Command options by only typing objCommand

objConnection.Provider = "ADsDSOObject"
'What will be used to query

objConnection.Open = "Active Directory Provider"
'Open the AD DB - Sort of

Set objConnection.ActiveConnection = objConnection
'States the connection to be used for the Query (command) is to use what was outlined in the objConnection reference

objCommand.Properties("page size") = 1000
' Sets the page size property of objCommand (ADODB.Command) to 1000

objCommand.Properties("searchscope") = SearchSub
' Sets the Search Scope property of objCommand to a value of 2 (see Const)

objCommand.CommandText _
"select Name from Ldap://dc=josephstevens,dc=com where objectcatagory='user'"
'The Query

'Condensed version:
Do while not objRecordSet.EOF
  wscript.echo objRecordSet.Fields("Name").Value
  objRecordSet.MoveNext
Loop
0
IT, Stop Being Called Into Every Meeting

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!

 
LVL 1

Author Comment

by:jsctechy
ID: 18839092
Set objConnection.ActiveConnection = objConnection

Error - Object doesn't support named arguments: 'objConnection.ActiveConnection'
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839103
That should be the command...
Set objCommand.ActiveConnection = objConnection
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18839125
oh, had 'set objConnection.ActiveConnection = objConnection'
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18839140
Do while not objRecordSet.EOF

Error: Object Required; 'objRecordSet'
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839187
Crucial step missed...(see, told you the recordset was stingy)

Set objRecordSet = objCommand.Execute 'have to execute it..
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839198
For 'proper' coding, you should always state where you're starting too (I, personally never do and have never run into a problem, but...thought I'd include this:)

Set objRecordSet = objCommand.Execute 'Execute the command

objRecordSet.MoveFirst 'Move pointer to the first returned record
Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields("Name").Value
    objRecordSet.MoveNext
Loop
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18839200
Sir,
Thanks- I just saw that as well.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839211
No worries. ;)
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18839242
AH!  Another problem-
One or more errors occured during processing of command.
Code: 8000E14
Source: Provider
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18839253
Do I need to do this?

strQuery = ""select Name from Ldap://dc=josephstevens,dc=com where objectcatagory='user'"
 
Set objRecordSet = objConnection.Execute(strQuery)

0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839283
Why don't you post the script as-is right now...
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18839311
Const SearchSub = 2
Set objConnection = CreateObject("ADODB.Connection")
'sets objConnection to use ADODB.Connection options by only typing objConnection
Set objCommand = CreateObject("ADODB.Command")
'sets objCommand to use all the ADODB.Command options by only typing objCommand

objConnection.Provider = "ADsDSOObject"
'What will be used to query

objConnection.Open = "Active Directory Provider"
'Open the AD DB - Sort of

Set objCommand.ActiveConnection = objConnection
'States the connection to be used for the Query (command) is to use what was outlined in the objConnection reference

objCommand.Properties("page size") = 1000
' Sets the page size property of objCommand (ADODB.Command) to 1000

objCommand.Properties("searchscope") = SearchSub
' Sets the Search Scope property of objCommand to a value of 2 (see Const)

objCommand.CommandText = "select Name from Ldap://dc=josephstevens,dc=com where objectcatagory='user'"
'The Query

Set objRecordSet = objCommand.Execute
'Execute the command
'Condensed version:
Do while not objRecordSet.EOF
  wscript.echo objRecordSet.Fields("Name").Value
  objRecordSet.MoveNext
Loop
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18839383
The command needs to be formatted as: (enclosed in apostrophe-quotes)

objCommand.CommandText = "select Name from 'Ldap://dc=josephstevens,dc=com' where objectcatagory='user'"
0
 
LVL 1

Author Comment

by:jsctechy
ID: 18842634
Sir,
You have put me onto the right path here.  I will work with my DBA on this and eventually post back.
Thanks for the help.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 18842637
Glad I could help. Have fun!
Thanx for the grade!! :^)
0

Featured Post

IT, Stop Being Called Into Every Meeting

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

Recently I have been answering a lot of questions like this in IT forums that I frequent. The question posed is usually something along the lines of "We have software X installed and need to uninstall it for reason Y" or some other variant of the sa…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 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

22 Experts available now in Live!

Get 1:1 Help Now