Programmatically pull owner/author, date created & date last accessed

Willing to do this in either a DOS script, VB, VBS or WMI (although not very familiar with the latter).

I'm creating a script (with your help) to pull the info in the subject on each MDB file found on my servers.

Currently, thru dos, I have the following, but it appears to be limited to date modified (or more accurately date created) only:

@echo off

:start
if "%srv%"=="" set srv=%computername:~0,7%
if exist e:\batch\%srv%-mdb.lst erase e:\batch\%srv%-mdb.lst
for /r g:\ %%I in (*.mdb) do echo "\\%srv%\g$%%~pnxtI" >> e:\batch\%srv%-mdb.lst

Can anyone help me rewrite this or utilize another prog lang to accomplish what I need?

Thanx!
LVL 67
sirbountyAsked:
Who is Participating?
 
guidwayConnect With a Mentor Commented:
first, this code is not the cleanest or prettiest, but it runs and works for me so hopefully you say the same. :) Copy everything into a .vbs file and it should search your entire C: drive for all .mdb files and then write the owner/file information into a text file (similar to your batch script). You probably will want to reformat the information that is printed out into whatever format you want to display it in. try this:

'----------------
'start code
'----------------

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
  wscript.echo computerName
   strOutputFile = "./" & computerName & "-db.lst"
   Set objFileSystem = CreateObject("Scripting.fileSystemObject")
   Set objOutputFile = objFileSystem.CreateTextFile(strOutputFile, TRUE)
   Set f = fso.GetFolder("C:\")  
   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)
   
    Call SearchFolder(objFolder)
End Sub

Sub SearchFile(byVal oFil)
  'wscript.echo oFil.Path
  if right(oFil.name, 4) = ".mdb" then  
     objOutputFile.WriteLine(oFil.path)
     Set colItems = objWMIService.ExecQuery ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & oFil.path & "'}" & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
     For Each objItem in colItems
         owner = objItem.AccountName & VBCrLf
     Next
         s2 = ""
         s2 = "Attributes: " & oFil.Attributes & VBCrLf
         s2 = s2 & "DateCreated: " & oFil.DateCreated & VBCrLf
         s2 = s2 & "DateLastAccessed: " & oFil.DateLastAccessed & VBCrLf
         s2 = s2 & "DateLastModified: " & oFil.DateLastModified & VBCrLf
         s2 = s2 & "Name: " & oFil.Name & VBCrLf
         s2 = s2 & "Size: " & oFil.Size & " bytes" & VBCrLf
         s2 = s2 & "Path: " & oFil.Path & VBCrLf
         s2 = s2 & "Type: " & oFil.Type & VBCrLf
         s2 = s2 & "Owner: " & owner & VBCrLf
         objOutputFile.WriteLine(s2)
  end if
End Sub


'----------------
'end code
'----------------
0
 
kemp_aCommented:
Not sure what you really require here!

Your script so far echo's the path, filename, extension, date/dime (probably created date/time) of every '.MDB' file on the g:\ drive on the computer it's run into a file called e:\batch\%whatever%-mdb.lst. I just run that on my system & found all the .mdb's on my system. So it appears to work.

What is it that you want to achieve?

0
 
