Help with this code?

Thanx to guidway in http:Q_21175457.html
I am using the following to pull mdb information on my servers:

Dim FSO, oFil, s2, objArgs, objItem
Dim computerName, owner
Dim objFileSystem, objOutputFile
Dim strOutputFile

Set FSO = CreateObject("Scripting.FileSystemObject")

  strComputer = "."
  Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
  Set objNetwork = Wscript.CreateObject("Wscript.Network")
  computerName = objNetwork.ComputerName
   strOutputFile = "./" & computerName & "-db.xls"
   Set objFileSystem = CreateObject("Scripting.fileSystemObject")
   Set objOutputFile = objFileSystem.CreateTextFile(strOutputFile, TRUE)
   Set f = fso.GetFolder("G:\Groups")  
   Call SearchAll(f)

Sub SearchFolder(byRef objFolder)
    Dim objFile, objSubFolder
   
  ' Loop through the files and check each one.  
    if not objFolder.name = "System Volume Information" then
       For Each objFile in objFolder.Files
          Call SearchFile(objFile)    
       next'objFile  
    end if
  ' Loop through the sub folders and call self on each folder.  
    if not objFolder.name = "System Volume Information" then
       For Each objSubFolder in objFolder.SubFolders
         Call SearchFolder(objSubFolder)
       Next'objSubFolder  
    end if
End Sub

Sub SearchAll(byVal strFolder)
    dim objFSO, objFolder
   
  ' Setup the FSO Object  
    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
  ' Get the root folder  
    Set objFolder = objFSO.GetFolder(strFolder)
    objOutputFile.WriteLine("FILE NAME, FILE PATH, FILE SIZE, CREATED, ACCESSED, OWNER")  
    Call SearchFolder(objFolder)
End Sub

Sub SearchFile(byVal oFil)
  'wscript.echo oFil.Path
  if right(oFil.name, 4) = ".mdb" then  
     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
     For Each objItem in colItems
         owner = objItem.AccountName
     Next
         s2 = ""
         s2 = oFil.Name & ","
         s2 = s2 & oFil.Path & ","
         s2 = s2 & oFil.Size & ","
         s2 = s2 & left(oFil.DateCreated,10) & ","

         s2 = s2 & left(oFil.DateLastAccessed,10) & ","
'         s2 = s2 & "Type: " & oFil.Type & ","
         s2 = s2 &  owner
         objOutputFile.WriteLine(s2)
  end if
End Sub

Now, I need help with the following items:

1) I need the code modified slightly to 'check' for the appropriate drives/paths.
We have a mixture of w2k and w2k3 servers.  On the w2k servers, there may be just a G:\Groups_1 folder that I need scanned, or, if the office is large enough, an H:\Groups_2 might exist.  On our W2k3 servers the folder is simply renamed to G:\Groups.  So, I'd like to either go through all 3 instances, or simply determine if it's a w2k3 and only do groups or a w2k and do groups_1 and groups_2, if it exists.

2) I don't know VBS that well, and this code worked great on my laptop, but running it on my server generates an apparent error:

sdbfa.vbs(51, 6) (null): 0x8004103A

Now, maybe that means something to someone here, but it does nothing for me.  My suspicion is it's on an improperly named subfolder somewhere, cause I had this problem in my dos-batch file as well.  I found at least two folders that contained "&" in the name (concatenate), so I suspect that's it, since the code runs for a while before halting here.  Can anyone assist?

Thanx!
LVL 67
sirbountyAsked:
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.

huntersvcsCommented:
Can you give a brief description of what (exactly) you are trying to do?  The Script looks OK (at first glance), but we can't find errors without more information.

If your suspicions are correct, and it the subfolder names, try using " before and after the name, but without & in between.  The symbol & usually means connecting two separate values.  For example:  "Still" & "Born" gives a value of "StillBorn".

