Determining if databases still being used

I am tasked with determing which databases are still being used in our organization.  To trim the list down i would like to include a parameter in a view that only includes databases that have had writes to them in the past 6 months.  How can i access the property in the Database Properties>Info>UserActivity > User and Write fields programmatically?  I'd like to be able to do something with this info (maybe set a boolean value to true if x number of users and x amt of writes have been triggered in the last 6 months) but dont know how to get to that property(?)

Or can this be done?

Thanks!
LVL 6
TheRookie32Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

p_parthaCommented:
You can try accessing the database last modified property and check when it was used. I had a similar requirement, and I had some code, will try to dig in for you

Partha
0
p_parthaCommented:
I think this code was written by ZVonko, All you need to create is three fields in one form, the following are the fieldnames:
servername, databasepath,lastaccessed and create one button and paste this code

In the Declarations:

Declare Function OSLockObject& Lib "nnotes" (Byval nHandle%)
Declare Function OSUnlockObject% Lib "nnotes" (Byval nHandle%)
Declare Function OSMemFree% Lib "nnotes" (Byval nHandle%)
Declare Function NSGetServerList% Lib "nnotes" (Byval dwPortName&, nRetServerTextList%)
Declare Function ListGetText% Lib "nnotes" (Byval dwList&, Byval nPrefixDataType%, Byval nEntryNumber%, dwRetTextPointer&, nRetTextLength%)
Declare Function OSTranslate% Lib "nnotes" (Byval nTranslateMode%, Byval dwIn&, Byval nLength%, Byval lpszOut$, Byval nOutLength%)


In the options:
Const OS_TRANSLATE_LMBCS_TO_NATIVE = 1
Const MAX_SERVER_NAME = 256

And a function :
Function GetServerName() As Variant
      Dim ws As New NotesUIWorkspace
      Dim ServerName As NotesName
      Dim lpszServer$
      Dim szArray() As String
      Dim hList%, nStatus%, nCount%, nLength%
      Dim dwList&, dwHold&
      nStatus%=NSGetServerList(0, hList%)
      If nStatus%=0 And hList% <> 0 Then
            Redim szArray(0)
            dwList&=OSLockObject(hList%)
            Do While nStatus%=0
                  nStatus%=ListGetText(dwList&, 0, nCount%, dwHold&, nLength%)
                  If nStatus%=0 And nLength%>0 Then
                        lpszServer$=Space$(nLength%)
                        Call OSTranslate(OS_TRANSLATE_LMBCS_TO_NATIVE, dwHold&, nLength%, lpszServer$, MAX_SERVER_NAME)
                        Redim Preserve szArray(nCount)
                        Set ServerName = New NotesName ( lpszServer$ )
                        szArray(nCount)= ServerName.Abbreviated
                  End If
                  nCount=nCount+1
            Loop
            Call OSUnlockObject(hList%)
            Call OSMemFree(hList%)
      End If
      If Ubound(szArray) > 0 Then
            GetServerName = ws.Prompt(PROMPT_OKCANCELLIST, "Select Server", "Select one server from the list.", szArray(0), szArray)  
      End If
End Function



In the click event of the button

