sirbounty
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.Fi leSystemOb ject")
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=imper sonate}!\\ " & strComputer & "\root\cimv2")
Set objNetwork = Wscript.CreateObject("Wscr ipt.Networ k")
computerName = objNetwork.ComputerName
strOutputFile = "./" & computerName & "-db.xls"
Set objFileSystem = CreateObject("Scripting.fi leSystemOb ject")
Set objOutputFile = objFileSystem.CreateTextFi le(strOutp utFile, 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.Fi leSystemOb ject")
' Get the root folder
Set objFolder = objFSO.GetFolder(strFolder )
objOutputFile.WriteLine("F ILE 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_LogicalFileSecurity Setting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFi leOwner 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!
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.Fi
strComputer = "."
Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=imper
Set objNetwork = Wscript.CreateObject("Wscr
computerName = objNetwork.ComputerName
strOutputFile = "./" & computerName & "-db.xls"
Set objFileSystem = CreateObject("Scripting.fi
Set objOutputFile = objFileSystem.CreateTextFi
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.Fi
' Get the root folder
Set objFolder = objFSO.GetFolder(strFolder
objOutputFile.WriteLine("F
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_LogicalFileSecurity
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
' 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!
If objFSO.FolderExists("G:\Gr oups\") Then
<do processing for G:\Groups\>
else
If objFSO.FolderExists("H:\Gr oups_2\") Then
<do processing for H:\Groups_2>
else
If objFSO.FolderExists("G:\Gr oups_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.
<do processing for G:\Groups\>
else
If objFSO.FolderExists("H:\Gr
<do processing for H:\Groups_2>
else
If objFSO.FolderExists("G:\Gr
<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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
pdrau (or anyone) - my proxy blocks that site (grrr) - can anyone post the snippet here?
ASKER
guidway...last entry in the log:
G:\Groups\CORPORATE\ATLFIN ANCL\!!Sav e_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.
G:\Groups\CORPORATE\ATLFIN
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thanks for posting that guid...I'm just getting rolling this morning...uggh!
ASKER
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...
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...
ASKER
This time it's stopping at G:\Groups\CORPORATE\ATLFIN ANCL\!!!!M EU Pricing folder\Williams
: (
No attributes on any of the folders...
: (
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?
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...
ASKER
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!
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...
ASKER
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...
Still, I suppose it's possible...But my 'dos' version works fine...
ASKER
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
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(oF il.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).
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(oF
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_LogicalFileSecurity Setting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFi leOwner 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
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_LogicalFileSecurity
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
ASKER
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
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
ASKER
Can I test for null simply with an if statement?
if colitems not null
or
if not isnull(colitems)
??
if colitems not null
or
if not isnull(colitems)
??
ASKER
Hmm - guidway, the last folder in the output doesn't even contain an mdb...just some MVC-????.jpg files...
ASKER
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
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_LogicalFileSecurity Setting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFi leOwner ResultRole=Owner",,wbemFla gReturnWhe nComplete)
Pratap
Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecurity
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_LogicalFileSecurity Setting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFi leOwner ResultRole=Owner",,wbemFla gReturnWhe nComplete)
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
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_LogicalFileSecurity
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
ASKER
pratap - now I get 50,6: SWbemServicesEx: Invalid object path
Last entry is !!Save_Florida_2003 again..
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
Pratap
ASKER
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_LogicalFileSecurity Setting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFi leOwner ResultRole=Owner",,wbemFla gReturnWhe nComplete)
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
on error resume next '<--dont forget this
Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecurity
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
ASKER
Oops - nope didn't get that far yet. I'll use that now... (sorry)
ASKER
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...
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
Pratap
ASKER
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.
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_LogicalFileSecurityS etting 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.
ASKER
I can probably next week...busy myself this week... :(
Thanx for all your help everyone! : )
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_LogicalFileSecurityS etting parameter.. :( hmmm.. still trying..
Pratap
Pratap
ASKER
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_LogicalFileSecurity Setting="" " & Replace(oFil.path, "\", "\\") & """}" & " WHERE AssocClass=Win32_LogicalFi leOwner ResultRole=Owner")
For Each objItem in colItems
Yay a solution!
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_LogicalFileSecurity
For Each objItem in colItems
Yay a solution!
ASKER
Wow - thanx for that...
I'll give it a shot sometime this week. : )
I'll give it a shot sometime this week. : )
ASKER
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
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
ASKER
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!!!
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!!!
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