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
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 the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...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 ...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", ...