Ever wanted to run reports on the most viewed knowledge base articles, highest KB contributors, topics and more? Now you can, with some minor customisation. Here are the steps that will show you how. 

Step 1:

Launch ServiceDesk Plus database and click New and choose New table / Import Data.

Step 2:

Click Local and Cloud Databases.

Step 3:

Fill the database server details for the connection under the Connect to Local and Cloud Databases section and click Next. (In this case, I have chosen MS SQL)

Step 4:

Choose Custom Query radio button and paste the custom query inside the query editor. Click Next to proceed.

Step 5:

Type the table name and choose the proper datatypes for the columns in the preview. Click Next to proceed.

Step 6

Schedule the import and click Create to complete the import.

Below are the queries,

PGSQL:- 

select so.solutionid as "KB ID",title " KB article title",aa.first_name as "Created By", aa1.first_name as "LastUpdated By",so.solutiontype as "Type", CASE soi.ispublic when true THEN 'Public' Else 'Private' End as "View Type", to_timestamp(soi.createdtime/1000) as "Created On",to_timestamp(soi.lastupdatedtime/1000) as "LastUpdated On",soi.noofhits as "Views",st.statusname as "Status" from solution so left join solutioninfo soi on soi.solutionid=so.solutionid left join SOL_STATUSDEFINITION st on st.statusid=so.statusid left join AaaUser aa on aa.user_id=soi.createdby left join SdUser sd on aa.user_id=sd.userid left join Aaauser aa1 on aa1.user_id=soi.lastupdatedby 

MSSQL :-

select so.solutionid as "KB ID",title " KB article title",aa.first_name as "Created By", aa1.first_name as "LastUpdated By",so.solutiontype as "Type",CASE when soi.ispublic='true' THEN 'Public' Else 'Private' End as "View Type", DATEADD(MILLISECOND, soi.createdtime % 1000, DATEADD(SECOND, soi.createdtime / 1000, '19700101')) as "Created On",DATEADD(MILLISECOND, soi.lastupdatedtime % 1000, DATEADD(SECOND, soi.lastupdatedtime / 1000, '19700101')) as "LastUpdated On",soi.noofhits as "Views",st.statusname as "Status" from solution so left join solutioninfo soi on soi.solutionid=so.solutionid left join SOL_STATUSDEFINITION st on st.statusid=so.statusid left join AaaUser aa on aa.user_id=soi.createdby left join SdUser sd on aa.user_id=sd.userid left join Aaauser aa1 on aa1.user_id=soi.lastupdatedby