Right up front, I want to give a huge shoutout to my friend Michelle at Costal Community Credit Union. She took time out of her day to help me tweak the queries below until we got them working just right. You rock, Michelle!
So, a question came up a recently about setting default label restriction on KB articles. Basically, if we create articles, and someone forgets to add the appropriate label to it, then everyone can see the article. This obviously becomes an issue when the article in question is intended to be internal IT documentation or the like.
While there's not a default setting for this, it is something you can accomplish with a simple ticket rule, so I thought I'd share. Obviously there's still some limitation here as ticket rules aren't running constantly (most frequent you can run them is every 15 minutes). So there could still be cases where a KB gets published and a user sees it, so if your KBs contain particularly sensitive information, you may want to explore some additional options or training for you team. But as a safety-net solution, this will work pretty well.
First thing to know is that KBs live in a table called "ADVISORY." This table houses the contents of the KB as well as the KB ID. We also have a table called "ADVISORY_LABEL_JT" which houses the KB IDs and the accompanying label IDs. The important thing to note about ADVISORY_LABEL_JT is that when a KB doesn't have a label, you don't have a null field in the table, it just doesn't populate a row at all for the associate KB ID. Because of this, when we write the second half of the ticket rule, we can't use an "update" we have to instead use an "insert."
Another important note, is that we'll be adding our "default" label via label ID. The easy way to find the label you want to use, is to simply navigate to Home > Label Management > Labels
Now find the label you want to use, and depending on your browser hover your cursor over it, and somewhere you should see the URL, or alternatively, right click the label name and copy the URL address, paste it somewhere that you can view it in plaintext, and at the end of the URL you will see the label ID number.
With these things in mind, we can dive into the ticket rule. First you'll want to select tickets without labels, obviously. So to do this, we'll join the ADVISORY table and the ADVISORY_LABEL_JT table. And because we're joining the tables, the resulting table WILL contain null values in the label ID field, so we'll select for rows where that ID field is null. Then in our update query, because we don't have null fields as previously mentioned, we'll run an insert to create a record with both the KB ID and the Label ID.
So putting it all together now (starting from step one for those who may have never used ticket rules)
Go to Service Desk > Configuration > Rules
Then you're going to click Choose Action and select "New (SQL)"
Now we'll build our queries. First our select which will look like this:
select ADVISORY.ID as ADVISORY_ID
left join ADVISORY_LABEL_JT
on ADVISORY.ID = ADVISORY_LABEL_JT.ADVISORY_ID
left join LABEL
on LABEL_ID = ADVISORY_LABEL_JT.LABEL_ID
where LABEL.NAME is NULL
Then our update query. To do this, select "Run update query" checkbox as seen in the screenshot below, and enter the following query:
insert into ADVISORY_LABEL_JT
SET ADVISORY_ID = ADVISORY_ID, LABEL_ID = "YOUR LABEL NUMBER"
where "YOUR LABEL NUMBER" is that label number we pulled from the URL earlier.
And remember to select the "Enabled" checkbox.
And finally, select the frequency you want this process to run.
And thats it! You're now applying a default label to your KB articles.