Hope this helps.
Rick
0
pdrauCommented:
If objFSO.FolderExists("G:\Groups\") Then

<do processing for G:\Groups\>

else
If objFSO.FolderExists("H:\Groups_2\") Then

<do processing for H:\Groups_2>

else
If objFSO.FolderExists("G:\Groups_1\") Then

<do processing for G:\Groups_1>

End if

You could get more fancy with it I'm sure by putting them in an array and looping through your code that way, but this is quick.

That should give you a starting point on 1.  I don't know what the 2. error is.


0
guidwayCommented:
hey sb,

on number 2:
right here:
Sub SearchFolder(byRef objFolder)
    Dim objFile, objSubFolder
   
  ' Loop through the files and check each one.  
    if not objFolder.name = "System Volume Information" then
       For Each objFile in objFolder.Files
          Call SearchFile(objFile)    
       next'objFile  
    end if
  ' Loop through the sub folders and call self on each folder.  
    if not objFolder.name = "System Volume Information" then
       For Each objSubFolder in objFolder.SubFolders
         Call SearchFolder(objSubFolder)
       Next'objSubFolder  
    end if
End Sub

set up a statement to write out the directories that are accessed into your output file, like this:
    if not objFolder.name = "System Volume Information" then
       objOutputFile.WriteLine(objFolder.Path) '<-- right here
       For Each objSubFolder in objFolder.SubFolders
         Call SearchFolder(objSubFolder)
       Next'objSubFolder  
    end if

now run your script and when it crashes, check your log file to see what the last directory was that was written out (I had this problem when it hit the System Volume information file, which is why you see it check it above).

hope this helps
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sirbountyAuthor Commented:
Thanx guidway - I'll have a peek at it tomorrow when I return to the office.

@pdrau - looks like that'll do it.  Can you use Select case statements in vbs?
If there was a way to determine the OS, then I could perform some case statements.
Select Case OS.Version
  Case WindowsNT.2000
         if exist h:\groups_2
            set Folder=h:\groups_2
            call group_processing (Folder)
         end if
         Set folder=G:\groups_1
  Case WindowsNT.2003
         Set folder=G:\Groups
End select
Call Group_Processing (Folder)
   
@huntersvcs
The gist of it is - search our server's group shares (locally) recursively through all subfolders for MDB files found.  Gather File name, path, size, creation date, last accessed date and owner information - output to a csv file...

Hope that helps explain it a bit more.  Thanx all!
0
pdrauCommented:
yes, you can use select case

I found this site that has a vbscript to check for OS version.  You should be able to pull out the code snippets you need from it.

http://cwashington.netreach.net/depo/view.asp?Index=868&ScriptType=vbscript

You should be good to go now.  Good luck!

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
sirbountyAuthor Commented:
pdrau (or anyone) - my proxy blocks that site (grrr) - can anyone post the snippet here?
0
sirbountyAuthor Commented:
guidway...last entry in the log:

G:\Groups\CORPORATE\ATLFINANCL\!!Save_Florida_2003

Perhaps it's the "!" character?

And I won't have to check sys vol - since I'm only checking the subfolder...not the root.
0
guidwayCommented:
here is the script pdrau mentioned (pts of course go to him if it helps):

'Dim WSHShell
Dim sComputerName, sServer, sPatchPath, sSwitches, sSwitchesNT
Dim sServerName, sHotfix
Dim Command, objArgs

'If any glitches, script will resume by telling you that there was a problem
On Error Resume Next

Set objArgs = WScript.Arguments
If objArgs.Count = 1 Then
    'take the name after the command as the server name argument to pull the
    'patch from
    sServerName = objArgs(0)
ElseIf objArgs.Count = 0 Then
    'if no arguments are given at the command line, then set the server name
    'here as a default
    sServerName = "server"
End If

Set WSHShell = WScript.CreateObject("WScript.Shell")

    'change MS Q number below to check for different hotfix installs
    sHotfix = "KB824146"
    'Get local computer name to figure OS version below
    sComputerName = WSHShell.ExpandEnvironmentStrings("%COMPUTERNAME%")

    'share and path after servername in which the patch resides
    sPatchPath = "\patches\rpcpatch"
    'Patch command exe after path - we renamed ours to something a bit shorter
    sWin2k = "\rpcw2k0910.exe"
    sWinXp = "\rpcwxp0910.exe"
    sWinNTWrk = "\rpcwntwrk0910.exe"
    sWinNTsrv = "\rpcwntsrv0910.exe"
    'command line switches for the patch
    sSwitches = " /f /u"
    'switches for NT
    sSwitchesNT = " -f -m"
   
Set objWMIService = GetObject("winmgmts:\\" & sComputerName & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem",,48)
For Each objItem In colItems
    'wscript.echo objItem.Caption
    OSVer = objitem.caption
Next

If InStr(1, OSVer, "2000") > 0 Then 'search for win2k reg key
    bKey = WSHShell.RegRead("HKLM\SOFTWARE\Microsoft\Updates\Windows 2000\SP5\"_
    & sHotfix & "\Description")
    'Set the command line - format: "\\server\share\path\hotfix.exe" /switches
    sCmd = Chr(34) & "\\" & sServerName & sPatchPath & sWin2k & Chr(34) & sSwitches
ElseIf Instr(1, OSVer, "XP") >0 Then 'search for winXP reg key
    'if the OS is XP with SP1 installed, then run following
    bKey = WSHShell.RegRead("HKLM\SOFTWARE\Microsoft\Updates\Windows XP\SP2\"_
    & sHotfix & "\Description")
    If bKey = "" Then 'if XP system does NOT have SP1 installed yet
        bKey = WSHShell.RegRead("HKLM\SOFTWARE\Microsoft\Updates\Windows XP\"_
        & "SP1\" & sHotfix & "\Description")
    End If
    'Set the command line
    sCmd = chr(34) & "\\" & sServerName & sPatchPath & sWinXP & Chr(34) & sSwitches
ElseIf Instr(1, OSVer, "NT") >0 Then 'search for NT40 reg key
    bKey = WSHShell.RegRead("HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\"_
    & "Hotfix\" & sHotfix & "\Hotfix Description")
    If Instr(1, OSVer, "Workstation") >0 Then 'set command line NT4 workstation
        'set the command
        sCmd = chr(34) & " \\" & sServerName & sPatchPath & sWinNTwrk & ""_
        & Chr(34) & sSwitchesNT
    'Only other option would be if OS is NT _Server_, then set command for server
    Else
        'set the command line
        sCmd = chr(34) & "\\" & sServerName & sPatchPath & sWinNTsrv & ""_
        & Chr(34) & sSwitchesNT
    End If
End If

as far as the problem in #2. can you try renaming that directory (temporarily) then re-running the script and see if it fails in that same directory again? If so, then we can rule out a directory problem. If it crashes on another directory (or completes) we will know that was the problem. By the way, is that some form of Backup directory created by windows or just one you created? I'm assuming you created it so it shouldn't have its System property set, but thought I better ask anyway. The script doesn't seem to like directories that are System directories.

good luck
guid
0
pdrauCommented:
thanks for posting that guid...I'm just getting rolling this morning...uggh!
0
sirbountyAuthor Commented:
Fyi - this is 'after' it processes a folder in that same directory !!!!MEU Pricing folder
So maybe it's not the "!"...

I'll see if I can rename it - and check it's attributes.  These are folders the customers create...
0
sirbountyAuthor Commented:
This time it's stopping at G:\Groups\CORPORATE\ATLFINANCL\!!!!MEU Pricing folder\Williams
: (

No attributes on any of the folders...
0
guidwayCommented:
so it appears to be it is with folders with exclamation points then, right? since when you renamed the other one it moved to a different folder. What is the likelihood the folders with exclamation points will have mdb files inside of them? If none, we could set up an if statement to block any folder that begins with an exclamation out...

Also, I'm assuming you're administrator so you should have access permissions to all the folders... correct?
0
guidwayCommented:
oops... misread your comment, ignore last post. (let me think about this a little)
0
guidwayCommented:
The only thing I can think of off-hand is to check for strange file names first in the directories it is failing in. I'll keep thinking...
0
sirbountyAuthor Commented:
Actually - the last one posted comes 'before' the first failure... :(
So, I'm not sure that the problem is - I noticed the file name on one of the failures (forget which one) was simply hack.mdb
Yes, they'll be mdb's in these folders (this is one server out of about 40 too, so ... blah)

Yes, I'm a domain admin...

Thanx thus far sirs!
0
guidwayCommented:
is there a possibility someone else (on a workstation) is currently using that folder and the script can't read it while it is in use? just curious...
0
sirbountyAuthor Commented:
If that were the case, I would've though I couldn't have renamed it... :(
Still, I suppose it's possible...But my 'dos' version works fine...
0
sirbountyAuthor Commented:
And it's not a big deal if we can't get this working - I was just more curious 'how' to get it working that way and which would prove faster.  
I'll give it another week and then close this out if we haven't discovered the problem.  I'll also try first thing in the morning before anyone comes in...  Thanx for everyone's help! :D
0
guidwayCommented:
one other idea we could try:

print the file paths themselves instead of the directory paths

first comment out the outputfile statement I told you to add before.

Instead, in the SearchFiles subroutine:

add the line:
objOutputFile.WriteLine(oFil.path)

at the first line of the subroutine.

this should print out all files on the computer (fair warning: this may slow down the server since there are probably millions of files).
0
pratap_rCommented:
hey sirbounty

about the second error.. its failing because the security permissions cannot be retrived by the wmi call..

  if right(oFil.name, 4) = ".mdb" then  
     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
     For Each objItem in colItems '<<<<--------------fails here
         owner = objItem.AccountName
     Next

fails because colItems is nothing or null, which means the file name is corrupted in the wmi query... you have a ' (single quote) ( an exclamation ! should work fine) in some file or directory... escape it and it should work fine.

Have fun,
Pratap
0
sirbountyAuthor Commented:
Ok - I'll try that.
My dos script doesn't seem to pull the owner info 100% of the time, so I'm now hoping we can get this working if it can do any more with that information...
I'll let you know.  Thanx
0
sirbountyAuthor Commented:
Can I test for null simply with an if statement?

if colitems not null
or
if not isnull(colitems)
??
0
sirbountyAuthor Commented:
Hmm - guidway, the last folder in the output doesn't even contain an mdb...just some MVC-????.jpg files...
0
sirbountyAuthor Commented:
Okay, now I've got

If not isnull(colitems) then
 for each...
   owner=
 else
  owner="None Found"
 end if

Runs for a bit then 52,2 (null) same hex
0
pratap_rCommented:
hey try this.. you will get a more detailed error.. will help us solve the problem

     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner",,wbemFlagReturnWhenComplete)

Pratap
0
pratap_rCommented:
and isnull will not help there..

probably you should try trapping the error using on error resume next and then checking if err.number is > 0

eg,
on error resume next
     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner",,wbemFlagReturnWhenComplete)
if err.number > 0 then
WScript.Echo "Error accessing info for file : " & oFil.path
Exit Sub
end if
For Each objItem in colItems '<<<<--------------fails here
     owner = objItem.AccountName
Next
0
sirbountyAuthor Commented:
pratap - now I get 50,6: SWbemServicesEx: Invalid object path
Last entry is !!Save_Florida_2003 again..
0
pratap_rCommented:
ah.. just as i thought.. did it print the line "Error accessing info for file : " and the file name?? that is the file that has the problem.. !!Save_Florida... was successfull its the next file after that that is creating the problem..

Pratap
0
sirbountyAuthor Commented:
No, it didn't print the file...is it supposed to?
0
pratap_rCommented:
did you use this code?

on error resume next '<--dont forget this
     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner",,wbemFlagReturnWhenComplete)
if err.number > 0 then
WScript.Echo "Error accessing info for file : " & oFil.path '<<<---- this line should print the file
Exit Sub
end if
For Each objItem in colItems '<<<<--------------fails here
     owner = objItem.AccountName
Next
0
sirbountyAuthor Commented:
Oops - nope didn't get that far yet.  I'll use that now... (sorry)
0
sirbountyAuthor Commented:
Nothing.  Still just the stupid !!Save_Florida_2003 line...
Prior to that is an actual output line (that goes in my log file) that has the csv info.
Hack.mdb, G:\path with florida\hack.mdb, 142942208, 02/11/2003,10/292/004, EmployeeID

And prior to that I have path prints of a !!!!MEU Pricing folder subfolder...
0
pratap_rCommented:
you are executing the it from the command prompt right? using cscript? din't you see the command line error message? the log file (csv) will not give you the file which has the problem, it will mention the last file which was successfull...

Pratap
0
sirbountyAuthor Commented:
Yep - using cscript.
No other output on the screen and that's all that's in the log file...
Maybe I should post that routine just to be sure...give me a bit.
0
guidwayCommented:
sorry for getting back so late. I went to Pensacola for the weekend and just got back. I'll response tomorrow (once I get some time to get back into everything).
0
pratap_rCommented:
hey guidway.. i figured that the call to Win32_LogicalFileSecuritySetting fails probably because of a ' in the search string (i.e, in the folder/file name). do you know how to escape this sequence? the normal '' we use for sql doesnt work, so does \' !!!! i tried simulating sirbounty's problem on my machine.. although i can reproduce the problem, i am not able to find the escape seq for ' :(
0
guidwayCommented:
don't know it off-hand, let me do a little research though.
0
guidwayCommented:
the only thing I could find is try replacing the single quote with chr(39) in the query. From what I read on the internet this is how others get past this problem in VB. I'm not completely sure it will work in VBS but worth a shot. I don't have time to write the code out to do this (heading for a meeting) so maybe someone can play around with that.
0
sirbountyAuthor Commented:
I can probably next week...busy myself this week... :(
Thanx for all your help everyone! : )
0
pratap_rCommented:
well i tried escaping the code with a ' and also with the standard vb escape conventions.. no use.. it works for the rest of the query part except for the Win32_LogicalFileSecuritySetting parameter.. :( hmmm.. still trying..

Pratap
0
sirbountyAuthor Commented:
Oh well - I just wanted to see if it were possible...already accomplished it with a batch, so enjoy the points. : )
0
guidwayCommented:
tried it again and couldn't get it to work either. sorry, although I'm glad the batch worked ok. :)
0
jackallanCommented:
Hi,

I had the exact same problem and eventually found the solution.  Instead of using apostrophies (') around your path value use quotes (").  Also you should be escaping any backslashes with a double slash.  

Here is an example:

  if right(oFil.name, 4) = ".mdb" then  
     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting=""" & Replace(oFil.path, "\", "\\") & """}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
     For Each objItem in colItems

Yay a solution!
0
sirbountyAuthor Commented:
Wow - thanx for that...
I'll give it a shot sometime this week. : )
0
sirbountyAuthor Commented:
I'm going back to the VBS solution, so I'd like to get this working.
See http:Q_21283471.html
I need Last Access date in my script along with the other columns.
If you'd like to assist, please drop by... : )
Thanx
0
sirbountyAuthor Commented:
jackallan that works!

Tho, I'm not sure why...I've been checking on this today and found more info about escaping the ' with a preceding \

I've tried it all different ways, but couldn't get it working...now it does!

Please post a comment in http://www.experts-exchange.com/Programming/Q_21289573.html so that I can give you points for this.
Thanx!!!
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
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.