Determining if databases still being used

Posted on 2006-04-04
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
    LVL 14

    Expert Comment

    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

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

    and in the servername field enter the server name and click on the button

    LVL 63

    Expert Comment

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

    Author Comment


    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
    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.
    LVL 3

    Expert Comment

    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.
    LVL 6

    Author Comment

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

    Sjef Bro,
    You seem to be a Happy Man !!!

    Partha :)
    LVL 46

    Expert Comment

    by:Sjef Bosman

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
    Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now