Malware Flagging and Reporting - SCCM, SCOM, SCSM, and SCO (part 2)

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...

1. Converts the SCOM Alert/SCSM Incident to a Parent Incident.
2. Queries your SCCM database for Malware Infected computers
3. Creates Child Incidents that get attached to the Parent Incident
4. Adds the SCCM context to each child incident such as Affected CI, malware type, severity, etc.
5. 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

1. Initialize Data

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...

1. Which Affected User's get the most malware?
2. 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!

Read More

Execute Native Powershell Version 4 in Orchestrator 2012/2012 R2

 

Yeah. No joke. That isn't a typo.

Let me repeat that because it bears repeating.

You can run Powershell v4, 64bit, natively in Orchestrator 2012.

NO TUNNELING.
NO SESSIONS.
NO MORE RETURN VARIABLES AS OBJECTS FROM A REMOTE SESSION NONSENSE.


How? Lies you say?!

Will Udovich (of Cireson) and his finding of a SINGLE blog on the internet from "TechGuy" Michael Seidel mention this little registry hack. One blog? ONE BLOG?!?! Grab anything you can that makes loud noises and make sure EVERYONE knows about this.

That's it! ONE FREAKING REGISTRY KEY!!! Now Run .NET Script will let you execute things like "Get-ADUser" natively!!!

I could not believe what I was reading until I fired up my development environment, added this registry key and watched it work exactly like any one would expect it to.


Also, in the event it wasn't clear...my reaction to all of this can be summarized by the opening gif.

Read More

Malware Flagging and Reporting - SCCM, SCOM, SCSM, and SCO (part 1)

Yeah. Buckle up.

Alright. So I had this idea and given how much all of the System Center components rely on one another it stood to reason I could execute this idea of mine relatively simply. OH HOW WRONG WAS I!

Scenario: Configuration Manager (SCCM) does malware reporting/alerting (sort of). If you've configured alerting in SCCM, it sends out an email from spoofed/fake email address out to select parties you've determined. But, you also leverage Service Manager and...

1. Really would rather have malware get flagged as an Incident and assigned out to someone to investigate or
2. You want it in SCSM so you can continue to develop and consume all of your reporting from a single point or
3. You want an Incident as it relates to a Configuration Item or
4. You want to open Incident on behalf of the Primary User of the PC

All of those things are valid reasons I think you'd want to start piping this through SCSM. So I thought to myself...

"Well. This sounds easy. I'll just get the SCCM management pack for SCOM, hook 'em up, forward alerts from SCOM into SCSM. Done."

Here's the problem though. SCCM has this rule (and the following example applies to all of them) called "Malware detection alert: Malware found." and in SCCM it monitors a collection, and the SCCM alert contains the PC and malware type. When you get SCOM hooked up to monitor this alert, when the alert forwards from SCCM to SCOM. The SCOM alert description says "Please see the Configuration Manager console for more details." Which means when this gets forwarded to SCSM the Incident description says "Please see the Configuration Manager console for more details." And what's worse? Their is only ONE Affected CI in the SCSM Incident and it's your SCCM server(s).

So I'd say this is less than ideal. Because now all I've done is add unnecessary process and if anything require someone to do more work. I want ALL THE THINGS in Service Manager! Here's the harder news to digest - this isn't an accident. SCCM designed the management pack like this. So if anything it's probably a feature request that I hope this blog post drives them to implement. Alright. That's probably wishful thinking on my part.

I THINK NOT!
So before you simply give up and move on. What about this idea...

SCCM forwards alerts to SCOM. SCOM forwards the Alert into SCSM. The SCSM Incident template for this contains a SCO runbook that executes a SQL query against SCCM. The result set is the machines infected. The runbook converts the Incident to a Parent and creates Child Incidents for each infected computer.

And that's exactly what I did. No time to waste. Let's build it.


Configure SCCM to forward Malware Alerts to SCOM
If you haven't already done so. You'll need to get the SCCM management pack for SCOM installed. Then you'll need to get it configured to correctly poll these alerts.

1. Obviously - import the management pack into SCOM
2. Make sure you have SCOM agents deployed to SCCM server(s)
3. Most importantly - Make sure you have enabled Agent Proxy under the Security tab on each managed SCCM server.
1. Basically, you are saying the agents can act as a proxy (or if you prefer a "front") to SCCM managed devices. It allows SCOM to see through SCCM to it's "managed things".

