1. Query Basics

1. Query Basics


Basic Query:

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       aau.FIRST_NAME AS "Requester",
       ti.FIRST_NAME AS "Technician",
       cd.CATEGORYNAME AS "Category",
       std.STATUSNAME AS "Request Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (wo.ISPARENT='1')
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth>

Select Statement:

The SELECT statement is used to query the database and retrieve the fields that you specify. You can select as many fields (column names) as you want, or use the asterisk symbol "*" to select all fields. 

For adding columns in the query use the below link.


Conditions:

The WHERE clause specifies which data values or rows will be returned or displayed, based on the criteria you specify. 

For adding conditions in the query use the below link.


For adding date filter in the query use the below link.


Order by:

The ORDER BY clause organizes/sorts the data by the field specified. 

For organizes/sorts the data by the field specified in the query use the below link.







      • Related Articles

      • Advanced Analytics - FAQ & Troubleshooting tips

        Troubleshooting tips This document is listing of customer facing issues,  Advanced analytics is common name for both Zoho reports and Analytics plus.  First setup we have to get with customer that recreate issue and generate support log ...
      • How to connect to the AssetExplorer database?

        To find the database connected to your installation, open Command Prompt with elevated permission, navigate to [AE-Home]\bin directory and invoke the batch file 'changeDBServer.bat'. [AE-Home] is Drive:\ManageEngine\AssetExplorer\ directory. ...
      • How to connect to the ServiceDesk database?

        Open command prompt on the server and change the directory to MangeEngine\ServiceDesk\bin, invoke the batch file 'changeDBServer.bat' For MSSQL database  Connect to the query analyzer of the SQL server and execute the queries. For PostgreSQL database ...
      • How to change the database from MySQL/PostgreSQL to MSSQL

        The steps mentioned below are applicable only if you migrate the DB from PgSQL to MSSQL. This means that the application server is going to remain the same. The data alone is going to be transferred to MSSQL DB from the existing PgSQL DB.    For ...
      • Data Migration - ESM Build

        Requirement: All the tickets that are assigned to specific sites has to be moved to a corresponding instances that are built for the sites. For example: Site A ticket from IT instance should be moved to Instance A. The tickets once moved should be ...