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

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. 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-service.zip


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

   birt-service/
     bin/
     demo-base/
     etc/
     ibm-jre8-aix/
     ibm-jre8-linux/
     ibm-jre8-win64/
     lib/
     logs/
     modules/
     resources/
     webapps/


3.  (Linux and AIX only). Set permissions on key executables.

    - Open a shell window -> cd to the birt-service/ directory -> type

    . init_birt




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: ipccust2
     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 GTA.SR10727153/ 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 MAXIMO.zip and reports.zip to extract the MAXIMO.sl3 SQLite DB file, and the reporting-related files. 
   Leave the doclinks-######.zip as-is (don't uncompress).
   
   Once done, your birt-service/webapps/birt/ directory structure should look like

     birt-service/webapps/birt/
                     custom.css
                     get-attachments.jsp
                     IBM_logo_Black.gif
                     index.html
                     jquery.min.js
                     jquery-tooltip.js
                     MAXIMO.sl3
                     MaximoSystemLibrary.rptlibrary
                     Query.rptdesign
                     ticketprint.rptdesign
                     woprint.rptdesign
                     tivoli.gif
                     tooltip.css
                     doclinks-000001.zip       
                     doclinks-000002.zip
                     doclinks-000003.zip
                       :
                       :
                       :


6. (Optional) Additional customizations:

   a. Edit these lines in birt-service/webapps/birt/reports/get-attachments.jsp to change the default
      location for the doclinks-######.zip files:

      //----- Edit these parameters as necessary ---------
	String remote_path="";  // set to null if not used
	String local_path="webapps/birt";

	For Windows, use double back-slash separators
	For Unix, use single forward-slash separators.

	remote_path is used if the attachment files are to be stored on a remote server.
	local_path is used if the attachment files are stored on the same server as the BIRT service.


   b. Edit this line in birt-service/webapps/birt/WEB-INF/classes/mxreportdatasources.properties to change
      the default location of MAXIMO.sl3:

        MAXIMO_SL3.url=jdbc:sqlite:/C:/birt-service/webapps/birt/MAXIMO.sl3

      For Windows or Unix use the single forward-slash separator.


   c. Edit this line in birt-service/start.ini to change the default service port:

      jetty.ssl.port=8443


C. Start the BIRT Reporting Service


1. How you start the BIRT Reporting Service depends on the platform.

   AIX/Linux/OSX:  

   - Open a shell window -> cd to the birt-service/ directory


   Windows:
   
   - Open a command window -> cd to the birt-service/ directory

   or

   - Open Explorer -> navigate to the birt-service/ directory 
   
        double-click 'Open Command Line'  -> type


2. Start the service

        ./birtman start


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

   
   
   
D. (Optional) Managing the BIRT service
 
1. birtman is the command-line utility for managing the BIRT service.

With it, you can 

- add/remove/list/send password file for users in the password DB
- start/stop/restart/check status of the BIRT service



Usage: birtman options

Where options are
   start                     -- Start the BIRT service
   stop                      -- Stop the BIRT service
   restart                   -- Restart the BIRT service
   status                    -- Show status of BIRT service
   adduser userid [password] -- Create userid with optional password
                             -- If no password given, a random password is generated
   rmuser userid             -- Remove userid
   lsuser                    -- List the password DB
   sendpw userid email_addr  -- (Unix only) Send user's password file via email to the provided address




E. Running BIRT for the first time

1. The BIRT service uses secure https and requires the use of an SSL certificate. The built-in certificate is self-signed and so the first time you access the BIRT service via your browser, you will be presented with a security/certificate challenge.

The procedure for accepting the certificate varies by browser. 

Firefox:
- You will be presented with the message "Your connection is not secure" the very first time
- To accept the certificate, click Advanced -> 'Add Exception' -> 'Confirm Security Exception'


2. The BIRT service uses id/password authentication. You can add/remove users via the birtman command as shown previously.

The default userid/password provided is admin/admin.

   
F. Run BIRT Reports


1. Launch the Query report

   Run your browser and open this URL -> http://localhost:8443/birt or use the machine's IP address. 

   The service can be accessed remotely depending on the service machine's firewall and any interceding firewalls
   between the browser machine and the service machine.


   You will be presented with a query form for CH, WO, IN, PR and SR records. 



2. Build your query

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

      select * from ticket where _________  (for IN PR and SR records)
      or
      select * from workorder where __________ (for CH and WO records)

   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.

   The Max Rows field limits the number of records displayed in the output.
   The Order By field allows you set the sort order by a single field.
   The Additional filter(s) field allows you to add to the where clause. You must know the exact column name and data type
   for this to work.


3. After entering your query parameters, click the appropriate search button to launch the query for that record type.


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


4. Want to run another report query? 

   Return to the initial query form window. Each time you run a query, a separate window is opened for the result set
   leaving the original query window intact.


5. 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.


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


7. How to view attachments?

   Downloading attachments is directly integrated into the BIRT report interface. 
   The detailed report lists any associated attachments which you can click on to download.
   Optionally, you can open the attachment after downloading if you have the appropriate software
   to view the downloaded file type.


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.