Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with this code?

Posted on 2004-10-27
46
Medium Priority
?
1,269 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:sirbounty
[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
  • 22
  • 11
  • 8
  • +3
46 Comments
 
LVL 11

Expert Comment

by:huntersvcs
ID: 12425576
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
 
LVL 2

Expert Comment

by:pdrau
ID: 12425817
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
 
LVL 12

Assisted Solution

by:guidway
guidway earned 1000 total points
ID: 12426432
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 67

Author Comment

by:sirbounty
ID: 12427663
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
 
LVL 2

Accepted Solution

by:
pdrau earned 600 total points
ID: 12429535
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12432746
pdrau (or anyone) - my proxy blocks that site (grrr) - can anyone post the snippet here?
0
 
LVL 67

Author Comment

by:sirbounty
ID: 12432792
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
 
LVL 12

Assisted Solution

by:guidway
guidway earned 1000 total points
ID: 12433407
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
 
LVL 2

Expert Comment

by:pdrau
ID: 12433845
thanks for posting that guid...I'm just getting rolling this morning...uggh!
0
 
LVL 67

Author Comment

by:sirbounty
ID: 12434032
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12434046
This time it's stopping at G:\Groups\CORPORATE\ATLFINANCL\!!!!MEU Pricing folder\Williams
: (

No attributes on any of the folders...
0
 
LVL 12

Expert Comment

by:guidway
ID: 12434307
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
 
LVL 12

Expert Comment

by:guidway
ID: 12434319
oops... misread your comment, ignore last post. (let me think about this a little)
0
 
LVL 12

Expert Comment

by:guidway
ID: 12434342
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12435755
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
 
LVL 12

Expert Comment

by:guidway
ID: 12435916
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12440036
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12440041
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
 
LVL 12

Expert Comment

by:guidway
ID: 12440722
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12444974
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12444980
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12445031
Can I test for null simply with an if statement?

if colitems not null
or
if not isnull(colitems)
??
0
 
LVL 67

Author Comment

by:sirbounty
ID: 12445074
Hmm - guidway, the last folder in the output doesn't even contain an mdb...just some MVC-????.jpg files...
0
 
LVL 67

Author Comment

by:sirbounty
ID: 12445142
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12445232
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12445323
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12446099
pratap - now I get 50,6: SWbemServicesEx: Invalid object path
Last entry is !!Save_Florida_2003 again..
0
 
LVL 11

Expert Comment

by:pratap_r
ID: 12447039
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12447118
No, it didn't print the file...is it supposed to?
0
 
LVL 11

Expert Comment

by:pratap_r
ID: 12447165
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12447202
Oops - nope didn't get that far yet.  I'll use that now... (sorry)
0
 
LVL 67

Author Comment

by:sirbounty
ID: 12447251
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12447386
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12448404
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
 
LVL 12

Expert Comment

by:guidway
ID: 12467246
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
 
LVL 11

Expert Comment

by:pratap_r
ID: 12470204
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
 
LVL 12

Expert Comment

by:guidway
ID: 12494859
don't know it off-hand, let me do a little research though.
0
 
LVL 12

Expert Comment

by:guidway
ID: 12495500
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12495958
I can probably next week...busy myself this week... :(
Thanx for all your help everyone! : )
0
 
LVL 11

Expert Comment

by:pratap_r
ID: 12496084
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
 
LVL 67

Author Comment

by:sirbounty
ID: 12604570
Oh well - I just wanted to see if it were possible...already accomplished it with a batch, so enjoy the points. : )
0
 
LVL 12

Expert Comment

by:guidway
ID: 12606501
tried it again and couldn't get it to work either. sorry, although I'm glad the batch worked ok. :)
0
 
LVL 2

Expert Comment

by:jackallan
ID: 13059847
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
 
LVL 67

Author Comment

by:sirbounty
ID: 13059899
Wow - thanx for that...
I'll give it a shot sometime this week. : )
0
 
LVL 67

Author Comment

by:sirbounty
ID: 13108529
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
 
LVL 67

Author Comment

by:sirbounty
ID: 13149355
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A short article about problems I had with the new location API and permissions in Marshmallow
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

597 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