Query to Export solutions (MSSQL & PGSQL)

Query to Export solutions (MSSQL & PGSQL)

Tested in Build PGSQL (14300) or MSSQL (14306)

Run this query under Reports->New Query Report and export it to the desired format

SELECT solution.solutionid "Solutionid", KB_Topics.TOPICNAME " Topic",Solution.TITLE "Subject ",Solution.DESCRIPTION "Contents",solutioninfo.noofhits   
"Views",Creator.FIRST_NAME "Created By",longtodate(SolutionInfo.CREATEDTIME) "Created Time",Updater.FIRST_NAME "Last Modified By",longtodate
(SolutionInfo.LASTUPDATEDTIME) "Last Modified Time",Sol_StatusDefinition.STATUSNAME "Status" 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 Sol_StatusDefinition ON Solution.STATUSID=Sol_StatusDefinition.STATUSID
LEFT JOIN solutionugmapping sum ON solutioninfo.solutionid=sum.solutionid
LEFT JOIN Usergroups ug ON sum.usergroupid=ug.id
LEFT JOIN Usergroupsaccmapping ugam ON ug.id=ugam.usergroupid
LEFT JOIN AccountDefinition ad ON ugam.accountid=ad.org_id

Build no over 14000 use the below query.

SELECT solution.solutionid "Solutionid", KB_Topics.TOPICNAME " Topic",Solution.TITLE "Subject ",Solution.DESCRIPTION "Contents",solutioninfo.noofhits   
"No of View",Creator.FIRST_NAME "Created By",longtodate(SolutionInfo.CREATEDTIME) "Created Time",Updater.FIRST_NAME "Last Modified By",longtodate
(SolutionInfo.LASTUPDATEDTIME) "Last Modified Time",Sol_StatusDefinition.STATUSNAME "Status" 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 Sol_StatusDefinition ON Solution.STATUSID=Sol_StatusDefinition.STATUSID
LEFT JOIN solutionugmapping sum ON solutioninfo.solutionid=sum.solutionid
LEFT JOIN Usergroups ug ON sum.usergroupid=ug.id
LEFT JOIN Usergroupsaccmapping ugam ON ug.id=ugam.usergroupid
LEFT JOIN AccountDefinition ad ON ugam.accountid=ad.org_id
                New to ADManager Plus?

                  New to ADSelfService Plus?