11 Replies Latest reply on Jan 19, 2018 11:42 AM by Carl Wilson

    Exporting ticket data from the ARSystem database

    Nick Harris-Dobson

      I've taken over a project to implement a new Servicedesk, moving away from Remedy to another product.

       

      All but the database server have been shut down, and I've been asked to provide a method of querying the database for any legacy tickets, as we may still need to refer to some historical tickets as part of working through tickets on the new platform.

       

      Just wondering what is the best way of achieving a read-only output of tickets held within the ARSystem database?

       

      Is it just some select SQL queries? (I would want to be able to look up people by first/last name, and tickets by ID)

      Is there a read-only GUI that wouldn't require a full Remedy install but could possibly be used just to query the data? (or even a free-license if there is a read-only application I can install).

       

      Just looking for the best solution, any and all help much appreciated!

        • 1. Re: Exporting ticket data from the ARSystem database
          Mark Walters

          You can use SQL directly but that would require an understanding of the table structure, where the data you want to query resides and how all the various tables are related.  If, for example, it was an ITSM system and you're after work log entries related to incidents there are all sorts of related tables you'll need to trawl through.  There are database views available for the forms which will help but it's probably still a non-trivial exercise.

           

          How long do you need the old data available for?  How many users require access?  What sort of integration options are available?  Would it be possible to export the minimum required information to another system that could be more easily queried?  Is there an option to keep a Remedy server running with a reduced license count/read-only users?  The latter would probably afford a wider choice of integrations such as web services/API.

           

          More questions than answers I'm afraid!

          • 2. Re: Exporting ticket data from the ARSystem database
            Nick Harris-Dobson

            the old data is needed certainly for the foreseeable future, as a customer may query a legacy ticket at any time.

             

            I would only need read-only access for a single user (myself) - the remedy application server and mid-tier servers are all shut down, but are still technically available in our VMware environment. Not sure what is available in terms of a read-only license or something and how much that may cost - ideally looking for a zero-cost solution, even if it is an export of all tickets into a csv/spreadsheet that can then be pumped into something else.

            • 3. Re: Exporting ticket data from the ARSystem database
              LJ LongWing

              Nick,

              There is no cost associated with running your old application server, so if it's still available, you can turn it back on and continue using it...you could easily remove all user access other than yours and have the system fully available to you as if it was still a production machine.  Once the license is purchased you have access to that license going forward....the only ongoing cost is if you are looking for support for that box from BMC....if you don't want support or upgrades, you can continue running your existing server.  As to it being read-only, depending on your version there are options to allow the db to be marked read-only and still access the information through the normal Remedy systems.

              1 of 1 people found this helpful
              • 4. Re: Exporting ticket data from the ARSystem database
                Nick Harris-Dobson

                My problem with that is our legacy Production environment was 8 VMs strong, App 1 & 2, Mid-Tier 1, 2 & 3, Administration Server, SmartIT, Smart Reporting and then the SQL server on top. Since we've moved away from the product I want to avoid turning on as many of these as I can, as its 32 vCPUs and 128GB of RAM which is a tremendous amount of resource.

                 

                If I could simply run the SQL server and maybe 1 of these servers for the sake of having an application that can then query the data? Would one of the application servers and the SQL server alone do what I need it to?

                 

                Thanks

                Nick

                • 5. Re: Exporting ticket data from the ARSystem database
                  LJ LongWing

                  Yes, all of the hardware would be extra considering there would be no users.  You would obviously want to turn off all sorts of background processing (escalations, integration jobs, etc)...so one small app server would be more than sufficient...could probably even run a Mid-Tier on the same box as the app server just to keep resource usage down.

                  1 of 1 people found this helpful
                  • 6. Re: Exporting ticket data from the ARSystem database
                    Igor Svetek

                    Had similar task as you described for our customer, we did following:

                    1. Reconfigured environment from server group (4 App, 4 Mid and 1 Reporting server) to single server (App + Mid)

                    2. Reviewed access rights (left only a small number of people)

                    3. Fine-tuned for optimal hardware performance

                    2 of 2 people found this helpful
                    • 7. Re: Exporting ticket data from the ARSystem database
                      Nick Harris-Dobson

                      Ah fantastic, glad its not just us going through this!

                       

                      So did you have a single server (App+mid) as well as the SQL server on a second box?

                       

                      I presume databases connections etc were already in place as it was on the same server it always was?

                       

                      Thanks

                      Nick

                      • 8. Re: Exporting ticket data from the ARSystem database
                        Nick Harris-Dobson

                        Also, any advise on which way round you went?

                         

                        Did you put the app onto an existing midtier, or vice versa?

                        • 9. Re: Exporting ticket data from the ARSystem database
                          Igor Svetek

                          Correct, we have a single server with App and Midtier and SQL server on the second box.

                          We installed midtier on existing app server (I believe it was server group admin).

                          1 of 1 people found this helpful
                          • 10. Re: Exporting ticket data from the ARSystem database
                            Nick Harris-Dobson

                            fantastic thank you for the insight, at least I know this solution has already been implemented elsewhere.

                             

                            I will discuss with my managers as these servers have already been shut down, so just need the acceptance to keep another online before the rest go through decom.

                            • 11. Re: Exporting ticket data from the ARSystem database
                              Carl Wilson

                              Hi Nick,

                              with your estate, you can turn back on the AR Admin Server and the Mid-Tier (on the internal network).

                              You can also reduce the specs that we did on the servers as part of the performance tuning to get back your VM resources.

                              This would be better than going through a single server install.

                               

                              Yourself or Andy can ping me an email if you want to discuss.

                               

                              Cheers

                              Carl