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 M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Query to export All Requesters from All Accounts (MSSQL & PGSQL)

                      Tested in builds from PGSQL (14300) or MSSQL (14306) Execute the below query under Reports > New Query Reports: SELECT AaaUser.USER_ID,AaaUser.FIRST_NAME "FullName",AaaLogin.NAME "LoginName",AaaLogin.DOMAINNAME "Domain",AaaContactInfo.EMAILID ...

                    • Query to show Average response time for Category (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...

                    • Query to show Category, Sub category and Item (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: Excute the below query under Report > New Report > Query Report > Copy, Paste the below and Run the query. SELECT CategoryDefinition.CATEGORYNAME "Category Name", ...

                    • Query to export account based solutions (PGSQL & MSSQL )

                      Tested in Build 14600 pgsql SELECT solution.solutionid "Solutionid", KB_Topics.TOPICNAME " Topic",Solution.TITLE "Subject ",Solution.DESCRIPTION "Contents",solutioninfo.noofhits "No of View",Creator.FIRST_NAME "Created ...

                    • Query to show technician hop count (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...

                      Related Products