 |
| I promise this isn't going to be nearly as bad as the Employee Management thing. |
In this post, we'll assemble the Orchestrator runbook that...
- Converts the SCOM Alert/SCSM Incident to a Parent Incident.
- Queries your SCCM database for Malware Infected computers
- Creates Child Incidents that get attached to the Parent Incident
- Adds the SCCM context to each child incident such as Affected CI, malware type, severity, etc.
- Define the Primary User of the Computer as the Affected User of the respective Child Incident
At the conclusion of this post we'll have SCCM alerts, that trigger SCOM alerts, that in turn create an SCSM Parent Incident, that triggers an SCO runbook that creates Child Incidents for each infected computer. If you missed Part 1 that establishes the majority of this setup, you can go back to it
here. Otherwise, let's get on with it!
Get the "SCORCH Dev - SQL" Integration Pack
You'll need to get a Codeplex SQL Integration Pack for Orchestrator. I know you can write SQL queries out of box with SCO, but their results are less than ideal in terms of Orchestrator flow. This version will spit out the returned dataset in a much more consumable fashion for downstream activities. You can get this IP
here.
It also should be mentioned like many add-ons and tools in the System Center (Orchestrator) space; there are a host of options and as such, there a
lot of different IPs that let you work with SQL. I'm personally a fan of this one. It's free, it's simple, and it gets data the way I've come to expect in Orchestrator. If you have another one you prefer (or have paid for) go ahead and use that.
NOTE: Once imported, you
must deploy the IP to all SCO management servers, runbook servers, and any designers where you want to build (and in turn, potentially execute) from. Otherwise you must force the execution of the runbook on a particular server via the runbook's Properties -> Runbook Servers tab.
WHY: Presumably you have more than one Orchestrator server in the environment and have
not changed where the runbook executes - Orchestrator will execute the runbook on an available server in the SCO group. However before execution (as in before you see it in Log or Log History) SCO will verify the IP exists on all available servers. If the IP isn't found the runbook will never execute and there will be no recorded Log History of it ever occurring. However you will see an error in the Events view about how "Resources could not be found."). Not knowing this would probably lead you to a fair amount of frustration as SCSM invokes the runbook and SCO has no recorded history of it ever occurring.
Building the Runbook
1. Initialize Data
2. Get Relationship (Runbook to Incident)
3. Get Object (Incident)
4. Update Object (Incident - Is Parent = True)
5. Run .NET Script (Get the Current Date/Time)
6. Format Start Date/Time (Start Time)
7. Format End Date/Time (End Time)
8. Run SQL Query
9. Get Object (Windows Computer)
10. Get Object (Active Directory User)
11. New Object (Incident)
12. Create Relationship (Incident has Affected CI)
13. Create Relationship (Child Incident has Parent Incident)
14. Create Relationship (Incident has Affected User)
Here's a screenshot of the entire runbook
2. Get Relationship (Runbook to Incident)
3. Get Object (Incident).
SC Object GUID Equals Related Object Guid from Get Relationship (Runbook to Incident)
4. Update Object (Incident)
Object GUID from "Get Incident"
Is Parent = True
5. Run .NET Script (Get Current Date/Time)
I feel like this is the step where I'm bound to get someone saying "RIDICILOUS! NOT DOING IT!" - the SQL query we're going to execute against SCCM I need to have some kind of bounds. The most logical (to me at least) are a window of time where the malware was discovered by SCCM's Endpoint Protection. That said, a Run .NET Script was introduced into this runbook to get the current time, minutes into the past, and minutes into the future. For the purposes of this example you can see I said "10 minutes in the past" and "10 minutes into the future". Also, I know you see "20" there, but notice that the second line of the script updates the "currentDateTime" variable. You could just as easily change these values to a smaller window of time.
Finally, these values are published back out to the Orchestrator data bus so we can use them in our SQL query.
The two following activities take the outputs of the PowerShell script and format them into a time suited for our SQL Query. Both of these activities use the following Input and Output formats.
6. Format Start Date (Start Time)
7. Format End Date (End Time)
8. Run SQL Query
You can run the query with or without a global config. Personally, I like the global config route as I may want to use this connection in the future. Either way, you have to define a connection to your SCCM server's database and instance and your SCO service account will need
Read on the SCCM DB. Here's the setup for the Global config and how it
should look.
 |
