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?

[Shivalika]

Hello @Manthira Moorth 

Please use below SQL query in database view and it should work just fine - 

SELECT 

    k.sys_id AS kb_article_id,

    k.number AS kb_number,

    k.short_description AS kb_title,

FROM kb_knowledge k

LEFT JOIN kb_use ku 

    ON k.sys_id = ku.kb_knowledge 

    AND ku.user = gs.getUserID();

WHERE ku.sys_id IS NULL;

How It Works

The LEFT JOIN ensures that all KB articles are included.

The WHERE ku.sys_id IS NULL condition filters out articles that do not have a matching view record in kb_use, meaning the user hasn’t read them yet.

For your 2nd question - the viewed field in KB_USE table is never explicitly set to false. It might mean there is some custom implementation running to set it to false after some duration or the user hasn't fully accessed the article. The key is record is only inserted in KB_USE table once it's viewed by the user.

Please mark my answer as helpful if it helped you in anyway 🙂 

Linkedin - https://www.linkedin.com/in/shivalika-gupta-540346194

YouTube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=xiIA16WHp4K_WJZo

[Manthira Moorth]

Hi @Shivalika ,

Could you please tell me where i needs to use this SQL Quries. I tried in the Where Clause.

I tried like this but not working. Not sure where you asking me to put this.

Kindly let me know do i needs to use same query BR for this database view by adding this above query?

[Shivalika]

Hello @Manthira Moorth , 

Actually by that select I meant - columns that you could add from both kb_knowledge and KB_USE tables when you add. 

For the left join condition, when you chose left join there should be a "JOIN CONDITION" field where you can give the query I gave for join - 

k.sys_id = ku.kb_knowledge

ku.user = gs.getUserID(); 

In the WHERE part you need to give condition 

ku.sys_id IS NULL

This will ensure that only unread articles come up. 

And yes there will be script for QUERY BR also but it will be different. However this should work directly in database view. 

Regards, 

Shivalika 

LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

YouTube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=jeXR57sYV-_9YyNO

Please mark my answer as Helpful and accept as solution if it helped you in anyway 🙂 

[Shivalika]

Hello @Manthira Moorth 

Incase you change your mind by completely using database views, here's the scripting you can run at backend and see once - 

(function() {

    var userID = gs.getUserID();

    var unreadArticles = [];

    var allKBs = new GlideRecord('kb_knowledge');

    allKBs.addQuery('active', true);

    allKBs.query();

    while (allKBs.next()) {

        var kbUse = new GlideRecord('kb_use');

        kbUse.addQuery('kb_knowledge', allKBs.sys_id);

        kbUse.addQuery('user', userID);

        kbUse.query();

        if (!kbUse.next()) {

            unreadArticles.push({

                id: allKBs.sys_id.toString(),

                title: allKBs.short_description.toString()

            });

        }

    }

    return

unreadArticles;

})();

I ran it some time back and I was able to fetch results. 

You can go with any of the solutions I proposed. 

Please mark any of the solutions as acceptable or helpful if it helped you in anyway 🙂 

Regards, 

Shivalika 

LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

YouTube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=lYqR4o0W7XqsuWcN