On Error Goto par
      Dim ws As New NotesUIWorkspace
      Dim uidoc As NotesUIDocument
      Dim ServerName As String
      Dim DatabasePath As String
      
      Set uidoc = ws.CurrentDocument
      ServerName = Trim$(uidoc.FieldGetText("ServerName"))
      If (ServerName = "") Then
            ServerName=GetServerName()
      End If
      If (ServerName = "") Then Exit Sub
      Call uidoc.FieldSetText("ServerName", ServerName)
      
      Dim session As New notessession
      Dim db As notesdatabase
      Dim dbdir As New NotesDbDirectory(ServerName)
      Dim path As String
      Dim ServerPaths() As String
      Dim ServerPaths1() As String
      Dim isFound As Integer
      Set db = dbdir.GetFirstDatabase(DATABASE)
      Redim ServerPaths(0)
      Redim ServerPaths1(0)
      count= 0
      While Not db Is Nothing
            path = Strleftback(db.filepath, "\")
            Print path
            openflag = 0
            Call  db.Open(db.Server, db.FilePath)
            If db.isopen Then
                  If Err Then
                        Print "No Access - " & db.Title & ", File Path = " & db.FilePath & " - and Error " & Err & ": " & Error()
                        ThisError = Error()
                        Resume ReadNext
                  End If
                  If Err = 4060 Or Err = 4063 Or Err = 19 Then 'No Database access or .............. '
                        Resume ReadNext
                  End If
                  isFound = False
                  
                  Forall paths In ServerPaths
                        If path = paths Then isFound = True
                  End Forall
                  If Not isFound Then
                        Redim Preserve ServerPaths(Ubound(ServerPaths)+1)
                        Redim Preserve ServerPaths1(Ubound(ServerPaths1)+1)
                        ServerPaths(Ubound(ServerPaths)-1) = db.filepath
                        ServerPaths1(Ubound(ServerPaths1)-1) = Strleft(db.LastModified," ")
                  End If
            End If
ReadNext      :
            Set db = dbdir.GetnextDatabase
      Wend
      Redim Preserve ServerPaths(Ubound(ServerPaths)-1)
      
      'DatabasePath = ws.Prompt(PROMPT_OKCANCELLIST, "Select Database Path", "Select one Database Path from the list.", "", ServerPaths)  
      Forall x In serverpaths
            databasepath = databasepath + x + Chr(10)
      End Forall
      Forall x In serverpaths1
            databasepath1 = databasepath1 + x + Chr(10)
      End Forall
      Call       uidoc.fieldsettext("databasepath",databasepath)
      Call       uidoc.fieldsettext("LastAccessed",databasepath1)
      If (DatabasePath <> "") Then Call uidoc.FieldSetText("DatabasePath", DatabasePath)
      Exit Sub
par:
      If Err = 4060 Or Err = 4063 Or Err = 19 Then 'No Database access or .............. '
            
            Resume ReadNext
      End If


Hope this helps
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
p_parthaCommented:
and in the servername field enter the server name and click on the button

Partha
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

SysExpertCommented:
I would look at the Noets Log DB temlate since it has a view that shows usage.

The problem is that it incldes Server access, and DB's may be read on any server, s that you need to have a consolidated view of AL the servers, not just one.

If these are Mail DB's. I prefer to use the Sent view to see when the last email was sent . This is usually a good indicator.


I hope this helps !
0
TheRookie32Author Commented:
@p_partha:

I believe the last modified property is for when the design of the database was modified, not necessarily documents added to it...

will work with your code though and see what i come up with...  thanks!
0
Sjef BosmanGroupware ConsultantCommented:
There is no need to code anything, the info is there. It is not the Notes Log you should look in, it is the Catalog database (catalog.nsf). The only requirement is that the Catalog-task is running every night. Databases can be excluded from the normal views of this database, but don't worry, they are all in the database.
0
NARobertsCommented:
You can also look in you admin client under "server" "status" and "database users" to see current activity.  You could watch that to see if someone is in it right now.  It won't show history though.  Probably the catalog is best place to look for that.
0
TheRookie32Author Commented:
@sjef - it will require a bit of coding as i dont want to have to physically look at each of the databases because we have about 2000+ of them.  

Thanks for your replies!  I believe i got it...
0
Sjef BosmanGroupware ConsultantCommented:
I think you could have created a simple view in the Catalog, displaying exactly what you need... But if you're happy, I'm happy :)
0
p_parthaCommented:
Sjef Bro,
You seem to be a Happy Man !!!

Partha :)
0
Sjef BosmanGroupware ConsultantCommented:
Yeah...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.