| This is the syntax of what your Global Config for the SQL IP for your SCCM DB should look like. |
Then here's the SQL query you'll use in the Run SQL Query Activity. Don't forget to update the WHERE statement to include your Format Result/published data from the two previous time based activities!
SELECT DISTINCT dbo.Computer_System_DATA.Name00 AS Computer, dbo.Users.UserName, dbo.EP_Malware.ThreatName, dbo.EP_ThreatCategories.Category, dbo.EP_Malware.Process, dbo.EP_ThreatSeverities.Severity
FROM dbo.EP_Malware INNER JOIN
dbo.Users ON dbo.EP_Malware.UserID = dbo.Users.UserID INNER JOIN
dbo.Computer_System_DATA ON dbo.EP_Malware.MachineID = dbo.Computer_System_DATA.MachineID INNER JOIN
dbo.EP_ThreatCategories ON dbo.EP_Malware.CategoryID = dbo.EP_ThreatCategories.CategoryID INNER JOIN
dbo.EP_ThreatSeverities ON dbo.EP_Malware.SeverityID = dbo.EP_ThreatSeverities.SeverityID
WHERE (CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, dbo.EP_Malware.DetectionTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) > 'formatResultStartTimeGoesHere')
AND (CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, dbo.EP_Malware.DetectionTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) < 'formatResultEndTimeGoesHere')
And what exactly is this SQL query getting you ask? This SQL query returns the Computer name, the Primary User(name), Malware Name, Malware Category, Malware Process name, and the Malware Severity. We also take the time formats and ensure they are converted from UTC to
your local time zone. Now if you are really involved with SCCM you may have also immediately realized...
Wait a second. There is a native SCCM Activity for Orchestrator that lets me execute queries. Stored queries in SCCM no less! Why am I not using that?
Well...truthfully there is no good reason. You absolutely could do that. The thing is that has to be written in WQL which I'll be honest - is a skill I need to work on. As a result. It means you either "convert" my SQL query to a WQL query you store in SCCM or execute SQL against the DB from this IP. As you can see, I clearly opted for the SQL route. So if you are up for some conversion, go for it and leave a comment and help make this thing more awesome!
Credit goes to Robin's CM IT Blog and the post "PowerShell: Get SCCM Malware Detections to CSV file" as providing the basis for this query.
9. Get Computer
There are a few ways you can do this match. Depending on what your SQL result returns for computer names you can use one of the following properties and of course your choice of Relation field. Although I can't think of reason you wouldn't want to use "Equals" here.
Display Name, DNS Name, etc. equals fieldXX from your SQL query
11. Get Primary User
UserName equals fieldXX from your SQL query. I know I don't have any filters in the screen shot BUT the same exact rule/logic from the previous step applies. So, you'll need a filter!
You'll also notice something we
didn't do here and that's say/build something like "Get Relationship from Get Computer to Active Directory User." We just as easily could have as presumably you are syncing SCCM into SCSM - however, depending on the last time that connector ran the "Primary User" of a computer in SCSM maybe stale. As a result, this is why in the SQL query we pulled the Primary User directly from SCCM and are doing a match inside of a single activity within our runbook. That way we obtaining the most recent data possible.
12. New Object - Create Incident
Here's the properties I'll be setting for the Child Incidents that get created for each computer. Obviously pick and choose what you need. For example, if you already set the Classification Category within the SCSM Incident template there is no reason to set it here.
In the Description of our Incident, we can consume the fieldXX properties from our SQL query. So we can insert the Infected Machine name, Malware Type, Path, Severity, etc.
13. Create Relationship (Attach Computer to IR)
If you've never used Create Relationship, it may take some getting used to. But it makes a great deal of sense once you have. What's the source class/point of origin? An Incident. What do we want to do to that Incident? Attach a Windows Computer (Target Class). What is the Relationship Type between these two Classes? SCO only lets you pick possible relationships! Finally, define what the GUIDs are for each of these. The Source Guid is our Incident. The Target Guid is our Computer.
14. Create Relationship (Attach IR to Parent IR)
This one may feel weird if you've never defined this kind of relationship. Source and Target are Incidents. The Relationship Type is "Has Parent Work Item". Notice that the distinction between child and parent is made in the definition of these items Guids. My Source Guid is the Incident I created within the execution of this runbook. The Target Guid is the Incident that is the inception of this runbook's execution/the parent we defined upstream earlier.
15. Create Relationship (Set Primary User as Affected User)
Source Class: Incident
Target Class: Active Directory User
Relationship Type: Affected User
Source Object Guid: SC Object Guid from "Create Child Incident"
Target Object Guid: SC Object Guid from "Get Primary User"
That's That.
"So why did you make the SQL query the way you did? Wouldn't I then only have a single Parent Incident with a single Child?"
Sure. What about when the Malware Alert goes off for "Multiple Malware Detected" from SCCM? Etc. Etc. I'd say remove the DISTINCT portion of the query and then modify it to only get the duplicate machines returned from the query.
The point is - I absolutely recognize this solution isn't perfect. I recognize that parts are clearly open to interpretation given everyone's environment. If anything it introduces the need for at least one or two more child Runbooks that flank perhaps based on the Title of the parent Incident. Perhaps you've REALLY customized your SCCM malware alerting. You'll have more than just a single scenario to account for! More than anything, I hope this serves as something you can build off of and personalize for yourself and your environment. Perhaps the following
example is the first runbook that the Incident executes? Or maybe it only converts to a parent in the Multiple scenario and not the single scenario?
More ideas? Ways to improve? Good! Seize the day and build!
...or perhaps it means the SCCM team needs to update their management pack for SCOM so it just does all of this magic for us? :)
In the next post we'll go over the SCSM SQL query for your data warehouse to start building reports to get things like...
- Which Affected User's get the most malware?
- Which Affected User Departments get the most malware?
If you are versed in your SCSM DW these reports may sound incredibly obvious given we have defined all the data we need too. If not, I'm writing the post!
