Query to display the solution details, including helpful/not helpful ratings and the number of comments added
QUERY:
SELECT solution.solutionid "Solution Id", Solution.TITLE "Title",KB_Topics.TOPICNAME "Topic", Sol_StatusDefinition.STATUSNAME "Status",solutioninfo.ispublic "Visibility",solutioninfo.noofhits "View(s)", Creator.FIRST_NAME "Created By", longtodate(SolutionInfo.CREATEDTIME) "Created On", Updater.FIRST_NAME "Last Modified By", longtodate(SolutionInfo.LASTUPDATEDTIME) "Last Modified Time", owner.FIRST_NAME "Solution Owner", SolutionStatistics.likescount "Helpful", SolutionStatistics.dislikescount "Not Helpful", SolutionStatistics.totalcommentscount "Comments",Solution.solutiontype "Type",longtodate(Solutioninfo.expiry_date) "Expiry Date" FROM Solution
INNER JOIN SolutionInfo ON Solution.SOLUTIONID=SolutionInfo.SOLUTIONID
LEFT JOIN KB_Topics ON Solution.TOPICID=KB_Topics.TOPICID
LEFT JOIN AaaUser Updater ON SolutionInfo.LASTUPDATEDBY=Updater.USER_ID
LEFT JOIN AaaUser Creator ON SolutionInfo.CREATEDBY=Creator.USER_ID
LEFT JOIN Solution_Keywords ON Solution.SOLUTIONID=Solution_Keywords.SOLUTIONID
LEFT JOIN solutionownerdefinition solown ON solown.solutionid=solution.solutionid
LEFT JOIN aaauser owner on owner.user_id=solown.ownerid
LEFT JOIN Sol_StatusDefinition ON Solution.STATUSID=Sol_StatusDefinition.STATUSID
LEFT JOIN SolutionStatistics ON Solution.Solutionid=SolutionStatistics.Solutionid
ORDER BY 1
OUTPUT:
Applicable to both PGSQL and MSSQL DBs
New to ADSelfService Plus?
Related Articles
Close comments
This report is used to find the close comment added by requester. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000627781192 SELECT wo.WORKORDERID ...Query to show both task comments and worklog comments ( MSSQL )
Tested in MSSQL build (14306) SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category", "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS ...Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...