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 ...Old Solution Link update
After migrating 14200 build our old solution link format is not accessible, in this situation one of our customer referred more number of old solution links in some solutions description, in this scenario we need to update the old format link to new ...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 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", ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...