/build/static/layout/Breadcrumb_cap_w.png

Adding business hours table to KACE, for calling upon in Ticket rules (and easy maintenance!)

(EDIT:  Since the update to KACE, I had to add one more step to this entire process.  I will add this step at the END of the article, as not to cause too much confusion)

 

Too many times I've searched for answers to the business hours issue of KACE.  Through a Q&A here on ITNINJA, I've come up with a solution that I would like to share.  (NOTE:  I'm still not amazing on SQL, but it worrkkkssss)

First:  Create a business hours table.  To do this, (Thanks to Chucksteel) I made an Asset-type and called it Business Hours.  For MY KBOX this ended up creating ASSET_DATA_20 (and it may be different for you).  For this asset Type, I made 3 columns of information:  BUSINESS_DAY_START, BUSINESS_DAY_END, DAY_OF_WEEK.  I then populated these columns with information for my ticket rule queries to check against.  NOTE:  I tried putting all the days of the week into one value and MYSQL just doesn't like comparing multiple values in such a fashion, so I did have to make an ASSET for each day of the week, but that is CONSIDERABLY easier to maintain (and for some companies, there are different hours on different days)

Now that the asset is made, ASSET_DATA_20 is created and has values! 


NOTE:  KACE does not like it when you rename columns within a data table!  I renamed FIELD_138 to BUSINESS_DAY_START and it works fine in a ticket rule when I call, but if you try to view the asset, the ASSET pages .php tries to query for FIELD_138 and can't find it, thus breaks :(.  For this reason, the queries you will see are FIELD_138, FIELD_139, and FIELD_140 as opposed to something named much more user-friendly.

Now:  I have notification ticket rules that send out, and I want that to only happen during the day, so I have to add the following things to a ticket rule:

Within the SELECT field I add:

FIELD_138 as DAYSTART,
FIELD_140 as DAYEND,
FIELD_139 as DAYOFWEEK,
 

Within the FROM field I add: 

FROM (HD_TICKET, ASSET_DATA_20)

 and then in the where filter I add: (NOTE: 138 = Day start, 139 = day of week, 140 = day end)

and TIME(NOW())< ASSET_DATA_20.FIELD_140 
and TIME(NOW())> ASSET_DATA_20.FIELD_138
and DAYOFWEEK(NOW()) = ASSET_DATA_20.FIELD_139

So now the query pulls up all the values from ASSET_DATA_20, and then in the where filters, it checks against them to see if the ticket rule meets the criteria and is allowed to run.  NOTE:  These rules are all set to run every 15 minutes, but an update query with HD_TICKET.MODIFIED being updated makes sure they don't keep running.

So, now if our business hours ever change, then I can just change them within the asset without having to change the query within every single ticket rule.

Thanks for the help random Ninja's with articles with my original time restraint and especially to Chucksteel with the asset Idea, a game changer (and now I know how to create any kind of table, even though I've used it before for importing tickets and just overlooked it's usefullness!)

 

 

EDIT:  Okay so with the update to KACE, no longer can you use a TIME field against a full date field within SQL.  For this reason I had to get a way to UPDATE my assets with the proper date.  Instead of changing the way all of my rules work and re-writing the business rules as a whole, I simply added one more little custom ticket rule that runs at midnight every night.

 Update ASSET_DATA_20
set ASSET_DATA_20.FIELD_138 = concat (CURDATE(),' 06:00:00'), ASSET_DATA_20.FIELD_140 = concat (CURDATE(),' 18:00:00')

The above SQL is placed in a custom ticket rule that is set to run daily at 0:00 and it updates the asset so that it is a date, allowing the SQL to run in the pre-made rules.  This also means that the above picture where I show the assets with their time in BUSINESS_DAY_START and BUSINESS_DAY_END had to be changed from Time stamps to DATE stamps, which are updated daily by the query above.  It adds an extra step to the whole process, but it is working again.


Comments

This post is locked
 
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