Knowledge Doc Rating

Products

• CA Service Desk Manager

Issue/Introduction

Service Desk admin wants to know how to tell which user left which rating on a particular Knowledge Document -- this tech doc explains how the data are stored in mdb and what queries admin can use to query them.

Environment

Release : 17.1/17.3/17.3

Component : SDM KNOWLEDGE MANAGEMENT

Resolution

Service Desk admin would need to look and query these tables: event_log, bu_trans, session_log and ca_contact 

1. query event_log to get the doc event

    select numdata1, session_id from event_log where event=16 and kd_id=the-id-of-the-doc

please notice that kid_id the-id-of-the-doc is the specific knowledge doc id and admin can query this id in skeletons table. for example, select id from skeletons where title="the-title-of-doc"

2. with numdata1 from step1, you can see the doc rating with this query

  select bu_rating, user_slv from bu_trans where id=numdata1

this is the mapping of the doc raiting:

yes+very helpful -->bu_rating=4, user_slv=1
yes+somewhat helpful -->bu_rating=2, user_slv=1
yes+not helful at all-->bu_rating=0, user_slv=1
no+very helpful -->bu_rating=4,user_slv=0
no+somewhat helpful-->bu_rating=2,user_slv=0
no+not helful at all -->bu_rating=0,user_slv=0

3. in order to see who submitted the rating, admin would need to query session_log and ca_contact table with session_id from step 1

   select contact from session_log where id=session_id

   select first_name, last_name from ca_contact where contact_uuid=contact-from-last_query

These steps and queries are useful for admin to construct some reporting queries. For example, Jaspersoft reporting.

Even without some reporting tool like Jaspersoft, step1 and step2 will give admin some idea how a specific knowledge doc is rated.