How to download and work with Maximo data
----------------------------------------------

Download format:
  Database format:    SQLite v3
  Attachments format: various



A. Download and install the BIRT Reporting Service

The BIRT solution consists of 2 components: the Web Browser (or client), and the 
Reporting Service (or server). There are 2 configuration modes, Single and Multi-User.

  In Single-User Mode, the Web Browser and the Reporting Service run on the same machine.
  For example, everything is installed on a user's workstation and is accessible to only
  the workstation's owner.

  In Multi-User Mode, the Web Browser runs on one machine and the Reporting Service on another.
  Multi-User Mode is used for sharing a single copy of the data amongst several users. 
  In this configuration, unique user accounts are created on the machine running the 
  Reporting Service and SSH-tunneling is used for network security and data privacy.
  Users access the data over the network via Web Browsers running on their respective
  workstations.

The Reporting Service will run on any platform as it is purely java-based. 
The machine it runs on can be any Windows, AIX, Linux, or OSX server/workstation. 
The Reporting Service requires java 1.8 to run.


1. Decide upon a configuration mode: Single or Multi-User Mode.


2. Install java 1.8 on the machine that will run the Reporting Service, if not already
   installed. After installing, locate the directory that contains java.exe. 
   You will need this in step A.5.
   

3. Download the BIRT installation zip to the machine that will run the Reporting Service.

   ftp://public.dhe.ibm.com/support/IPC/ipc-data-delivery/software/birt.zip


4. Extract the zip to create the following directory tree, then copy or move it to your preferred
   installation location.

   birt/
     bin/
     db/
     demo-base/
     etc/
     lib/
     logs/
     modules/
     resources/
     webapps/


5. Configure your installation

   If the machine running the Reporting Service is Windows, then edit this line in
   birt/BIRT.cmd:
   
     set JAVA_HOME=<directory containing java.exe from step A.2>

   If the machine running the Reporting Service is AIX, Linux or OSX, then edit this
   line in birt/BIRT.sh:

     JAVA_PATH=<directory containing java.exe from step A.2>


   Optionally, edit the PORT value if you want to run the server under a port other
   than the default 8080.


6. If you have selected the Multi-User configuration, there are a few extra steps

   - Ensure the sshd service is running.
   - Ensure port 8080 (or other port if you changed it) is blocked by the firewall EXCEPT to local users.
   - Add a local account for each user who requires access to the data.





B. Download and add your data

