Can this be done with VB Scripts?

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.
LVL 1
jsctechyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jsctechyAuthor Commented:
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
sirbountyCommented:
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
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

jsctechyAuthor Commented:
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
jsctechyAuthor Commented:
Sir,
Why do I need the following?
Const ADS_SCOPE_SUBTREE = 2

0
sirbountyCommented:
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
jsctechyAuthor Commented:
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
sirbountyCommented:
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
jsctechyAuthor Commented:
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
sirbountyCommented:
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
jsctechyAuthor Commented:
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
sirbountyCommented:
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
jsctechyAuthor Commented:
hey-
Thanks, I got an error, like 25 chr 1
error:  'loop' without 'do'
0
sirbountyCommented:
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
jsctechyAuthor Commented:
Set objConnection.ActiveConnection = objConnection

Error - Object doesn't support named arguments: 'objConnection.ActiveConnection'
0
sirbountyCommented:
That should be the command...
Set objCommand.ActiveConnection = objConnection
0
jsctechyAuthor Commented:
oh, had 'set objConnection.ActiveConnection = objConnection'
0
jsctechyAuthor Commented:
Do while not objRecordSet.EOF

Error: Object Required; 'objRecordSet'
0
sirbountyCommented:
Crucial step missed...(see, told you the recordset was stingy)

Set objRecordSet = objCommand.Execute 'have to execute it..
0
sirbountyCommented:
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
jsctechyAuthor Commented:
Sir,
Thanks- I just saw that as well.
0
sirbountyCommented:
No worries. ;)
0
jsctechyAuthor Commented:
AH!  Another problem-
One or more errors occured during processing of command.
Code: 8000E14
Source: Provider
0
jsctechyAuthor Commented:
Do I need to do this?

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

0
sirbountyCommented:
Why don't you post the script as-is right now...
0
jsctechyAuthor Commented:
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
sirbountyCommented:
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
jsctechyAuthor Commented:
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
sirbountyCommented:
Glad I could help. Have fun!
Thanx for the grade!! :^)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.