Solved

Help with this code?

Posted on 2004-10-27
1,246 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
Question by:sirbounty
    46 Comments
     
    LVL 11

    Expert Comment

    by:huntersvcs
    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
    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
    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
     
    LVL 67

    Author Comment

    by:sirbounty
    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:
    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
    pdrau (or anyone) - my proxy blocks that site (grrr) - can anyone post the snippet here?
    0
     
    LVL 67

    Author Comment

    by:sirbounty
    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
    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
    thanks for posting that guid...I'm just getting rolling this morning...uggh!
    0
     
    LVL 67

    Author Comment

    by:sirbounty
    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
    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
    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
    oops... misread your comment, ignore last post. (let me think about this a little)
    0
     
    LVL 12

    Expert Comment

    by:guidway
    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
    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
    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
    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
    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
    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
    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
    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
    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
    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
    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
    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
    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
    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
    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
    No, it didn't print the file...is it supposed to?
    0
     
    LVL 11

    Expert Comment

    by:pratap_r
    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
    Oops - nope didn't get that far yet.  I'll use that now... (sorry)
    0
     
    LVL 67

    Author Comment

    by:sirbounty
    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
    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
    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
    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
    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
    don't know it off-hand, let me do a little research though.
    0
     
    LVL 12

    Expert Comment

    by:guidway
    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
    I can probably next week...busy myself this week... :(
    Thanx for all your help everyone! : )
    0
     
    LVL 11

    Expert Comment

    by:pratap_r
    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
    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
    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
    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
    Wow - thanx for that...
    I'll give it a shot sometime this week. : )
    0
     
    LVL 67

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Suggested Solutions

    Title # Comments Views Activity
    bigDiff challenge 17 45
    centeredAverage challenge 8 63
    no14 challenge 14 40
     shows up in Outlook, not OWA or on phone 3 29
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    This is about my first experience with programming Arduino.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

    913 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

    17 Experts available now in Live!

    Get 1:1 Help Now