1. Your data is downloadable via public internet access using either the HTTPS or SFTP secure protocols
   with this ID and password:

     Userid: userid
     Password: password

   The HTTPS download method is the easiest to use as it only requires a browser and simple point-
   and-click to download one file at a time. However, this method is cumbersome to use
   if you have a lot of files to download.
   
   The SFTP download method is best to use if you have a lot of files in your download, but you will need
   specialized sftp software to allow you to perform batch downloads. The following free, public domain
   software is recommended for this purpose: WinSCP (Windows), FileZilla (Windows, Linux, or OSX),
   Cyberduck (OSX).


   A. HTTPS Download Method

      Open your browser, and navigate to 

        https://transfer.boulder.ibm.com



   B. SFTP Download Method

      Open WinSCP or FileZilla.

      Create a new connection as follows, with the provided ID and password:

        WinSCP: <From initial screen after opening app>
                Tools->Preferences -> Transfer-> Background-> Maximal number of transfers at the same time: [9]
                Select 'New Site', enter the following
                  File protocol [SFTP]
                  Host name: [transfer.boulder.ibm.com]
                Click Save, then Login
        FileZilla: <From initial screen after opening app>
                File -> Site Manager -> New Site -> enter the following
                  Logon Type: [Normal]
                  Host: [transfer.boulder.ibm.com
                  Protocol: [SFTP - SSH File transfer protocol]
                  Transfer Settings -> [x] Limit Number of Simultaneous Connections -> [10]
                Click Connect
        Cyberduck: 
                Bookmark -> New Bookmark ->
                  <select protocol at top> [SFTP - SSH File Transfer Protocol]
                  Server: [transfer.boulder.ibm.com]
                  Transfer files: [open multiple connections]
                Close the Window
                Double-click the bookmark to Connect
                


2. Log in with the provided ID and password.



3. Download files

   With your browser, or SFTP software, navigate to the CUST.SR######## directory on the remote server.
   Download the following files:


              MAXIMO.zip
              reports.zip
              doclinks-000001.zip       
              doclinks-000002.zip
              doclinks-000003.zip
                      :
                      :


4. Copy or move the downloaded files into the birt/webapps/birt/ directory of the machine selected to run the 
   BIRT Reporting Service.   (See step A.4)                     :


5. Uncompress all the ZIP files to extract the MAXIMO.sl3 SQLite DB file, the doclinks/ (attachments)
   and (BIRT) reports/ subdirectories. 
   
   Once done, your birt/webapps/birt/ directory structure should look like

     birt/webapps/birt/
                     doclinks/
                           attachments/
                                    file1
                                    file2
                                     :
                           images/
                                    file1
                                    file2
                                     :                       
                     reports/
                       Query.rptdesign
                       ticketprint.rptdesign
                       woprint.rptdesign
                       MaximoSystemLibrary.rptlibrary
                       tivoli.gif
                       IBM_logo_Black.gif
                     MAXIMO.sl3
                       :

   After extracting, you can safely move or delete all the ZIP files.


C. Start the BIRT Reporting Service

There are 2 modes for running the service: console or daemon

   - Console mode: All logged output will display on the shell/terminal screen used to 
                   launch the service. If the terminal screen is closed, the service is 
                   stopped.
   - Daemon mode: All logged output is sent to a log file.
                  The service is independent of the shell/terminal window used to launch 
                  it, which means you can start the service in this mode and then log out.


1. How you start the BIRT Reporting Service depends on the platform and the service mode 
   you want to start the service with.

   AIX/Linux/OSX:  

   - Daemon mode: open a shell window -> cd to the birt/ directory -> type 

        sh BIRT.sh 

     You can then log out if you want to and the service will still continue to run.

   - Console mode: open a shell window -> cd to the birt/ directory -> type 
   
        sh BIRT.sh console
        
     You will have to remain logged in for the service to continue running in this mode.	



   Windows: Only console mode is available on this platform
   
   - Console mode:  Open Explorer -> navigate to the birt/ directory 
   
        double-click BIRT.cmd.  

     You will have to remain logged in for the service to continue running in this mode.


   
   
   
   
D. Run BIRT Reports


1. (Optional - Remote mode) If accessing the BIRT Reporting Service in Multi-User mode, use PuTTY to create an SSH tunnel as follows:

   Session -> Host name [BIRT Reporting Service machine name or IP] -> Port [22] -> Saved Sessions [BIRT Reporting Server]
   Connection -> SSH -> Tunnels -> Source port [8080] -> Destination [localhost:8080] -> (*) Local -> Add  
   Session -> Save
   
   Open the connection by clicking Open, then log in with the userid and password created for you on that server.
   
   Leave this PuTTY session open while you access your BIRT data.


   Note: Use the port configured in step A.5 in the above instruction. (8080 is the default.)


2. Launch the Query report

   Run your browser and open this URL -> http://localhost:8080/birt 
   
     - If you are running in single-user mode, your browser will connect to your local BIRT Reporting Service on localhost port 8080.
     - If you are running in multi-user mode and have opened your SSH tunnel, your browser connection to localhost on port 8080
       will be redirected to the remote server's port 8080 via the secure tunnel.

   Click 'Run Query'

   You will be presented with a Parameter input form:

   ---After running a report, there is no need to close the viewer to start over.
      From the 'Query' BIRT Report Viewer window, you can click on the 2nd icon with a 'P' under the words
      "BIRT Report Viewer" to relaunch the parameter form. If you get a session timed out error message,
      start over with the http://localhost:8080/birt URL.

   Note: Use the port configured in step A.5 in the above instruction. (8080 is the default.)


3. Build your query

   Note: The Parameter form helps you build your ticket table query. You are building this statement:

      select * from ticket where _________
      or
      select * from workorder where __________

   Every parameter you supply a value for in this form adds to the sql clause. The resulting SQL is displayed in the query output.
   The parameter names closely match what you see in the ISM GUI.



4. Click OK to run the query.

   In the resulting report, click on the ticket or workorder number to display a detailed report
   containing all data related to that item.


5. Want to run another report query? 

   Return to the 'Query' BIRT Report Viewer window launched earlier and click the 2nd icon (with a 'P') found
   below the words "BIRT Report Viewer". This will launch the Parameter form, and you are ready to go again.


6. Want to save your detailed ticket/workorder into a PDF or Excel file?

   In the Detailed Report window, click the 4th icon found below the words "BIRT Report Viewer" ->
   Select either 'Excel' or 'PDF' in the Export Format drop list -> Click Ok ->
   A File Download window will prompt you to either directly open the new file or simply save it.

   Note: the other file formats are not as useful, IMO.


7. Want to print your report?

   Click on the 5th icon found below the words "BIRT Report Viewer"

   Alternatively, save the report as PDF or Excel, open that file and print from there.


8. How to view attachments?

   Viewing attachments is directly integrated into the BIRT report interface. 
   The detailed report lists any associated attachments which you can click on to open.
   


9. Enjoy!!





NOTE: This last procedure is purely optional and is more advanced. Mostly for DBA weenies who want to hack the data.


######################################################################
E. Work with the downloaded data using SQL Client (Advanced)
 - Multiple tools are possible. A couple of options are provided below
######################################################################

SQLite Studio (recommended for Customers)
------------------------------------------
1. Install and run the provided (freely licensed) SQLitestudio utility, if not already installed.

   Available here: http://www.sqlitestudio.pl/?act=download


2. Open the downloaded SQLite database as follows:

   Databases -> Add database
   Navigate to the directory where you extracted your data in step B.2
   Locate the .sl3 database file, a double-click it.
   Click Ok.

   Right-click on the newly added DB -> Connect

3. To browse the data for a given table or view, expand the Tables or Views section in the left navigation pane,
   then double-click the table or view you want.
   Select the 'Data' tab in the right-pane to view the data for that table or view.

   For your convenience, several useful views are provided:

     CH_, AC_, WO_  (CHANGE, ACTIVITY, WORKORDER)

       Views of the WORKORDER table for the respective type of WORKORDER record,
       with commonly used fields .


     SR_, PR_, IN_: (SR, PROBLEM, INCIDENT)

       Views of the TICKET table for the respective type of TICKET record,
       with commonly used fields.


     CH_DOCS, AC_DOCS, WO_DOCS, SR_DOCS, PR_DOCS, IN_DOCS

       Attachments related to the respective type of record
       Attachments are found in the provided doclinks.tar file.


     CH_LDESC, AC_LDESC, WO_LDESC, SR_LDESC, PR_LDESC, IN_LDESC

       Long descriptions related to the respective type of record


     CH_LOG, AC_LOG, WO_LOG, SR_LOG, PR_LOG, IN_LOG:

       Work logs related to the respective type of record


4. To query for specific records and common related data

   a. Open an SQL query editor window

      Tools -> Open SQL Query editor (or click the pencil icon or alt-e)

   b. Enter this query to look for data for, say, tickets IN10198798 and IN10198851

      select * from IN_ where TICKETID in ('IN10198798','IN10198851')

   c. Run the query

      Click the lightning bolt icon, or F9

   d. Repeat a-c with the following SQL:

      select * from IN_DOCS where TICKETID in ('IN10198798','IN10198851')

   e. Repeat a-c with the following SQL:

      select * from IN_LDESC where TICKETID in ('IN10198798','IN10198851')

   f. (Optional) Save results as a CSV file.

      Click the 'Export results' icon (5th icon from the left in the toolbar) ->
      Enter your output file name -> click Configure -> 
      Check [x] Column names as first row -> 
      Select [\t (tab)] Columns separator: -> Click Ok ->
      Click Export

   Note:

   Sample SQL to query for all record types and common related data

   INCIDENT
   --------
   select * from IN_ where TICKETID in ('IN10198798','IN10198851')
   select * from IN_DOCS where TICKETID in ('IN10198798','IN10198851')
   select * from IN_LDESC where TICKETID in ('IN10198798','IN10198851')

   SR
   --
   select * from SR_ where TICKETID in ('SR10198798','SR10198851')
   select * from SR_DOCS where TICKETID in ('SR10198798','SR10198851')
   select * from SR_LDESC where TICKETID in ('SR10198798','SR10198851')

   PROBLEM
   -------
   select * from PR_ where TICKETID in ('PR10198798','PR10198851')
   select * from PR_DOCS where TICKETID in ('PR10198798','PR10198851')
   select * from PR_LDESC where TICKETID in ('PR10198798','PR10198851')

   CHANGE + ACTIVITIES
   -------------------
   select * from CH_ where WONUM in ('CH10043315','CH10043509')
   select * from CH_DOCS where WONUM in ('CH10043315','CH10043509')
   select * from CH_LDESC where WONUM in ('CH10043315','CH10043509')
   select * from AC_ where PARENT in ('CH10043315','CH10043509')
   select * from AC_DOCS where WONUM in (select WONUM from AC_ where PARENT in ('CH10043315','CH10043509'))
   select * from AC_LDESC where WONUM in (select WONUM from AC_ where PARENT in ('CH10043315','CH10043509'))

   WORKORDER + ACTIVITIES
   ----------------------
   select * from WO_ where WONUM in ('10043315','10043509')
   select * from WO_DOCS where WONUM in ('10043315','10043509')
   select * from WO_LDESC where WONUM in ('10043315','10043509')
   select * from AC_ where PARENT in ('10043315','10043509')
   select * from AC_DOCS where WONUM in (select WONUM from AC_ where PARENT in ('10043315','10043509'))
   select * from AC_LDESC where WONUM in (select WONUM from AC_ where PARENT in ('10043315','10043509'))


5. To view attachments, navigate to the doclinks/ directory you downloaded in step B.3

   birt/webapps/birt/
                   doclinks/            --> directory you created after extracting the doclinks-???????.zip files
                       attachments/
                       images/     

   The DOCLINKS+DOCINFO tables contain the references to all the files associated with the records.

   For your convenience, a set of Views is provided to help you locate your documents more quickly.
   The CH_DOCS AC_DOCS WO_DOCS PR_DOCS IN_DOCS and SR_DOCS views in the database contain references to all the files
   associated with the respective record type.


SQuirreL SQL (For IBMers)
--------------------------------
0. Install java 1.6 or greater first, if not already installed. Make sure it is in your path.


1. Download SQuirrel SQL, if not already installed.

   Inside IBM, available here: http://appstore.tap.ibm.com/app/68097/squirrel-sql-liua

   Outside IBM, available here: http://www.squirrelsql.org/#installation
     Select the "Install jars..."


2. Install SQuirreL SQL (version may vary, and instructions are for WIndows 7)

   a. Start (menu) -> Search programs and files -> cmd -> Right-click cmd.exe -> Run as administrator ->
   b. If prompted with 'User Account Control', click Yes ->
   c. cd to the directory where you downloaded the .jar file to 
   d. run the installation command:
         java -jar squirrel-sql-3.4.0-installer.jar
   e. follow installation program prompts
      Welcome (screen) -> Next
      Required java version (screen) -> Next
      Select installation path -> Next
      Click OK if prompted with 'The target directory will be created' ->
      Select the pack you want to install (screen) -> [x] Standard -> Next ->
      Pack installatin progress (screen) -> Next ->
      Setup Shortcuts (screen) -> Next -> 
      Installation has completed (screen) ->  Done
     

3. Download/Install the SQLite jdbc driver (if not already done)

   Available here: https://bitbucket.org/xerial/sqlite-jdbc/downloads
     (Select the package named   sqlite-jdbc-#.#.#.jar)

   Copy downloaded jar file into SQuirrel lib/ directory
     for example, copy sqlite-jdbc-3.7.2.jar into c:\Program Files\squirrel-sql-3.4.0\lib\
 
   Open SQuirreL SQL Client -> click 'Drivers' tab in left navigation pane ->
   Click Drivers -> New driver
     Name: SQLite Driver
     Example URL:     jdbc:sqlite:C:/Users/johndoe/Downloads/MAXIMO.sl3
     Website URL:     https://bitbutcket.org/xerials/sqlite-jdbc
     Java Class Path: (locate the sqlite-jdbc-#.#.#.jar file in the list and select)
     Class Name:      org.sqlite.JDBC
   Click OK


4. Create Alias to Maximo SQLite database file (if not already done)

   Click Aliases on left navigation pane ->
   Select Aliases -> New Alias ->
   Name:  Maximo
   Driver: SQLite Driver
   URL:  jdbc:sqlite:C:/Users/johndoe/Downloads/MAXIMO.sl3
     (edit the path to for your .sl3 file)
   Click Ok -> Close


5. Open database

   Click Aliases on left navigation pane ->
   Right-click 'Maximo' and select 'Connect' -> Connect


6. To view the data for a given table or view, expand the Tables or Views section in the left navigation pane,
   then double-click the table or view you want.
   Select the 'Data' tab in the right-pane to view the data for that table or view.

   For your convenience, several useful views are provided:

     CH_, AC_, WO_  (CHANGE, ACTIVITY, WORKORDER)

       Views of the WORKORDER table for the respective type of WORKORDER record,
       with commonly used fields .


     SR_, PR_, IN_: (SR, PROBLEM, INCIDENT)

       Views of the TICKET table for the respective type of TICKET record,
       with commonly used fields.


     CH_DOCS, AC_DOCS, WO_DOCS, SR_DOCS, PR_DOCS, IN_DOCS

       Attachments related to the respective type of record
       Attachments are found in the provided doclinks.tar file.


     CH_LDESC, AC_LDESC, WO_LDESC, SR_LDESC, PR_LDESC, IN_LDESC

       Long descriptions related to the respective type of record


     CH_LOG, AC_LOG, WO_LOG, SR_LOG, PR_LOG, IN_LOG:

       Work logs related to the respective type of record


7. To query for specific records and common related data

   a. Click the SQL tab  (middle tab between Objects and Hibernate)


   b. Enter this SQL to look for data for, say, tickets IN10198798 and IN10198851

      select * from IN_ where TICKETID in ('IN10198798','IN10198851');
      select * from IN_DOCS where TICKETID in ('IN10198798','IN10198851');
      select * from IN_LDESC where TICKETID in ('IN10198798','IN10198851');

   c. Run the query

      Click the 'running man' icon, or Ctrl-Enter

   d. View results in results pane immediately below the SQL query pane. There will be one
      results tab for each query.

   e. (Optional) Save results as a MS Excel file.

      Right-click one of the result rows and select 'Select All' -> 
      Right-click one of the highlighted rows, and select 'Export CSV / MS Excel XML'
      Enter your output filename -> select (*) Export MS Excel (XLS) file -> click Ok


   Note:

   Sample SQL to query for all record types and common related data

   INCIDENT
   --------
   select * from IN_ where TICKETID in ('IN10198798','IN10198851');
   select * from IN_DOCS where TICKETID in ('IN10198798','IN10198851');
   select * from IN_LDESC where TICKETID in ('IN10198798','IN10198851');

   SR
   --
   select * from SR_ where TICKETID in ('SR10198798','SR10198851');
   select * from SR_DOCS where TICKETID in ('SR10198798','SR10198851');
   select * from SR_LDESC where TICKETID in ('SR10198798','SR10198851');

   PROBLEM
   -------
   select * from PR_ where TICKETID in ('PR10198798','PR10198851');
   select * from PR_DOCS where TICKETID in ('PR10198798','PR10198851');
   select * from PR_LDESC where TICKETID in ('PR10198798','PR10198851');

   CHANGE + ACTIVITIES
   -------------------
   select * from CH_ where WONUM in ('CH10043315','CH10043509');
   select * from CH_DOCS where WONUM in ('CH10043315','CH10043509');
   select * from CH_LDESC where WONUM in ('CH10043315','CH10043509');
   select * from AC_ where PARENT in ('CH10043315','CH10043509');
   select * from AC_DOCS where WONUM in (select WONUM from AC_ where PARENT in ('CH10043315','CH10043509'));
   select * from AC_LDESC where WONUM in (select WONUM from AC_ where PARENT in ('CH10043315','CH10043509'));

   WORKORDER + ACTIVITIES
   ----------------------
   select * from WO_ where WONUM in ('10043315','10043509');
   select * from WO_DOCS where WONUM in ('10043315','10043509');
   select * from WO_LDESC where WONUM in ('10043315','10043509');
   select * from AC_ where PARENT in ('10043315','10043509');
   select * from AC_DOCS where WONUM in (select WONUM from AC_ where PARENT in ('10043315','10043509'));
   select * from AC_LDESC where WONUM in (select WONUM from AC_ where PARENT in ('10043315','10043509'));




7. To view attachments, navigate to the doclinks/ directory you downloaded in step B.3

   birt/webapps/birt/
          doclinks/                --> directory you created after extracting the doclinks-???????.zip files
                attachments/
                images/     

   The DOCLINKS+DOCINFO tables contain the references to all the files associated with the records.

   For your convenience, a set of Views is provided to help you locate your documents more quickly.
   The CH_DOCS AC_DOCS WO_DOCS PR_DOCS IN_DOCS and SR_DOCS views in the database contain references to all the files
   associated with the respective record type.