sirbountyAuthor Commented:
I also would like the owner and last access date (don't think I can get it via dos)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
guidwayCommented:
this command should extract the accessed date:

dir /t a %1

*where %1 is the current file passed in

not sure if this is what you are looking for though...
0
 
guidwayCommented:
whoops, that should be:
dir /T:A %1

(this works on NT, not sure if this argument existed in 95 or 98 as I can't test them right now)
0
 
sirbountyAuthor Commented:
Sorry - XP client and 2k/2k3 backend.
Sounds good for the one.  I think I can pull the owner via wsh.
Testing...
0
 
sirbountyAuthor Commented:
Hmm - I think I'd rather do this in wsh, otherwise I feel like I'm accessing the file 2 or 3 times to get the info I need.
Any wsh pros here?
0
 
guidwayCommented:
>>Any wsh pros here?<<

nope, sorry. I did find a batch script to extract the owner information though... although (as you already said) it is requires another access to the file therefore it probably won't help. Here it is though:

http://www.robvanderwoude.com/files/owner.txt
0
 
sirbountyAuthor Commented:
Found this - I think I can work with it to get most of what I need...

Dim FSO, oFil, s2, Arg
  Set FSO = CreateObject("Scripting.FileSystemObject")
   Set oFil = FSO.GetFile(Arg)
      s2 = "Attributes: " & oFil.Attributes & VBCrLf
      s2 = s2 & "DateCreated: " & oFil.DateCreated & VBCrLf
      s2 = s2 & "DateLastAccessed: " & oFil.DateLastAccessed & VBCrLf
      s2 = s2 & "DateLastModified: " & oFil.DateLastModified & VBCrLf
      s2 = s2 & "Name: " & oFil.Name & VBCrLf
      s2 = s2 & "Size: " & oFil.Size & " bytes" & VBCrLf
      s2 = s2 & "Path: " & oFil.Path & VBCrLf
      s2 = s2 & "Type: " & oFil.Type & VBCrLf
      s2 = s2 & "ShortName: " & oFil.ShortName & VBCrLf
      s2 = s2 & "ParentFolder: " & oFil.ParentFolder & VBCrLf & VBCrLf
   
       MsgBox s2
     
    Set oFil = Nothing    
    Set FSO = Nothing

Just looking for a 'owner' property or equivalent...
0
 
sirbountyAuthor Commented:
Good info guidway...some points to you for that info, it may come in handy someday. :)
0
 
guidwayCommented:
this perhaps:

 ' NTAccess.Permissions - Ownership test ' ' (c) 1998 Zaks Solutions '  
testFile = InputBox("Enter a file or directory to test with","NTAccess.Permissions","c:\someFile.txt")
if Trim(testFile) <> "" then    
    DoTest
end if  

public sub DoTest()    
     set ntap = CreateObject("NTAccess.Permissions")    
     set perms = ntap(testFile)
     msgbox "Current Owner is " & perms.Owner & vbCRLF & "About to Take Ownership"    
end sub  
0
 
guidwayCommented:
ignore the "about to take ownership" text. I should have cut that out since I removed the very end of the script that does take ownership. :)

hope this helps
guid
0
 
guidwayCommented:
http://www.robvanderwoude.com/ is one of the best sites for batch scripts (IMO). They usually have pretty much anything you can think of somewhere in their library of scripts. They also have a few other types (Perl, VBScript, etc...).

good luck with your script!
0
 
sirbountyAuthor Commented:
Gotta love these office proxies - can't access groups.msn.com from here - but I'll take a peek at it later from home. :)
0
 
hesCommented:
Set a reference to dao in vb and try this

Private Sub Form_Load()
 Dim dbsMyDatabase As Database
 Dim prpLoop As Property
 DBEngine.SystemDB = "C:\Program Files\Microsoft Office\Office\system.mdw" 'change path as needed

  Set MyDatabase = OpenDatabase("C:\Documents and Settings\Administrator\My Documents\db3.mdb")
  For Each prpLoop In MyDatabase.Containers("Databases").Documents("SummaryInfo").Properties
      Select Case prpLoop.Name
        Case "Author"
          Text1.Text = Text1.Text & prpLoop.Name & "  " & prpLoop.Value & vbCrLf
        Case "DateCreated"
          Text1.Text = Text1.Text & prpLoop.Name & "  " & prpLoop.Value & vbCrLf
        Case "LastUpdated"
          Text1.Text = Text1.Text & prpLoop.Name & "  " & prpLoop.Value & vbCrLf
      End Select
  Next
 MyDatabase.Close
End Sub
0
 
sirbountyAuthor Commented:
hes - is that going to 'open' each db?  I don't need that...
Eddyky - still looking at this one..
0
 
hesCommented:
Yes it is that is the only place the Author is stored.
0
 
sirbountyAuthor Commented:
Oh - okay.  Looking more for the OS 'owner' info.  I'm running this against 90+ servers that may have hundreds of databases present...
0
 
sirbountyAuthor Commented:
Found a way to do it all through dos, so I'll go that route...
Some tweaking to do yet, but here's the bulk of it, if anyone's interested:

@echo off
cd\
for /r %%A in (*.mdb) do (
 for /f "tokens=1-2" %%I in ('dir /T:A "%%A" ^|find /i "%%~nxA"') do (
  set aDate=%%I
  set aTime=%%J
 )
 for /f "tokens=5" %%K in ('dir /Q "%%A" ^|find /i "%%~nxA"') do set owner=%%K
 >>c:\%computername:~0,7%-db.lst echo %%~nxA, %%~fA, %%~zA, %%~tA, %aDate%, %aTime%, %owner%
)

