4 Replies Latest reply on Oct 4, 2016 8:20 AM by TeriK.

    Can I update a HEAT ticket directly with a script? HEAT Call Logging v9.6.2

    TeriK. Rookie

      I have an IT person who wants to update tickets (add a Journal entry and possibly create or resolve an assignment) via a script that he is writing to take care of some other tasks outside of HEAT.  He wants to write directly to the HEAT database without opening the application.  Has anyone ever done this and/or can it be done?  I'm not getting warm fuzzies about it. I am concerned that something will be missed in the background -- ModBy, ModByDate, or something.

       

      Help!  Thoughts?

       

      ~Teri

        • 1. Re: Can I update a HEAT ticket directly with a script? HEAT Call Logging v9.6.2
          AlasdairRobertson ITSMMVPGroup

          Technically you will not be supported for scripts etc. that modify the database outside of the system API.  Saying that in HEAT 9.x historically SQL scripts have been used to provide additional functionality.  There used to be an API programming guide for HEAT I am not sure if it is still supported but possibly worth checking out with support in case there is some new documentation.

           

          In the case of adding journal entries you will also be faced with the HEATSeq number which is required (its the number of seconds since 01/01/1970) and the DTLastMod date which is the number of seconds since 01/01/1970 these are UTC if I remember correctly but check.

           

          In general do not write directly to the database but if you must then you should make sure that your scripts are tested very very well, also you will need to reset after every edit set or upgrade for compatibility.

          • 2. Re: Can I update a HEAT ticket directly with a script? HEAT Call Logging v9.6.2
            AlasdairRobertson ITSMMVPGroup

            Found a few mins here is a script that will insert a new journal record, just note due to SEQ you can only insert 1 record every second essentially as HEATSeq is a primary key on the table.

             

            /* Inserts a new journal entry*/
            Declare @SEQ int
            Set @SEQ = DateDiff(second,convert(datetime,'01/01/1970'),getdate())
            Insert into Journal (CallID, HEATSeq, Tracker,EntryDate,EntryTime,EntryText,JournalType,DTLastMod,EntryText2,TempField,TotalJournalTime) 
            Values 
            (
              '00000083',
              @SEQ,
              'Admin',
              convert(varchar(10),GetDate(),21),
              convert(varchar(8),GetDate(),108),
              'This is my Journal Entry',
              'Called Customer',
              @SEQ,
              '',
              '',
              0
            )
            
            
            • 3. Re: Can I update a HEAT ticket directly with a script? HEAT Call Logging v9.6.2
              stapletj@tbh.net Apprentice

              My scripts add journal entries through the email listener. If you format the subject line properly, it gets added to the ticket Activity History without having to worry about direct DB writes. It's also forward-compatible. I use blat on windows platform to send the emails.

              • 4. Re: Can I update a HEAT ticket directly with a script? HEAT Call Logging v9.6.2
                TeriK. Rookie

                Thank you for all of your helpful suggestions!  In the end we chose to use HEAT Messaging Center to update the tickets in question.

                 

                I appreciate the community support.

                 

                Teri