Link to home
Start Free TrialLog in
Avatar of sirbounty
sirbountyFlag for United States of America

asked on

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!
Avatar of huntersvcs
huntersvcs
Flag of Germany image

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
Avatar of pdrau
pdrau

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.


SOLUTION
Avatar of guidway
guidway
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sirbounty

ASKER

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!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
pdrau (or anyone) - my proxy blocks that site (grrr) - can anyone post the snippet here?
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for posting that guid...I'm just getting rolling this morning...uggh!
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...
This time it's stopping at G:\Groups\CORPORATE\ATLFINANCL\!!!!MEU Pricing folder\Williams
: (

No attributes on any of the folders...
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?
oops... misread your comment, ignore last post. (let me think about this a little)
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...
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!
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...
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...
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
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).
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
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
Can I test for null simply with an if statement?

if colitems not null
or
if not isnull(colitems)
??
Hmm - guidway, the last folder in the output doesn't even contain an mdb...just some MVC-????.jpg files...
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
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
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
pratap - now I get 50,6: SWbemServicesEx: Invalid object path
Last entry is !!Save_Florida_2003 again..
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
No, it didn't print the file...is it supposed to?
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
Oops - nope didn't get that far yet.  I'll use that now... (sorry)
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...
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
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.
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).
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 ' :(
don't know it off-hand, let me do a little research though.
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.
I can probably next week...busy myself this week... :(
Thanx for all your help everyone! : )
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
Oh well - I just wanted to see if it were possible...already accomplished it with a batch, so enjoy the points. : )
tried it again and couldn't get it to work either. sorry, although I'm glad the batch worked ok. :)
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!
Wow - thanx for that...
I'll give it a shot sometime this week. : )
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
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 https://www.experts-exchange.com/questions/21289573/Trouble-with-script.html so that I can give you points for this.
Thanx!!!