On your view of tickets, what does your TIME OPENED populate as?
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?
Answers (1)
Please review the comments for a more detailed explanation on the DIY fix. Do at your own risk, of course.
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). - chucksteel 11 years ago
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. - Wildwolfay 11 years ago
ALTER TABLE `ORG1`.`HD_TICKET` CHANGE COLUMN `TIME_OPENED` `TIME_OPENED` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ;
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. - Wildwolfay 11 years ago
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. - Wildwolfay 10 years ago
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. - Wildwolfay 10 years ago
(LONG url but just google 'virtual kace k1000' and you should find the PDF, which is what I linked)
More info:
http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=5&cad=rja&uact=8&ved=0CFEQFjAE&url=http%3A%2F%2Fplanet.kace.com%2Fsupport%2Fdocumentation%2FVK1000-QuickStart-Guide-v5.3.pdf&ei=M5exU46GCcfI8gG6iYDYDQ&usg=AFQjCNEEG4zYTlEz7ubV6_9fJDGPZ36gUA&sig2=sL99OLbaoGwJdjL8mTmweA&bvm=bv.69837884,d.b2U - Wildwolfay 10 years ago