Predict Affected/Impacted Configuration Item(s)

[AdhocAdam]

Invent a way to parse emails that are about a Configuration Item and relate it to a Work Item.

[AdhocAdam]

While the SCOM integration is only in its infancy, it seems doable to:

• Forward an email trigger from SCOM
• For the connector to parse it (e.g. the Alert GUID), look up the Alert, grab the SCOM Configuration Item
• Lookup the Configuration Item against SCSM
• Create the New Work Item based on the email and the related discovered Config Item

This however relies on the management pack existing across SCOM and SCSM and of course both products existing in any environment.

The above idea however does not address the ultimate goal of this request which is to either intentionally create a Work Item with a related Config Item as defined by the Sender or inferred from the Sender.

[AdhocAdam]

In the spirit of the current ML functionality (originally raised on #112), the plan is to extend that by honoring custom in house developed classes, ANY 3rd party management packs, and of course stock Microsoft MPs whether they ship native SCSM or native SCOM. While only the first pass, I think it's best shot at ensuring usability across environments of any shape or size.

For example, the following SCSM DW SQL Query will prep an Incident data set that came in through email, with Affected Config Items. Depending on the resulting data set, it's possible further preperation may be required to prevent overfitting of data such as ignore all Child Incidents, include SCOM generated Incidents since they reliably produce Affected CIs, etc.

SELECT DISTINCT
    ir.title as 'Email_Subject',
    ir.Description as 'Email_Description',
    ic.EnumTypeId as 'Case_Subject',
    tq.EnumTypeId as 'Queue_Name',
    'ir' as 'Case_CaseType',
    STUFF((SELECT DISTINCT ',' + cast(ci2.BaseManagedEntityId as varchar(38))
            FROM IncidentDim as ir2
                INNER JOIN EntityDim as e2 on ir2.entitydimkey = e2.entitydimkey
                INNER JOIN workitemdim as wi2 on e2.entitydimkey = wi2.entitydimkey
                INNER JOIN workitemaboutconfigitemfactvw as wiaci2 on wi2.workitemdimkey = wiaci2.workitemdimkey
                INNER JOIN configitemdim as ci2 on wiaci2.workitemaboutconfigitem_configitemdimkey = ci2.configitemdimkey
            WHERE ir2.Id = ir.Id
            FOR XML PATH('')), 1, 1, '') as [Affected_Config_Items]
FROM IncidentDim as ir
    INNER JOIN IncidentTierQueuesvw as tq on ir.TierQueue_IncidentTierQueuesId = tq.IncidentTierQueuesId
    INNER JOIN IncidentClassification as ic on ir.Classification_IncidentClassificationId = ic.IncidentClassificationId
    INNER JOIN EntityDim as e on ir.entitydimkey = e.entitydimkey
    INNER JOIN workitemdim as wi on e.entitydimkey = wi.entitydimkey
    INNER JOIN workitemaboutconfigitemfactvw as wiaci on wi.workitemdimkey = wiaci.workitemdimkey
    INNER JOIN configitemdim as ci on wiaci.workitemaboutconfigitem_configitemdimkey = ci.configitemdimkey
WHERE ir.createddate > '1-1-2020'
    and wiaci.DeletedDate is null
    and ir.Source like '%email%'
    and ir.Title is not null
    and ir.Description is not null

group by ir.Id,
    ir.title,
    ir.Description,
    ic.EnumTypeId,
    tq.EnumTypeId

This query would return something to the effect of:

Email_Subject	Email_Description	Case_Subject	Queue_Name	Case_CaseType	Affected_Config_Items
issue with computer	i'm having trouble logging in	5f89436e-6373-40f9-86c8-c3e3d6768578	d15b74b6-8cfb-46d2-948c-eacf802ef095	ir	8d6d205a-6a14-4801-bc0d-ffb5cfad89e0,7c059164-fdc0-4b03-bffe-730d8027bce3

Then for Service Requests, the same query is taken from above will slight modifications made. All in all, they are pretty much the identical query.

SELECT DISTINCT
    sr.title as 'Email_Subject',
    sr.Description as 'Email_Description',
    sa.EnumTypeId as 'Case_Subject',
    sg.EnumTypeId as 'Queue_Name',
    'sr' as 'Case_CaseType',
    STUFF((SELECT DISTINCT ',' + cast(ci2.BaseManagedEntityId as varchar(38))
            FROM servicerequestdim as sr2
                INNER JOIN EntityDim as e2 on sr2.entitydimkey = e2.entitydimkey
                INNER JOIN workitemdim as wi2 on e2.entitydimkey = wi2.entitydimkey
                INNER JOIN workitemaboutconfigitemfactvw as wiaci2 on wi2.workitemdimkey = wiaci2.workitemdimkey
                INNER JOIN configitemdim as ci2 on wiaci2.workitemaboutconfigitem_configitemdimkey = ci2.configitemdimkey
            WHERE sr2.Id = sr.Id
            FOR XML PATH('')), 1, 1, '') as [Affected_Config_Items]
FROM servicerequestdim as sr
    INNER JOIN ServiceRequestSupportGroup as sg on sr.SupportGroup_ServiceRequestSupportGroupId = sg.ServiceRequestSupportGroupId
    INNER JOIN ServiceRequestArea as sa on sr.Area_ServiceRequestAreaId = sa.ServiceRequestAreaId
    INNER JOIN EntityDim as e on sr.entitydimkey = e.entitydimkey
    INNER JOIN workitemdim as wi on e.entitydimkey = wi.entitydimkey
    INNER JOIN workitemaboutconfigitemfactvw as wiaci on wi.workitemdimkey = wiaci.workitemdimkey
    INNER JOIN configitemdim as ci on wiaci.workitemaboutconfigitem_configitemdimkey = ci.configitemdimkey
WHERE sr.createddate > '1-1-2020'
    and wiaci.DeletedDate is null
    and sr.Source like '%email%'
    and sr.Title is not null
    and sr.Description is not null

group by sr.Id,
    sr.title,
    sr.Description,
    sa.EnumTypeId,
    sg.EnumTypeId

Again, similiar results are returned only now centered around Service Requests	Email_Subject	Email_Description	Case_Subject	Queue_Name	Case_CaseType	Affected_Config_Items
access request	Can i get a license assigned to my account to use the application?	05ad7825-3443-4cd4-b7f9-59c88e8236e2	590cd1c8-b27e-425d-ada5-c695e87f12ae	sr	b73721ca-5409-4837-8200-0f69bf9a0622

Following this the current Azure Machine Learnig Studio Experiments would need to be altered:

1. SCSM Email Work Item Classification

   • the "Apply SQL Transformation" step near the top which follows your Ticket dataset needs to change to include the new Affected_Config_Items column. This can be done with the following: select Affected_Config_Items,Case_CaseType, Queue_Name,Case_Subject, Email_Subject || ' ' || Email_Description as Email_Text from t1;
   • from left to right after the "Feature Hashing" step, the first 3 "Select Columns in Dataset" need to add a new column to exclude which is: Affected_Config_Items
   • a wholly new "branch" (e.g. all of the steps that follow the "Feature Hashing" step) need to be made for Config Items. From left to right, just duplicate the 3rd. This new branch...
     • "Select Columns in Dataset" needs to exclude Case_Subject, Case_CaseType, Queue_Name
     • "Filter Based Feature Selection" should select "Affected_Config_Items" as its column
     • The two "Split Data" steps should select "Affected_Config_Items" as their column
     • "Tune Model Hyperparameters" should select "Affected_Config_Items" as its column

2. SCSM Email Work Item (Create Web service w/ trained models)

   • similiar to the first experiment, need a new Score Model from "Feature Hashing". This connects to a new "Execute R Script"
   • the new "Execute R Script" needs to be modified on Line 16 to update the column names being used to the following: colnames(data.set) <- c("Affected_Config_Items","Affected_Config_Items_Probability")
   • daisy chain the new "Execute R Script" into another "Add Columns" to ultimatley distill all 4 branches into the single "Web Service Output"

Following this, the Get-AMLWorkItemProbability function would be updated introducing two new key/value pairs on the returned object:

    $probabilityMatrix | Add-Member -MemberType NoteProperty -Name AffectedConfigItem -Value $probabilityResults[6]
    $probabilityMatrix | Add-Member -MemberType NoteProperty -Name AffectedConfigItemConfidence -Value (($probabilityResults[7] -as [decimal]) * 100)

Finally, when New-WorkItem optionally calls Get-AMLWorkItemProbability it would be able to parse the returned Config Item(s) and relate them to the Work Item as Impacted Config Items assuming minimum confidence thresholds were hit. e.g.

$amlProbability = Get-AMLWorkItemProbability -EmailSubject "account" -EmailBody "i think something is locking my account out. can you see if everything is ok?"
$amlProbability.AffectedConfigItem.Split(",") | %{New-SCSMRelationshipObject -Relationship $wiAboutCIRelClass -Source $newWorkItem -Target $_ -Bulk @scsmMGMTParams}

[AdhocAdam]

To be released with #180 and #181