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>