Determining if databases still being used

Posted on 2006-04-04
Medium Priority
Last Modified: 2013-12-18
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?

Question by:TheRookie32
  • 4
  • 3
  • 2
  • +2
LVL 14

Expert Comment

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

LVL 14

Accepted Solution

p_partha earned 1000 total points
ID: 16373658
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:

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)
            Do While nStatus%=0
                  nStatus%=ListGetText(dwList&, 0, nCount%, dwHold&, nLength%)
                  If nStatus%=0 And nLength%>0 Then
                        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
            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
      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
      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
      If Err = 4060 Or Err = 4063 Or Err = 19 Then 'No Database access or .............. '
            Resume ReadNext
      End If

Hope this helps
LVL 14

Expert Comment

ID: 16373667
and in the servername field enter the server name and click on the button


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 63

Expert Comment

ID: 16373676
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 !

Author Comment

ID: 16373742

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!
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16376182
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.

Expert Comment

ID: 16382494
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.

Author Comment

ID: 16386377
@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...
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16389785
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 :)
LVL 14

Expert Comment

ID: 16392717
Sjef Bro,
You seem to be a Happy Man !!!

Partha :)
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16399827

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question