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

asked on

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!
Avatar of kemp_a
kemp_a

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?

Avatar of sirbounty

ASKER

I also would like the owner and last access date (don't think I can get it via dos)
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...
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)
Sorry - XP client and 2k/2k3 backend.
Sounds good for the one.  I think I can pull the owner via wsh.
Testing...
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?
>>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
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...
Good info guidway...some points to you for that info, it may come in handy someday. :)
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  
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
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!
Gotta love these office proxies - can't access groups.msn.com from here - but I'll take a peek at it later from home. :)
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
hes - is that going to 'open' each db?  I don't need that...
Eddyky - still looking at this one..
Yes it is that is the only place the Author is stored.
Oh - okay.  Looking more for the OS 'owner' info.  I'm running this against 90+ servers that may have hundreds of databases present...
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?
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).
shouldn't be that bad (I meant)
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.
ASKER CERTIFIED 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
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. :)
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...
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
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! : )
no prob...

enjoy your days off

guid
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!
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? :)
Duh - take out the vbcrlf's, eh? :)  Nevermind...<grin>
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!!! :)
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
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
>>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). :)
give *or* take I meant...
>>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.
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!