Query to show vendor and products list (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Basic details:
select sdo.name "Vendor Name", componentdefinition.componentname "Product", componenttype.componenttypename "Product Type" from vendordefinition
left join sdorganization sdo on sdo.org_id=vendordefinition.vendorid
left join componentvendor on componentvendor.vendorid= vendordefinition.vendorid
left join componentdefinition on componentdefinition.componentid=componentvendor.componentid
left join componenttype on componenttype.componenttypeid=componentdefinition.componenttypeid
Along with vendor contact details:
select sdo.name "Vendor Name", aci.emailid "Vendor Email ID", apa.DOOR_NO"Door Number",
apa.STREET"Street",
apa.LOCATION"Location",
apa.LANDMARK"Land Mark",
apa.CITY"City",
apa.POSTALCODE"Postal Code",apa.state "State", apa.country "Country", currency.currencyname "Currency", componentdefinition.componentname "Product", componenttype.componenttypename "Product Type" from vendordefinition left join sdorganization sdo on sdo.org_id=vendordefinition.vendorid
left join componentvendor on componentvendor.vendorid= vendordefinition.vendorid
left join componentdefinition on componentdefinition.componentid=componentvendor.componentid
left join componenttype on componenttype.componenttypeid=componentdefinition.componenttypeid
left join currency on vendordefinition.currencyid=currency.currencyid
left join sdorgcontactinfo sdoc on sdo.org_id=sdoc.org_id
left join aaacontactinfo aci on sdoc.contactinfo_id = aci.contactinfo_id
left join sdorgpostaladdr spa on spa.org_id=sdo.org_id
left join aaapostaladdress apa on apa.POSTALADDR_ID=spa.POSTALADDR_ID
group by sdo.name, aci.emailid, apa.DOOR_NO, apa.STREET,
apa.LOCATION,
apa.LANDMARK,
apa.CITY,
apa.POSTALCODE,apa.state, apa.country, currency.currencyname, componentdefinition.componentname, componenttype.componenttypename order by sdo.name
New to ADSelfService Plus?
Related Articles
Query to show to Assets with their IP and MAC Address (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", networkinfo.IPADDRESS "IP Address", networkinfo.macaddress "MAC Address", product.COMPONENTNAME AS ...Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...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 pull Account ID and Site ID (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports Tab >> New Query Report and run the below query. For Postgres & MS SQL select adef.org_id "Account ID", adef.org_name "account",sdef.siteid "Siteid", sdo.name "Site" from sitedefinition ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...