Scripting Question

I need to change a range of values in a table with INSERT TO, but doesn't seem to work.

04/17/2013 1893 views

INSERT INTO HD_TICKET_FILTER (ID, USER_ID, NAME, FIND_FIELDS) VALUES ('incrementingvalue', '1371', 'Closed Tickets Only', 'a:13:{s:7:"WFIELD5";s:14:"HD_STATUS.NAME";s:11:"EXP_SELECT5";s:6:"EQUALS";s:6:"INPUT5";s:8:"Resolved";s:13:"UNION_SELECT6";s:2:"OR";s:7:"WFIELD6";s:14:"HD_STATUS.NAME";s:11:"EXP_SELECT6";s:6:"EQUALS";s:6:"INPUT6";s:12:"Consolidated";s:13:"UNION_SELECT7";s:2:"OR";s:7:"WFIELD7";s:14:"HD_STATUS.NAME";s:11:"EXP_SELECT7";s:6:"EQUALS";s:6:"INPUT7";s:6:"Closed";s:13:"UNION_SELECT8";s:1:"0";s:11:"FILTER_NAME";s:14:"Closed Tickets";}')

Basically I have my 4 columns of information I want to insert into.  The first in a value that cannot be duplicated, and incrementingvalue (luckily) works great so I can have the actual custom view name made.  The second value is the user I want to push the custom view to.  The third is the name of the custom view that they will see.  The fourth value is the wizard generated custom view from the KACE.

My question is focused on the second value.  For some reason, I can not seem to modify a GROUP of user ID's.  I have tried 'BETWEEN 1370 AND 1500' but to no avail.  Even if I can comma seperate them (without it thinking it's the next value, would '1371, 1370' work?  It didn't seem to for me because of their unique ID) that would be nice.  I don't want to have to run this insert statement over 3000 times to push a view out to people. 

FYI As it is written now (KACE RELATED) I just create a blank ticket rule, turn off EVERYTHING and run it in the first SQL table and it works great.  It is confirmed method of pushing a custom view out to users.  However, I don't want to do each user individually.  Thanks!

Answer Summary:
0 Comments   [ + ] Show comments


All Answers


Warning: the understanding of this is advanced but implementing it is easy.

You don't need to worry about ID as it will take care of iteself so the INSERT becomes:

insert into HD_TICKET_FILTER(USER_ID, NAME, FIND_FIELDS) values (blah)

Aside: I think 'incrementingvalue' is working for you because are essentially doing is inserting a string into an integer column and cast('incrementingvalue' as char) = 0 and the system will just ignore your 0 and increment it.  

Let's pretend that you wanted to "push this value to" (your words) a group of users that are in a label labelx.  I am not going to change anything about this filter except WHO uses it.  If i want to change the filter I will change it in the UI. 

However, I am not satisfied with pushing this filter one-time to a group of users.  I want to both:

  1. "Push this value" to a user group so they can start to use the same filter
  2. Maintain this filter - meaning any changes that user 1371 makes to this filter I want the others to inherit it.     In this way 1371's filters become a "template"

If that is the the case then this is what you want to do. 

Create a ticket rule:

  • * Frequency: some schedule (not  on ticket save )
  • * select query:  in this rare case it doesn't matter since we're going to ignore any pre-requisites and blindly copy / overwrite what is already there with our template
    select 1
  • * update query:
        ifnull(FILTER_CHK.ID, 0) OLD_OR_NEW_ID,
            and LABEL.NAME = 'labelx' /* our group of users */
     /* our template filter */         FILTER.NAME = 'Closed Tickets Only' 
            and FILTER.USER_ID = 1371
            left join
Hopefully you can see clearly in bold where you would need to modify the query for your situation.  The rest of the query can be used by any customer.

Sorry for the complexity of the query but since HD_TICKET_FILTER does not have a unique index on the FILTER.NAME and FILTER.USER_ID columns combined (it should but doesn't) I had to jump through an extra hoop to get it to work. 

edit: just made it easier to read that's all

Answered 04/17/2013 by: GillySpy
7th Degree Black Belt

  • It's a great concept and may be something I want to implement at one point, but isn't exactly what I was looking for as far as having a template. I suppose if I wanted to use a disabled account as one, that would work, but essentially this is going to be an initial push of views. Essentially when KACE goes live for us, I want users to log in, go to the queue, and have the custom view option of viewing "Closed tickets only" to be readily available. I'm not entirely concerned about it being dynamically updated, and even more so don't want them to make changes that are just going to revert when I activate the rule in the future for others.

    As I've played with this, I've just taken my original statement and copy/pasted it as many times I need (thankfully only a couple hundred) and replaced the user ID appropriately and then ran it as a whole through a blank ticket rule, updating 200 users with 1 RUN NOW.

    I let this reply marinate as I re-looked at the statement and there is 1 solid good part using the join of the labels to select users.... i will play some more with what you have given me, thank you.
  • A "replace" is both an insert and an update when written as i have. Meaning you can still use this as an initial push. simply disable the rule after running it one time. Re-enabled it (or use "run now") if you change your mind and want a template approach.
  • Thank you ! :)

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login


This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