How to modify tables in KACE SQL
I am trying to INSERT data into one of my tables in the KACE ORG1 (Just to not scare anyone, I am modifying HD_TICKET_FILTER so I can push out custom views) but it is giving me a command (denied) restriction.
Is this a licensing issue that I'm not aware of or is there a higher power login that isn't publicly listed in the admin and I need to contact someone about?
Answers (2)
Not supposed to make direct changes to the table.
The only supported method to make changes in the database is by creating custom ticket rules. I haven't heard of any users being given write access to the database otherwise.
Comments:
-
So create a ticket rule basically finding the ticket filter information, then have it run an update query? - Wildwolfay 11 years ago
-
Yes that is correct. As chuck stated this is the only place in the K1 that will allow a user to modify the database. Otherwise you are limited to read only. - Timi 11 years ago
-
Yeah I didn't actually have to create a big select rule with an update query. I just entered my INSERT INTO statement in the very first sql query and unchecked everything. It runs and says "0 of 1 tickets found" in the run log but it still executes the command and updates the table. Going to dangerously test whether or not this is true for deleting information in the database. I don't see why not.
essentially running the SQL query through the ticket rule is as if KACE was running the SQL and any command given to it with it's admin rights. - Wildwolfay 11 years ago
The run log should display the rows selected and the rows updated. You might want to check your select statement.
Best Practices is to rewrite your select statement into your update statement. Some update statements are independent of the select statement.
Comments:
-
You're right Kyle, but if I'm running the INSERT INTO logic statement in the very first SQL box that the ticket rule provides, the KACE's brain thinks I'm querying the database for information when in actuality I'm writing.
Essentially, people are not allowed WRITE access unless they write up the SQL statement in MYSQL, let it tell you that you do not have the correct access, copy/paste that into the first SQL query box, and RUN NOW. This is essentially making KACE write the statement instead of a user, granting WRITE access.
The run log will tell you that it found nothing because... well... you didn't query anything. Did the action still take place? Absolutely. I've officially given 20 users a custom view that I designed and liked (Example: tickets that are resolved/consolidated/closed with a resolution).
Honestly the only issue I have now is that when I give these custom views to people it's very inconsistent on how to get the custom view option to even show up in the drop down. For some users they have to be in ALL QUEUES, for another 3 users they have to be in 1 designated queue. For another user he can only see the custom view in admin ui. When I query the database of HD_TICKET_FILTER to see the custom views that people have, they are all identical. - Wildwolfay 11 years ago
There isn't a field in the USER table for the custom views, it's actually in the HD_TICKET_FILTER table and it references the user ID - Wildwolfay 10 years ago