• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3740
  • Last Modified:

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!
0
sirbounty
Asked:
sirbounty
  • 18
  • 18
  • 2
  • +2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 18
  • 18
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now