Need Database view for Unread KB's informations.

[Manthira Moorth]

Hi Experts,

I have one Requirement which is related to get the Unread Knowledge Articles by using the kb_use table.

This needs to be archive by database base view.

As per my knowledge KB_USE table is creating new records when we viewed that particular article by the user.

Kindly assist me to built the logics of database view.

My Old Ideas:

-----------------

 Tried the Query BR for that database View for the particular logged user by comparing with the kn_use by getting        the  unread KB. In the database view i added only the Kb_knowledge table.

   Expectations:  I can able to see that any user who viewed or not viewed the articles. 

   Eg: User -A viewed 500 KB and Not View 20 KB totally 700 KB' are there.

Summary:

------------

I need database View with unread Kb. I know the data will be very high but that is the requirement.

Question 2:

--------------

In the KB_USE table when we get Viewed false information for that KB?

[Manthira Moorth]

Hi @Shivalika ,

Thank you for your approach which you given the Code where i can use the Query BR.

But as per your code it will show only the logged in users who is not viewed the KB's (i.e) Unread KB.

This is Already archived in my OLD Ideas. 

Expectations:  I can able to see that any user who viewed or not viewed the articles. 

 Eg: User -A viewed 500 KB and Not View 200 KB totally 700 KB' are there.

       User -B viewed 250 KB and Not View 450 KB totally 700 KB' are there.

Kindly let me know if you have any other approaches🙂.

[Medi C]

Hi @Manthira Moorth,

I doubt this would be achievable by Database Views as there is no direct SQL access and you need to get the count to retrieve the count of KBs read by each user.

One alternative solution if it would work for you, since you can get all this data easily by scripting, would be easier to have an on demand scheduled job to get the data you need either from the logs or to trigger a notification (Possibly to have an attachment with a structured table as you desire)

[Manthira Moorth]

Hi @Medi C ,

Thank you for your approach.

But this is only for the report purpose. So custom tables is not possible as of now.

[Medi C]

Hi @Manthira Moorth,

As suggested on my previous reply. Please try the following script. as a Scripts-Background / Fix Script / On Demand Scheduled Job.

It would good to set limit for the users and try it first as scripts - background, if it works, then you can make a scheduled job to get the results for all users. As Scheduled Job should not have any timeout.

var grUser = new GlideRecord("sys_user");
grUser.addActiveQuery();
grUser.query();

var log = "";
while (grUser.next()) {
    var readArticles = getKbReadArticles(grUser.sys_id);
	var unReadArticles = getKbUnReadArticles(readArticles);

	log += "User: " + grUser.getDisplayValue() + " viewed " + readArticles.length + " KB and Not View " + unReadArticles.length + " KB. Total of " + (readArticles.length + unReadArticles.length) + " KB are there.\n";
}

gs.info(log);

function getKbReadArticles(user) {
    var grKbUse = new GlideRecord("kb_use");
    grKbUse.addQuery("user=" + user + "^article.workflow_state=published");
    grKbUse.query();

    var readKbArticles = [];

    while (grKbUse.next()) {
		var kbNumber = grKbUse.article.number.toString()
        if (readKbArticles.indexOf(kbNumber) == -1) {
            readKbArticles.push(kbNumber);
        }
    }
    return readKbArticles;
}

function getKbUnReadArticles(readArticles) {
    var grKnowledge = new GlideRecord("kb_knowledge");
    grKnowledge.addQuery("workflow_state=published");
    grKnowledge.query();

    var unReadKbArticles = [];


    while (grKnowledge.next()) {
        var kbNumber = grKnowledge.number.toString();

        if (readArticles.indexOf(kbNumber) == -1 && unReadKbArticles.indexOf(kbNumber) == -1) {
            unReadKbArticles.push(kbNumber);
        }
    }

    return unReadKbArticles;
}

Results:

I have just tried it as Scripts - Background and I got the following:

[Medi C · Accepted solution]

Hi @Manthira Moorth,

I hope you are doing well! Did my solution work for you?