How to auto-create change request(s) for unplanned changes on Assets

How to auto-create change request(s) for unplanned changes on Assets

This post describes the use of a python script to auto-create change requests for unplanned modifications on Assets using Custom Schedules.

This script is applicable only for builds prior to 11138.

UseCase: Multiple assets are inventoried in ServiceDesk plus and if an asset was to be modified, a change request should be associated with it. In the case that the asset is modified sans the change request being raised. The following script will spot the modifications and raise the request, thus we can ensure that the change process is applied for the specific modification. 

Execution steps:
1. Go to Reports--> New Query Report and run the below query and save this report.

select resources.resourcename as "assetname", 
cihisdtls.ATTRIBUTE "attribute",
max(cihisdtls.OLDVALUE) "oldvalue",
max(cihisdtls.newvalue) "newvalue",
LONGTODATE(max(cihistory.operationtime)) "operationtime",
(case WHEN cihistory.EDITMODE='sdp.history.csv.import' THEN 'Csv Import' WHEN cihistory.EDITMODE='sdp.admin.api.key.head' THEN 'API' WHEN cihistory.EDITMODE='sdp.history.AD.import' THEN 'AD Import' WHEN cihistory.EDITMODE='sdp.history.LDAP.import' THEN 'LDAP Import' ELSE 'Manual' END) "mode",
max(cihistory.username) "user" from resources
inner join componentdefinition on resources.componentid=componentdefinition.componentid
inner join componenttype on componentdefinition.componenttypeid=componenttype.componenttypeid
inner join resourcetype on componenttype.resourcetypeid=resourcetype.resourcetypeid
left join ci cit on resources.ciid=cit.ciid
left join cihistory on cit.ciid=cihistory.ciid
left join audithistory on resources.resourceid=audithistory.workstationid
left join ci on cihistory.ciid = ci.ciid
left join cihistorydetails cihisdtls on cihistory.historyid = cihisdtls.historyid
where cihistory.operationtime>=<from_today> and cihistory.operationtime<=<to_today> and resources.resourceid not in(select assetid from changetoasset cta left join changedetails cd on cta.changeid=cd.changeid where cd.createdtime >= <from_today> and cd.createdtime <= <to_today>) group by resources.resourcename, cihisdtls.ATTRIBUTE,cihistory.EDITMODE

This will give you the list of modifications on assets without change requests associated.If change requests have to be created for modifications occurred on a specific asset/ci type, use the below query,

select resources.resourcename as "assetname", 
cihisdtls.ATTRIBUTE "attribute",
max(cihisdtls.OLDVALUE) "oldvalue",
max(cihisdtls.newvalue) "newvalue",
LONGTODATE(max(cihistory.operationtime)) "operationtime",
(case WHEN cihistory.EDITMODE='sdp.history.csv.import' THEN 'Csv Import' WHEN cihistory.EDITMODE='sdp.admin.api.key.head' THEN 'API' WHEN cihistory.EDITMODE='sdp.history.AD.import' THEN 'AD Import' WHEN cihistory.EDITMODE='sdp.history.LDAP.import' THEN 'LDAP Import' ELSE 'Manual' END) "mode",
cihistory.username "user" from resources
inner join componentdefinition on resources.componentid=componentdefinition.componentid
inner join componenttype on componentdefinition.componenttypeid=componenttype.componenttypeid
inner join resourcetype on componenttype.resourcetypeid=resourcetype.resourcetypeid
left join ci cit on resources.ciid=cit.ciid
left join cihistory on cit.ciid=cihistory.ciid
left join audithistory on resources.resourceid=audithistory.workstationid
left join ci on cihistory.ciid = ci.ciid
left join citype on ci.citypeid=citype.typeid
left join cihistorydetails cihisdtls on cihistory.historyid = cihisdtls.historyid
where cihistory.operationtime>=<from_today> and cihistory.operationtime<=<to_today> and resources.resourceid not in(select assetid from changetoasset cta left join changedetails cd on cta.changeid=cd.changeid where cd.createdtime >= <from_today> and cd.createdtime <= <to_today>)  and citype.typename="server" group by resources.resourcename, cihisdtls.ATTRIBUTE,cihistory.EDITMODE

Replace 'server' in the above query with name of the desired CI type.

2. Download and place the script file in the ManageEngine/ ServiceDesk/ integration/custom_scripts folder.
3. Update the required values in the script with the help of the comments provided within.
4. Configure the schedule under Admin--> Custom Schedule like shown below.



A short guide on setting up python is available here.