We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Determining if databases still being used

TheRookie32
TheRookie32 asked
on
Medium Priority
295 Views
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?

Thanks!
Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

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

Partha
CERTIFIED EXPERT
Top Expert 2007

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

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!
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

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

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...
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

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

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

Partha :)
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Yeah...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.