Is there a CONTROL table within KACE? For holding times, for example?
Here is the intent: I want specific ticket rules to only run (successfully, at least) during business hours. In its current state, KACE cannot do this on its own so I have to write business hours within a query's where filter so it only succeeds at the specified time.
In each individual ticket rule / query, I (at this time) have to add in business hours:
and TIME(NOW())<'18:00:00' and TIME(NOW())>'05:00:00'
and DAYOFWEEK(NOW()) in (2,3,4,5,6)
This works fine for each ticket rule, however, I've created a maintenance nightmare. If our business hours were to ever change, then I would have to go through and update each ticket rule that has this time frame within the where filter.
Is there a way that I can just write the business hours, or this filter, into a data table and have the ticket call upon that data table for it to run only when those specifications are met? This way I only have to update that business hour table/data cell and it would take effect for all ticket rules, as opposed to having to go in and fix each individual ticket rule (which, in reality, can be changed via a statement writing to the HD_TICKET_RULE table that finds and changes those values.)
That all being said, the solution is to have a control table that is called upon. I was hoping such a table existed and I could just populate it and then call upon it. Another issue I can already see is the success of calling on such a table within a ticket rule.
Has anyone tried this?
Answers (2)
I would recommend using the assets table to hold your business hours. You can make a custom asset to contains the days and times that you are open. If you are clever with the structure of the asset then you could even have it include holidays so that rules don't run when you're closed for July 4th (if you're closed then).
If you want to use it that way, I would probably setup an asset with columns like date, opening time, closing time. You could then use Excel to build a list of business days between now and 2050 and import those days as assets. You would have to go through and delete your holidays, but that shouldn't be too painful. Using the separate columns for opening and closing you could even account for days that you close early (say you get to leave early the day before Thanksgiving, for instance).
Your rules would then need to check the asset_data_x table (where x is the ID of the asset created to hold this) to see if today's date matches and if the rule is running between the open and closing time.
In the event that your business hours change, just re-import the days into the asset and you're done.
Comments:
-
awesome idea using an asset!
Now here is where I show how little I know: What would be the statement within the ticket rule to check the asset data table? Would WHERE now() = Asset_data_10.DATE and now() = Asset_data_10.TIME - Wildwolfay 11 years ago -
So this is how i'm setting it up, is with a Asset Type = Business Hours
then below there is BUSINESS_DAY_START
BUSINESS_DAY_END
DAY_OF_WEEK
I'm creating a new asset that would hold these values (for data reference). once this asset is created with the business hours in the original post, how would I call upon that table to match ticket rule standards? - Wildwolfay 11 years ago -
Note: I did alter the table so those ARE column names (So it's ASSET_DATA_20.BUSINESS_DAY_START instead of ASSET_DATA_20.FIELD_138 - Wildwolfay 11 years ago
So, using the idea of creating a data table with an ASSET_TYPE has worked swimmingly:
I add this little statement to my WHERE filter within the ticket rule and it checks against the assets I made (asset_data_20 = asset type BUSINESS HOURS).
Only hiccup was that I could not hold multiple values for the day of week, so I actually had to make a MONDAY TUESDAY ETC asset each, just to populate the data table, but that is still EXTREMELY easy to maintain if we change working hours ever.
and TIME(NOW())< ASSET_DATA_20.FIELD_140 and TIME(NOW())> ASSET_DATA_20.FIELD_138
and DAYOFWEEK(NOW()) = ASSET_DATA_20.FIELD_139
FYI: I tried to rename the FIELD_139 and such to BUSINESS_DAY_END for better control, and it worked, until I had to go to the asset page of my KACE. the PHP was looking for FIELD_139 and since it didn't exist, it just through errors and freaked out until I changed the names back to stock.