Auto-archive TM1 user profile folders

Ideas and tips for enhancing your TM1 application
Post Reply
Gareth Soo
Posts: 14
Joined: Fri Nov 23, 2012 3:25 am
OLAP Product: TM1
Version: 9.52
Excel Version: 07

Auto-archive TM1 user profile folders

Post by Gareth Soo » Sun Apr 28, 2013 11:11 pm

After some inspiration (thanks Alan), I have put some code together that automatically archives user account folders from the TM1 data directory into a 'Archive' folder reducing the size of the server directory and eliminates the need to clean up the directory (which use to be my annual process).

There are two parts to this code, creating a vbs file that sits on the server (which moves the folders) and ti code that calls the vbs file.

The code will need to be modified to apply to your server:
D:\Cognos\TM1 Servers\planning\data\ is where the data directory is
D:\Cognos\TM1 Servers\planning\archive\ is where the profile folder will be archived to


Code: Select all

dim filesys
userFolder = WScript.Arguments(0)

set filesys=CreateObject("Scripting.FileSystemObject")
If filesys.FolderExists("D:\Cognos\TM1 Servers\planning\data\"& userfolder) Then
filesys.CopyFolder "D:\Cognos\TM1 Servers\planning\data\"&userFolder, "D:\Cognos\TM1 Servers\planning\archive\"&userFolder
filesys.DeleteFolder("D:\Cognos\TM1 Servers\planning\data\"&userFolder)
End If

In my case, I call the vbs file in the EPILOG of the 'remove user' process. The first parameter is where the VBS file sits on the server and pUser is a parameter of the 'remove user' process'. Just note that if any folders in the script path have a space in the name, then you will need to add speech marks, ie. cognos\"TM1 servers"\planning.

EPILOG of DeleteUser:

Code: Select all

S_Run = 'cmd /c D:\Cognos\"TM1 Servers"\planning\Archive\ArchiveExpired.vbs ' | pUser;

Furthermore, to retrieve a list of profile folders without active accounts you can run this vb in a spreadsheet (you will need to log into the server first though):

Code: Select all

Sub Ck()
    Dim strStartPath As String
    Dim servername As String
    strStartPath = "\\servername\d$\cognose\tm1 servers\planning\data"
    servername = "planning"
    ListFolder strStartPath, servername
End Sub
Sub ListFolder(sFolderPath As String, servername As String)
    Dim FS As New FileSystemObject
    Dim FSfolder As Folder
    Dim subfolder As Folder
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    Set FSfolder = FS.GetFolder(sFolderPath)
    For Each subfolder In FSfolder.SubFolders
        subfoldername = Right(subfolder, Len(subfolder) - Len(sFolderPath) - 1)
         'Add your own criteria here to determine whether a folder is a profile folder
         If (Len(subfoldername) = 7 And UCase(Left(subfoldername, 1)) = "T") Then
            i = i + 1
            Cells(i, 1) = subfoldername
                isFound = Application.Run("DIMIX", servername & ":}clients", subfoldername)
                If isFound > 0 Then
                    j = j + 1
                    Cells(j, 2) = subfoldername
                    k = k + 1
                    Cells(k, 3) = subfoldername
                    'You could archive the folder here if you wish
                End If
         End If
    Next subfolder
    Set FSfolder = Nothing
End Sub
Column A will list all profile folders in the data directory, Col B will list active users and Col C will list profile folders without active tm1 accounts which will be archived. You could call the VBS file when Col C is generated but I like to eye-ball the list before moving anything.

I created a formula to generate the below code in the spreadsheet to be used in a bat file. In hindsight though, you could also have this step generate Col D at the same time as Col C. Place the bat file on the server though and let it run.
move d:\cognos\"tm1 servers"\planning\data\6482 d:\cognos\"tm1 servers"\planning\archive
move d:\cognos\"tm1 servers"\planning\data\2849 d:\cognos\"tm1 servers"\planning\archive
move d:\cognos\"tm1 servers"\planning\data\1605 d:\cognos\"tm1 servers"\planning\archive

Post Reply