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 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='ttd'
Note : Replace the highlighted with Account Name
New to ADSelfService Plus?
Related Articles
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 ...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 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 get Requesters details for each account (Not Working)
Doesnt work in builds from PGSQL (14300) or MSSQL (14306) ERROR: missing FROM-clause entry for table "sdu" PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", ...Query to show count of tickets account based (PGSQL)
Tested in Build PGSQL (14300) 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 ...