Can anyone duplicate this in VBS?
0
 
guidwayCommented:
sb,

give me till Monday... I'm going to try to attempt it although I'm not extremely familiar with vbs (I know VB though so it should be that bad).
0
 
guidwayCommented:
shouldn't be that bad (I meant)
0
 
sirbountyAuthor Commented:
LOL - it's bad, believe me.
I tried doing it thru a vb shell and it didn't seem to work right... :(

Good luck though.
0
 
guidwayCommented:
whoops, I left one of the wscript.echo commands in place. Please comment it out or it will prompt you with the name of your computer when it runs. :)
0
 
guidwayCommented:
btw, I tested this script and it does not work on NT machines. It will only work on XP (and I think) 2k machines (although I haven't tested it on 2k). just a head's up...
0
 
guidwayCommented:
just had a little time and tested it on 2k also. It worked fine on mine. If you have any problems or want something done differently, let me know

guid
0
 
sirbountyAuthor Commented:
Okay - the boss gave me today and tomorrow off since I worked so long last week (nice guy, eh?).
So, I'll test this on Wed.  Thanx guidway! : )
0
 
guidwayCommented:
no prob...

enjoy your days off

guid
0
 
sirbountyAuthor Commented:
Hey guidway.
I appreciate your time here, but I think I'm going to go with the DOS solution.
I don't know VBS that well either and it concerns me that I know no way of stepping through this code to make sure it's working properly.  My 'test' server is actually my local production server, so I have to be careful.  The points are yours for your time though - interesting code.  I may try running it local to my workstation to play around with it.  Thanx!
0
 
sirbountyAuthor Commented:
Ooh cool - I just ran it on my workstation and it does a great job.
How difficult would it be to put that data in a comma-delimited format for an excel import?
(and where'd you look to get this info since u don't know vbs? :)
0
 
sirbountyAuthor Commented:
Duh - take out the vbcrlf's, eh? :)  Nevermind...<grin>
0
 
sirbountyAuthor Commented:
Okay - this might save me some of the headaches I'm having with my DOS code...

My main problem has been with foldernames with a "&" inside them...

I'll try to modify this myself, but here's the minor issues I'm still having with your code:

1) It's creating two output lines for the item accessed.  Appears the first line is the full path and file name and the 2nd line is everything that I need.  I should be able to read through this and remove it....

2) Since I don't know VBS (at least not as well as you do - LOL), I actually need to strip off the "Times" from the creation date and last access dates.  Don't need the time for these.  Can I just use a TRIM or SPLIT function for this?

Thanx again!!! :)
0
 
sirbountyAuthor Commented:
Ha - hate to keep spamming you, but I got it...Only outstanding problem is the additional space between each column, but I'm sure I can find that if I just look for it. :D

Ok, I'll just edit my text here instead of sending you a new email.
Continuation, if interested, is at http:Q_21184797.html
0
 
guidwayCommented:
sorry, been busy today with a project and haven't been on the web for a while now. let me read through all your comments real quick and get caught up. lol
0
 
guidwayCommented:
>>and where'd you look to get this info since u don't know vbs?<<

well... I did a few different searches

1.) find a function to loop through all directories of a drive (recursive loop) and search for a specific file

+

2.) determine computer name

+

3.) determine owner of file

and then I began playing with the code to see what did what, etc... Since I knew VB already it wasn't too bad. (the owner information was the worst pain, I still don't understand how that works). The resources I used I searched all through google. I don't have the websites here with me right now, but I probably still have them on my home computer and can check them when I get home tonight. Took about 3 hours to write (give her take a few minute breaks to answer other questions on EE). :)
0
 
guidwayCommented:
give *or* take I meant...
0
 
guidwayCommented:
>>Only outstanding problem is the additional space between each column<<

did you get that space fixed? I haven't tried running your new code (posted to the other question) yet. My e-mail is on an NT machine so I can't run it from here. :( I'll have to wait till I get home to test anything.
0
 
sirbountyAuthor Commented:
Yep - it was a IO error (ignorant operator).
All set here.  Hey thanx for the explanation...I half-attempted googling for some vbs solutions, but gave up - besides DOS scripting is more of a challenge to me :) and it's fun!
0
 
sirbountyAuthor Commented:
0
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.

All Courses

From novice to tech pro — start learning today.