Query to get solutions account based

Query to get solutions account based


PGSQL & MSSQL:


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 where ad.org_name='New'

Note: Please replace the exact Account name in the query in place of the highlighted.



      • Related Articles

      • Export solutions

        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    ...
      • Query to get Requesters details for each account

        PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", au.first_name "First Name", au.last_name "Last Name", sdu.isvipuser "VIP User", sdu.employeeid "Employee ...
      • Auto suggest solutions to technicians

        Use Case When a request is created, solutions can be auto suggested to technicians under Resolution Tab with a bulb icon. Based on the subject keywords the available solutions will be suggested to the technician which will help them narrow down the ...
      • Query to show count of tickets account based

        Please go to Reports-New Query Report and execute this report. select concat(ad.org_name, '   - ', tmp.wo_count), w.workorderid "Request ID",to_timestamp((w.createdtime)/1000)::TIMESTAMP "Created time",wotodesc.FULLDESCRIPTION AS ...
      • Query to show Solutions and its associated fields

        MSSQL: SELECT solution.solutionid "Solution ID" , max(Solution.TITLE) "Solution Title",  max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords",  max(owner.FIRST_NAME) "Solution Owner",  ...