Once you've done that, you may have to let SCOM/SCCM bake awhile before you start seeing the SCCM alerts show up as Not Monitored alerts inside of the SCOM view "Alerts from Configuration Manager". Once they appear though, you may wonder...

"So why aren't they monitored? They are all green. What gives?"

The reason they aren't monitored is because the SCCM management pack by default has almost everything disabled by default.

"That's lame. Why would they do that?"

Think of it this way. If it was all on by default, SCOM would vomit everywhere. If this is the first management pack you've experienced where everything is off by default your reaction may now be...

"Oh. Well. Yeah. That does make more sense."

 So how do we start monitoring these things?

Configure SCOM to listen to SCCM
Quite simple. Create an override!

1. Right click on the SCCM alert you want SCOM to monitor
2. Navigate to Open, and then Health Explorer for Alert You Selected
3. Now (left) click on "Entity Health - Malware outbreak alert..."
4. Now click Overrides
5. Select "Override the Monitor"
6. Select "For a Specific Class of the Object: ConfigMgr Alert: Malware Alert"

You should now seen your collections you monitoring in SCCM. Select the collection you want SCOM to poll from SCCM.

Finally you are placed on the Override screen...

Here you can see the Default Value for Enabled is False and we are setting the Override value to True.

If you haven't done so already in your initial setup of the SCCM management pack, now would be a good time to create a SCOM management pack to store all of your SCCM modifications. Once you've done that, save this override in your new Unsealed SCCM Management Pack for SCOM.

Repeat this process of enabling rules and saving them to your Unsealed SCCM Management Pack for SCOM for as many things as you want SCOM to monitor from SCCM.


Create a SCOM group for your SCCM Alerts
Next, let's head into the Authoring section of SCOM. Select Groups and let's create a new group called "Configuration Manager Alerts" we'll save it of course in our recently created Unsealed SCCM Management Pack for SCOM. This way our overrides and groups are stored in the same place.

Now while you can get fancy with some Dynamic membership. I'm going to use Explicit Membership and grab my Malware Alerts. Just go to Add and under the default scope of "Object" just type Malware and search.

Then just go ahead and add the rules you want (and more importantly overrode to Enabled True).


Add a new Rule to your SCOM/SCSM Subscription
Now you can open up your SCOM subscriptions and open up your SCSM connector/forwarder.

1. Create a new Subscription
2. Unselect all Groups and then select the one we just created - Configuration Manager Alerts
3. Leave "Forward alerts from all targets automatically..." the default option selected.
4. Hit Next
5. Leave the default SCOM criteria selected
6. Done

Now, since this is just the initial walkthrough it goes without saying (and I'm typing so that works) that you can always go back, modify, and tweak these rules beyond belief. For the purposes of this walkthrough I'm just keeping things simple.


Create the SCSM Incident Template
Next let's move into Service Manager to create an Incident template so that when our Alert gets forwarded from SCOM into SCSM we can consistently apply the same Incident. More importantly, when we go to start building reporting we have a consistent shape to our data.

1. Head into Library
2. Go to Templates
3. Create a new Incident template
4. Store the template in...well...either a management pack you already have and are keeping all of your Incident templates in or create a new Management Pack to store it in. Anyone versed in SCSM will appreciate the thought that goes into grouping your Templates into properly defined management packs. So Step 4 here is a bit open ended since everyone's SCSM environment/administration is always just a little bit different.

This new Template we'll want to assign a few things out of the gate
1. Impact
2. Urgency
3. Classification
4. Support Group and/or Assigned To - because after all that's kind of the point of this entire post of mine!

Save it. You're done in SCSM...for now.



In the next post (available here) we'll go over the crux of this entire thing...

1. Creating the Orchestrator Runbook that converts the Incident to a Parent Incident.
2. Queries your SCCM database for Malware Infected computers
3. Creates Child Incidents that get attached to the Parent
4. Adds the SCCM context to each child incident such as Affected CI, malware type, severity, etc.
5. Define the Primary User of the Computer as the Affected User of the respective Child Incident

System Center Icons obtained from System Central Central. Here's the direct link.

Read More