Query to find out the users who are updated through LDAP import (MSSQL & PGSQL)
This should be handy during Audit. Execute the queries under Reports->New Query Report
select ch.ciid "CI ID", ci.ciname "CI Name", chd.attribute "Attribute", chd.newvalue "New Value", chd.oldvalue "Old Value", ch.editmode "Edit Mode", LONGTODATE(ch.Operationtime) "Updated Time", ch.username "Updated By" from cihistory CH LEFT JOIN cihistorydetails CHD ON ch.historyid=chd.historyid LEFT JOIN CI ON ch.ciid=ci.ciid where ch.editmode='sdp.history.LDAP.import' and operationtime >= <from_today> AND operationtime <= <to_today> order by 1
Below one can be used for customized dates,
select
ch.ciid "CI ID", ci.ciname "CI Name", chd.attribute "Attribute",
chd.newvalue "New Value", chd.oldvalue "Old Value", ch.editmode "Edit
Mode", LONGTODATE(ch.Operationtime) "Updated Time", ch.username "Updated
By" from cihistory CH LEFT JOIN cihistorydetails CHD ON
ch.historyid=chd.historyid LEFT JOIN CI ON ch.ciid=ci.ciid where
ch.editmode='sdp.history.LDAP.import' and operationtime >=DATETOLONG('2019-07-01 00:00:00') AND operationtime <= DATETOLONG('2019-07-31 00:00:00') order by 1
If required, other date filters can also be used instead of the highlighted ones, kindly refer below,
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
How to import user additional fields using LDAP
In order to import additional fields or map any other fields in AD to the system fields, a row needs to be updated with the appropriate values You can follow the below steps 1. Take a trimmed backup of the database. 2. Connect to the Database 4. Run ...Query to show last updated field (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...Query to find out who created Accounts (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Execute the below queries under Reports->New Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down ...LDAP Import fails if you use a CN other than Users..
Issue Description: When we import users through LDAP, it seems like if we use the CN = Users as a Base DN its working fine. If you use any other CN apart from that its showing error message like "FAILURE : Error occurred while trying to connect with ...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 ...