I am in a pretty heated debate with support who says "nope that's how it works" and my TIME OPENED on my ticket view is populating at midnight of the day the ticket is created.  The created populates fine, but the problem being that TIME OPEN is calculated off TIME_OPENED which is giving me 00:00:00 for a time the ticket was made.  So a ticket open for 30 seconds opened at 7 AM, shows as a ticket that was 7 hours long.  I've noticed that TIME_OPENED is the only column in the HD_TICKET table that is varchar(10) and not a timestamp.  in HD_ARCHIVE_TICKET it is a "timestamp" and shows the proper time.   What do yours say?  Am I taking crazy pills?  As far as I know, a ticket age should be from when ownership is taken/creation to when it is closed.  Since my categories are auto-assigned, ownership = creation. 

I'm rather confused as to why this support tech would tell me "yep works great!" when a varchar(10) CAN'T FIT THE WHOLE TIMESTAMP.  How can it possibly be used to calculate a proper time? HELP?

Answer Summary:
Please review the comments for a more detailed explanation on the DIY fix. Do at your own risk, of course.
2 Comments   [ - ] Hide Comments


  • Where do you see that TIME_OPENED is varchar(20)? When I look at the attributes in MySQL Workbench it is listed as a timestamp column.

    When I add the Time Opened column to my ticket view it does show a timestamp. There are tickets that don't have a value (they would show as all zeros in the database) but all of them never had a state that is Open (most were closed as soon as they were entered).
    • I've got the screenshots of the left pane of my MYSQL. TIME_OPENED is a varchar(10) in my database. and my TIME_OPENED cells are filled with dates, but not time. My ticket layout has dates, and attempts to show a time but is 00:00:00.

      The support ticket helper finally went "oh hey, I mixed up tickets and yep, that's bad, sending you to an engineer!" so now I feel less crazy.
    • I can confirm that TIME_OPENED is a timestamp column in my database as well. Sounds like maybe a one-off problem with the database.
  • Thank you guys,


    seems like this would solve all of my problems if I just run it from a ticket rule, but I'll let them take a look first and see what caused it.
    • Were you ever able to find a resolution for this issue? Because after updating my K1000 to 6.0, I am having this issue. KACE techs are telling me "its a known bug" and they will let me know when a fix is out. However, based on how they've handled every other bug report I've submitted, I will probably never see a resolution for this, making the entire feature completely unusable. I'm just curious if the KACE team applied a fix to your box or what happened in the end.
      • No kace fix, just my own. I ran the above SQL through a ticket rule to update the table in my database.

        If you're not very familiar with the database and don't feel comfortable making alterations to it directly, make sure you back it up or just ask KACE if this is a 'wise thing to do to fix the problem for now'

        I applied this fix I believe on 5.3 or 5.4, and I'm on 5.5 and it's still working appropriately.
      • Thanks for the response, I'm not surprised at all to hear that. I'm not very comfortable with SQL or the KACE database but perhaps I will work with a tech to implement that ticket rule, if they are willing to assist.
      • I fully understand the lack of comfort :) If you have a test box, I would try it there of course. Make sure you have an active DB backup.

        Although 'yes' it's a ticket rule, it works by creating the ticket rule, running that command via RUN NOW, then deleting the ticket rule to make sure it's not accidentally ran or turned on. You're just using the ticket rule to write into the database for a one-time stint.
      • Got it. No test box sadly (does KACE let you download a VM version if you've got a physical...?). Seems pretty straightforward though. I just posted about this problem in another thread (http://www.itninja.com/question/k1000-6-0-share-your-experiences-please) so I may wait to see if I can get any KACE tech to respond, and then may try it myself. After adequately securing my backups, that is.
      • If you have a physical kbox, your license is good for a virtual device as well.
        (LONG url but just google 'virtual kace k1000' and you should find the PDF, which is what I linked)

        More info:
Please log in to comment

Answer this question or Comment on this question for clarity


Manually changed my table via a ticket rule to alter the table.  No KACE support resolution, but now works.
Answered 06/30/2014 by: Wildwolfay
Red Belt

Please log in to comment