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 get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...