OpsMgr (SCOM) - Goodbye Import-Module OperationsManager

Hello SDK!

We all suffer about the same.
Import-Module OperationsManager just takes too long - 13 seconds!

 $(Get-Date)  
 Import-Module OperationsManager  
 $(Get-Date)  
 Tuesday, April 26, 2016 3:42:35 PM  
 Tuesday, April 26, 2016 3:42:48 PM  

So, if you use powershell in your subscriptions in OpsMgr, to affect some customfileds with extra data, well ... it could be a problem if you have a lot of 'Impor-Modules' going on.

So, i decided to put myself working on 'how to (not) use operations manager powershell module).

First things first.
Load your SDK. (DLL's)

 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.EnterpriseManagement.OperationsManager.Common") | Out-Null  
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.EnterpriseManagement.Core') | Out-Null  
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.EnterpriseManagement.OperationsManager') | Out-Null  
 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.EnterpriseManagement.Runtime') | Out-Null  

Now, let's open up a connection to your MS.

 $MGConnSetting = New-Object Microsoft.EnterpriseManagement.ManagementGroupConnectionSettings($env:computername)  
 $MG = New-Object Microsoft.EnterpriseManagement.ManagementGroup($MGConnSetting)  

Now, inside this new object ($MG) you have it all, and i mean it, ALL!
Let's say you want to list a specific list of alerts based on a specific criteria.
Well, untill now you used get-scomalert cmdlet ... now you use this :

 $newTime = (Get-Date).AddHours(-24)  
 $Criteria = New-Object Microsoft.EnterpriseManagement.Monitoring.MonitoringAlertCriteria("ResolutionState = 0 AND Severity >= 1 AND TimeRaised > `'$newTime`'")  
 $MG.GetMonitoringAlerts($Criteria)  

Well, the difference ? 13 seconds!
Old-way :

 $(Get-Date)  
 Import-Module OperationsManager  
 $newTime = (Get-Date).AddHours(-24)  
 $Criteria = New-Object Microsoft.EnterpriseManagement.Monitoring.MonitoringAlertCriteria("ResolutionState = 0 AND Severity >= 1 AND TimeRaised > `'$newTime`'")  
 $Alerts = Get-SCOMAlert -Criteria $Criteria  
 $(Get-Date)  
 Tuesday, April 26, 2016 3:54:06 PM  
 Tuesday, April 26, 2016 3:54:19 PM  

New-way :

 $(Get-Date)  
 $newTime = (Get-Date).AddHours(-24)  
 $Criteria = New-Object Microsoft.EnterpriseManagement.Monitoring.MonitoringAlertCriteria("ResolutionState = 0 AND Severity >= 1 AND TimeRaised > `'$newTime`'")  
 $Alerts = $MG.GetMonitoringAlerts($Criteria)  
 $(Get-Date)  
 Tuesday, April 26, 2016 3:54:32 PM  
 Tuesday, April 26, 2016 3:54:32 PM  

Other examples :

 Get-SCOMClassInstance  

in our new way :

 $ClassCriteria = New-Object Microsoft.EnterpriseManagement.Configuration.MonitoringClassCriteria("DisplayName = 'Test Class'")  
 $MonitoringClass = $MG.GetMonitoringClasses($ClassCriteria)  

And related MonitoringObjects ? (Get-SCOMMonitoringObject) ?

 $MG.GetMonitoringObjects($MonitoringClass[0])  

OpsMgr (SCOM) - Visual Studio Report (System Drive Available Space)

Challanged by a local PFE friend to start using Visual Studio to create my reports, and keeping in mind i'm no SQL expert, and no Report-Builder greatest fan, i tried to make a costumers request on specific report they wanted (C: Drive available Space)!

So, since i'm kind new to Visual Studio, and to this scenario in general, i decided to make this how to, and obviously, share it here.

So! Let's open Visual Studio and click NEW!

And then :



You'll have a blank report, so we need to add a datasource (from where you'll get your data from!)
(Since we're using OpsMgrDW database...)



Then, add a dataset (it's a query!) :)

This one i used!

 SELECT DISTINCT VME.Path AS Agente, DISKs.SystemDiskUsed AS FreePercentage, DISKs.SystemDiskFreeMB AS FreeMB  
 FROM Perf.vPerfDaily AS PERF   
 INNER JOIN vManagedEntity AS VME ON PERF.ManagedEntityRowId = VME.ManagedEntityRowId   
 INNER JOIN vPerformanceRuleInstance AS PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId   
 INNER JOIN vPerformanceRule AS PR ON PR.RuleRowId = PRI.RuleRowId   
 LEFT OUTER JOIN  
 (  
     SELECT ME.Path AS Computer,  
     AVG(CASE WHEN PR.CounterName = '% Free Space' THEN PERF.AverageValue END) AS SystemDiskUsed,   
     AVG(CASE WHEN PR.CounterName = 'Free Megabytes' THEN PERF.AverageValue END) AS SystemDiskFreeMB  
     FROM Perf.vPerfDaily AS PERF   
     INNER JOIN vPerformanceRuleInstance AS PRI ON PRI.PerformanceRuleInstanceRowId = PERF.PerformanceRuleInstanceRowId  
     INNER JOIN vPerformanceRule AS PR ON PR.RuleRowId = PRI.RuleRowId  
     INNER JOIN vManagedEntity AS ME ON PERF.ManagedEntityRowId = ME.ManagedEntityRowId  
     WHERE (PERF.DateTime > DATEADD(Day, - 7, GETDATE()))   
     AND (PR.CounterName = '% Free Space'   
     OR PR.CounterName = 'Free Megabytes') AND (PRI.InstanceName = 'C:')  
     GROUP BY ME.Path, PRI.InstanceName  
 ) AS DISKs ON VME.Path = DISKs.Computer  
 WHERE (PERF.DateTime > GETUTCDATE() - 2)   
 AND (PR.CounterName LIKE '% Free Space')  
 AND (PRI.InstanceName LIKE 'C:')  
 ORDER BY FreeMB  

Then, add a matrix, with that dataset.



Add a logo you may want (Company logo perhaps!):

Change the expression value for the FreeMB (Just a tweak to look better!)

So, now, let's add some colour to it with a new row with a 'progress bar'.

First, add the row:

Then, insert the 'Data Bar'

For the 'progress bar' you added, change the values of its expression like : 

And the values of the series fill propreties, just to make it RED when is bellow a threshold you define.

As you did for the FreeMB field, do the same for the Free% :

Run the preview and you'll get :

And that's it! :)

Save the project, copy the RDL file into your Reporting Services server and schedule it!

In the future i'll make other posts like this, now that i'm a Visual Studio rookie :)

Cheers :)

OpsMgr (SCOM) - Powershell Get Servers in DA

I got this need to upgrade this script ( [SCOM & Orchestrator] - Alert Forwarding ) to include a new CustomField in the alarm properties with the DA name the object belongs to!
So i wondered if somehow was possible to know to which DA a specific server belongs to, and came up with this 'idea' (poweshell script!):

 Import-Module OperationsManager  
 $list = Get-SCOMClass -DisplayName "DA - NAME - HERE" | Get-SCOMClassInstance | %{$_.GetRelatedMonitoringObjects()} | %{$_.GetMonitoringRelationshipObjects()} | select SourceMonitoringObject, TargetMonitoringObject  
 ForEach ($i in $list) {  
     If ( $i.TargetMonitoringObject.DisplayName -eq 'SERVER_FQDN' ) {   
         $DA += $i.SourceMonitoringObject.DisplayName + ' || '  
     }  
}  

Enjoy :)

SCCM - Inventory SQL Query (Server Info)

The guy who manages CMDB wanted to update some CMDB fields, like Total Memory, CPU, Storage and OS Information on every registered server.

So, since SCCM is our configuration and also our "inventory" application, perhaps we could do some SQL-Query to save the day.

 SELECT DISTINCT   
    [RSYS].Netbios_Name0 AS [CI Name],  
      ROUND([OS].TotalVirtualMemorySize0 / CAST(1024 AS FLOAT),0) AS [Total RAM (GB)],  
      CASE WHEN ISNULL(SUM(CAST([LDISK].Size0 AS INT)),0) / 1024 > 0  
      THEN ISNULL(SUM(CAST([LDISK].Size0 AS INT)),0) / 1024  
      ELSE 1  
      END AS [Total Storage (GB)],  
      ROUND(SUM(CAST([LDISK].FreeSpace0 AS FLOAT) ) / CAST(1024 AS FLOAT),1) AS [Total Free Space (GB)],  
      [CPU].Name0 AS [CPU Info],  
      CASE WHEN [CPU].NumberOfCores0 IS NULL THEN '0' ELSE [CPU].NumberOfCores0 END AS Cores,  
      CASE WHEN [CPU].NumberOfLogicalProcessors0 IS NULL THEN '0' ELSE [CPU].NumberOfLogicalProcessors0 END AS [Logical Processors],  
      COUNT([CPU].ResourceID) AS [Number of CPUs],  
      [OS].Caption0 AS [OS]  
  FROM v_R_System RSYS  
      JOIN v_GS_PROCESSOR CPU on RSYS.ResourceID = CPU.ResourceID  
    JOIN v_GS_LOGICAL_DISK LDISK on RSYS.ResourceID = LDISK.ResourceID   
      JOIN v_GS_OPERATING_SYSTEM OS ON RSYS.ResourceID = OS.ResourceID  
 WHERE ISNULL([RSYS].Obsolete0, 0) <> 1  
 AND [LDISK].Size0 IS NOT NULL  
 AND ( [LDISK].DeviceID0 = 'C:' OR [LDISK].VolumeName0 = '/' )  
 GROUP BY [RSYS].Netbios_Name0, [OS].Caption0, [OS].TotalVirtualMemorySize0, [CPU].Name0, [LDISK].DeviceID0, [LDISK].VolumeName0, [LDISK].Size0,[CPU].NumberOfCores0,[CPU].NumberOfLogicalProcessors0  
 ORDER BY [RSYS].Netbios_Name0  

This will give you something like this :

See you soon!

OpsMgr (SCOM) - Create Performance Charts in Powershell

It might be usefull for people who don't have Datazen or just want to create and manage their own costumized HTML views, or perhaps, for fun!

Powershell is one of the things i like the most while exploring everything that SCOM (and other System Center tools) can give to you, it just makes everything you want, a way or another.

So.

What about creating this in a Powershell script ?

This is the script that does all the magic :

!!NOTE!! : For the most problematic servers in specific groups i've i retrieve memory, cpu, and disk usage performance data, but, you change this logic as well as the performance counters.

Please attempt to the comments in-line so you can understand the logic :)

 [void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")  
 Import-Module OperationsManager  
 New-SCOMManagementGroupConnection -ComputerName "opsmgr.server.local" #Change here :)  
 $script:scriptpath = "C:\PS\OpsMgr_PerfCharts\images"  
   
 # Foreach Group above list the most problematic agents   
   
 $MyGroups = @()  
 $MyGroups += Get-SCOMGroup -DisplayName 'UNIX/Linux Computer Group'  
 $MyGroups += Get-SCOMGroup -DisplayName 'All Windows Computers'  
 $newTime = (Get-Date).AddHours(-24)  
 $Criteria = New-Object Microsoft.EnterpriseManagement.Monitoring.MonitoringAlertCriteria("TimeRaised > `'$newTime`'")  
 $TransversalDepth = [Microsoft.EnterpriseManagement.Common.TraversalDepth]::Recursive  
 $TopAgents = @()  
 Foreach ( $Group in $MyGroups ) {  
   $TopObjAlerts = ($Group.GetMonitoringAlerts($Criteria , $TransversalDepth) | Group-Object MonitoringObjectPath | Sort-Object count -descending | select -first 5 Values).Values  
   Foreach ($i in $TopObjAlerts) {  
     $TopAgents += ($i -split ";")[0]  
   }  
   $TopAgents = $TopAgents.Split(";",[System.StringSplitOptions]::RemoveEmptyEntries)  
 }  
 $Count = 0  
 $Query = ""  
 Foreach ($Agent in $TopAgents) {  
   If ($TopAgents.Count -eq $Count ) {  
     $Query += "'" + $agent + "'" + ","  
   } Else { $Query += "'" + $agent + "'" }  
   $Count++  
 }  
   
 # Create SQL Query with the most problematic agents criteria   
   
 $Query = $Query -replace "''","','"  
   
 $SqlQuery = "  
 SELECT          
     Path,  
     DateTime,  
     CASE   
     WHEN vpr.ObjectName = 'Processor Information' THEN 'Processor'  
     WHEN vpr.ObjectName = 'LogicalDisk' THEN 'Logical Disk' ELSE vpr.ObjectName END as ObjectName,  
 CASE   
     WHEN vpr.CounterName = 'PercentMemoryUsed' OR vpr.CounterName = '% Available Memory' THEN '% Used Memory'   
     WHEN vpr.CounterName = '% Free Space' THEN '% Used Space'  
     ELSE vpr.CounterName END as CounterName,  
 CASE   
     WHEN len(vpri.InstanceName) < 1 THEN '_Total' ELSE vpri.InstanceName END as InstanceName,  
 CASE   
     WHEN vpr.CounterName = '% Available Memory' or vpr.CounterName = '% Free Space' THEN 100 - pvpr.AverageValue ELSE pvpr.AverageValue END as AverageValue,  
 100.00 as ComparisonValue  
 FROM   
     Perf.vPerfDaily pvpr WITH (NOLOCK)   
     inner join vManagedEntity vme WITH (NOLOCK) on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId   
     inner join vPerformanceRuleInstance vpri WITH (NOLOCK) on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId   
     inner join vPerformanceRule vpr WITH (NOLOCK) on vpr.RuleRowId = vpri.RuleRowId   
 WHERE  
     Path in (  
         select   
             unix.DisplayName  
         from   
             vManagedEntity unix WITH (NOLOCK)   
             join vManagedEntityManagementGroup unixMG WITH (NOLOCK) ON unix.ManagedEntityRowId = unixMG.ManagedEntityRowId  
             join vManagedEntityType unixType WITH (NOLOCK) on unix.ManagedEntityTypeRowId = unixType.ManagedEntityTypeRowId  
         where   
             unixMG.ToDateTime Is Null   
             and (unixType.ManagedEntityTypeSystemName = 'Microsoft.Unix.Computer'   
             or unixType.ManagedEntityTypeSystemName = 'Microsoft.Windows.Computer' )  
             AND vpr.ObjectName <> 'Process'  
             AND unix.DisplayName IN ( $Query )  
     )  
 AND vpr.CounterName in ('% Processor Time','PercentMemoryUsed','% Available Memory','% Free Space')  
 AND vpri.InstanceName in ('_Total','C:','/','')  
 AND DATEDIFF(DAY, pvpr.DateTime, GETDATE()) < 8  
 "  
   
 $SqlQuery > $env:TEMP\query.sql  
   
 $SqlQuery = Get-Content $env:TEMP\query.sql  
   
 # Create connection to SCOMDW Database   
   
 $script:SQLServer = "SCOMDBSERVER\SCOMDW"  
 $script:SQLDBName = "OperationsManagerDW"  
 $script:connString = "Data Source=$SQLServer;Initial Catalog=$SQLDBName;Integrated Security = True"  
 $script:connection = New-Object System.Data.SqlClient.SqlConnection($connString)  
 $connection.Open()  
 $sqlcmd = $connection.CreateCommand()  
 $sqlcmd.CommandText = $SqlQuery  
 $results = $sqlcmd.ExecuteReader()  
 $script:table = new-object “System.Data.DataTable”  
 $script:table.Load($results)  
   
 # Lets GRAPH!  
   
 $Servers = ($table | Select -Unique Path).Path  
 # 1 Graph foreach Agent   
 Foreach ( $Server in $Servers ) {  
   $Server = ($Server -split '\.')[0]  
   $GraphName = $Server  
   # Create CHART   
   $GraphName = New-object System.Windows.Forms.DataVisualization.Charting.Chart  
   $GraphName.Width = 900  
   $GraphName.Height = 300  
   $GraphName.BackColor = [System.Drawing.Color]::White  
   # CHART Title  
   [void]$GraphName.Titles.Add("$Server - Performance Data")  
   $GraphName.Titles[0].Font = "Arial,10pt"  
   $GraphName.Titles[0].Alignment = "MiddleCenter"  
   # CHART Area and X/Y sizes   
   $chartarea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea  
   $chartarea.Name = "ChartArea"  
   $chartarea.AxisY.Title = "$CounterName"  
   $chartarea.AxisX.Title = "Time"  
   $chartarea.AxisY.Maximum = 100  
   $chartarea.AxisY.Interval = 10  
   $chartarea.AxisX.Interval = 1  
   $GraphName.ChartAreas.Add($chartarea)  
   # CHART Legend  
   $legend = New-Object system.Windows.Forms.DataVisualization.Charting.Legend  
   $legend.name = "Legend"  
   $GraphName.Legends.Add($legend)  
     # Line Colors  
     $num = 0  
   $LineColours = ('DarkBlue','Brown','DarkMagenta')  
   # Create 'n' SERIES (lines) for each COUNTER  
   Foreach ($Counter in $Counters) {  
     $LineColor = $LineColours[$num]  
     [void]$GraphName.Series.Add("$Counter")  
     $GraphName.Series["$Counter"].ChartType = "Line"  
     $GraphName.Series["$Counter"].BorderWidth = 2  
     $GraphName.Series["$Counter"].IsVisibleInLegend = $true  
     $GraphName.Series["$Counter"].chartarea = "ChartArea"  
     $GraphName.Series["$Counter"].Legend = "Legend"  
     $GraphName.Series["$Counter"].color = "$LineColor"  
     ForEach ($i in ($table | ? { $_.ObjectName -eq $Counter -and $_.Path -like "$Server*" }) ) {   
       $GraphName.Series["$Counter"].Points.addxy( ($i.DateTime -split ' ')[0] , $i.AverageValue )   
     }  
     $num++ # Next Color :)  
   }  
   $GraphName.SaveImage("$scriptpath\$Server.png","png") # Save the GRAPH as PNG  
 }  

Have fun! :)

OpsMgr (SCOM) - Alerts per group SQL Query (+) Datazen Dashboard

Since i showed up how to get it in a last post, a PFE friend of mine noticed something it migh consern, and to be honest i didn't pointed out because to me was not an issue, but, i understand it might me to some of you.

It doesn't give you a recursive membership alerts.
My older post will only give you object specific alerts on that group, "nothing else"!
So, i started to think on how i could help me (and you!) out with this "issue", and came up with this ideia.

First, i've showed how to do this by powershell.
All alerts for specific group, in a specific time range, and for specific severity - this were my filters.
But, how can i "translate" powershell into SQL ?
SQL-Profiler!
Basically when you run a powershell cmd-let like :
Get-SCOMAlert -Criteria (...)
You're connecting into OperationsManager database and making a query.

So, the query goes as follow:

First, i create a temp table i can put my "groups".

DECLARE @TMP_GROUP_TABLE table(BaseManagedEntityId uniqueidentifier, DisplayName varchar(50));  
 INSERT INTO @TMP_GROUP_TABLE  
 SELECT BaseManagedEntityId, DisplayName FROM basemanagedentity WITH (nolock)  
 WHERE DisplayName = ('Group Name Here')  
 OR DisplayName = ('Other Group')  
 OR DisplayName = ('Other Group')  
 OR DisplayName = ('Other Group')  
 OR DisplayName = ('Other Group')  
 OR DisplayName = ('Other Group')

Then, the magic query - it's already made up to join the temp table and add the group name in the end of it :) (Promise i'll make a post about SQL-Profiler!)

 DECLARE @LanguageCode1 varchar(3)  
 DECLARE @LanguageCode2 varchar(3)  
 DECLARE @ParentManagedEntityId uniqueidentifier  
 DECLARE @ResolutionState0 nvarchar(max)  
 DECLARE @Severity0 nvarchar(max)  
 DECLARE @TimeRaised0 datetime  
 SET @LanguageCode1='ENU' ; SET @LanguageCode2=NULL  
 SET @ResolutionState0=N'0' ; SET @Severity0=N'1'; SET @TimeRaised0='2016-04-04 00:00:00'  
 SELECT DISTINCT [AlertView].[Id],[AlertView].[Name],[AlertView].[Description],[AlertView].[MonitoringObjectId],[AlertView].[ClassId],[AlertView].[MonitoringObjectDisplayName],[AlertView].[MonitoringObjectName],  
                 [AlertView].[MonitoringObjectPath],[AlertView].[MonitoringObjectFullName],[AlertView].[IsMonitorAlert],[AlertView].[ProblemId],[AlertView].[RuleId],[AlertView].[ResolutionState],[AlertView].[Priority],  
                 [AlertView].[Severity],[AlertView].[Category],[AlertView].[Owner],[AlertView].[ResolvedBy],[AlertView].[TimeRaised],[AlertView].[TimeAdded],[AlertView].[LastModified],[AlertView].[LastModifiedBy],  
                 [AlertView].[TimeResolved],[AlertView].[TimeResolutionStateLastModified],[AlertView].[CustomField1],[AlertView].[CustomField2],[AlertView].[CustomField3],[AlertView].[CustomField4],[AlertView].[CustomField5],  
                 [AlertView].[CustomField6],[AlertView].[CustomField7],[AlertView].[CustomField8],[AlertView].[CustomField9],[AlertView].[CustomField10],[AlertView].[TicketId],[AlertView].[Context],[AlertView].[ConnectorId],  
                 [AlertView].[LastModifiedByNonConnector],[AlertView].[MonitoringObjectInMaintenanceMode],[AlertView].[MonitoringObjectHealthState],[AlertView].[ConnectorStatus],[AlertView].[RepeatCount],  
                 [MT_Computer].[NetbiosComputerName],[MT_Computer].[NetbiosDomainName],[MT_Computer].[PrincipalName],[AlertView].[LanguageCode],[AlertView].[AlertParams],[AlertView].[SiteName],  
                 [AlertView].[MaintenanceModeLastModified],[AlertView].[StateLastModified],[AlertView].[TfsWorkItemId],[AlertView].[TfsWorkItemOwner],  
                 [TEMP].DisplayName AS GroupName  
 FROM dbo.fn_AlertView(@LanguageCode1, @LanguageCode2) AS AlertView   
 LEFT OUTER JOIN dbo.MT_Computer ON AlertView.TopLevelHostEntityId = MT_Computer.BaseManagedEntityId  
 INNER JOIN dbo.RecursiveMembership AS RM ON AlertView.MonitoringObjectId = RM.ContainedEntityId   
 INNER JOIN @TMP_GROUP_TABLE AS TEMP ON RM.ContainerEntityId = TEMP.BaseManagedEntityId  
 WHERE (AlertView.[ResolutionState] = @ResolutionState0   
 AND AlertView.[Severity] >= @Severity0   
 AND AlertView.[TimeRaised] > @TimeRaised0)   
 AND (((RM.ContainerEntityId IN ( SELECT BaseManagedEntityId FROM @TMP_GROUP_TABLE ) )))   
 ORDER BY [AlertView].[LastModified] DESC  

Since you got your data into place, let's make the Datazen Dashboard for your teams!

This is a 5 minutes dashboard, you can edit the query to set some "alert thresholds" and create a more "KPI" oriented alert view dashboad in Datazen.

Cheers,

OpsMgr (SCOM) - Alerts per group SQL Query

A few days ago, i showed up how to get SCOM alerts for a certain group in Powershell.
Now i needed to put it in DataZen and i could, but it's more simple to get data by SQL, so, the query i came up with is this :

 DECLARE @TMP_GROUP_TABLE table(groupname varchar(50));  
 insert into @TMP_GROUP_TABLE   
 values('Group #1'), -- List  
      ('Group #2'), -- Of   
      ('Group #3'), -- Groups   
      ('Group #4'), -- You  
      ('Group #5') -- Want  
 SELECT   
     s.displayName as [Group],   
     CASE WHEN t.Path IS NULL THEN t.DisplayName ELSE t.path END AS [CI],  
     av.AlertName as [Alert Name],  
     av.AlertDescription as [Description],  
     count(av.AlertName) as [AlertCount],  
     ResolutionState, RaisedDateTime,av.Severity  
 FROM vrelationship r   
      inner join vManagedEntity s on s.ManagedEntityRowId = r.SourceManagedEntityRowId   
      inner join vManagedEntity t on t.ManagedEntityRowId = r.TargetManagedEntityRowId   
      inner join Alert.vAlert av on av.ManagedEntityRowId= t.ManagedEntityRowId  
      inner JOIN Alert.vAlertDetail adv on av.AlertGuid =adv.AlertGuid   
      inner JOIN Alert.vAlertResolutionState arsv on av.AlertGuid =arsv.AlertGuid   
      inner JOIN Alert.vAlertParameter apv on av.AlertGuid =apv.AlertGuid   
 WHERE   
      -- I choose 7 days, you can put a value as you like  
      RaisedDateTime >=DATEADD(day,-7,GETDATE())  
      and s.DisplayName IN ( SELECT groupname FROM @TMP_GROUP_TABLE )  
      -- Filter only for CRIT and WARN alarms  
      AND av.Severity >= 1  
 group by s.displayName,t.displayname,av.AlertDescription,ResolutionState,RaisedDateTime,av.Severity,av.AlertName,t.Path  
 order by AlertCount desc  

This is good so you can create a nice Datazen dashboard to keep teams up with their alarms (you can put their objects inside respective groups).

Feel free to criticize, no SQL master at all (lol!)

SCCM - Free Space in C Drive SQL-Query

Sometimes it can happen - you getting out of disk space in your C: drive, so usually i run this query to know, if the collection ID i'm patching or deploying something, is enough.

 SELECT   
     SYS.Name,  
     LDISK.DeviceID0,  
     LDISK.VolumeName0,  
     LDISK.FreeSpace0/1024 as [Free space (GB)],  
     LDISK.Size0/1024 as [Total space (GB)]  
 FROM v_FullCollectionMembership SYS  
      JOIN v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID  
      JOIN v_R_System RSYS ON SYS.ResourceID = RSYS.ResourceID  
 WHERE  
     LDISK.DeviceID0 = 'C:'  
     AND LDISK.DriveType0 = 3  
     AND LDISK.Size0 > 0  
     AND SYS.CollectionID = 'COLLECTION_ID_HERE'  
 ORDER BY SYS.Name, LDISK.DeviceID0  

Enjoy :)

OpsMgr (SCOM) - Supported Network Devices

An everyday question is :
"Is this network device model "y" from vendor "x" supported ? - Can you make it discoverable?"

Well, it is if it's here:

https://www.microsoft.com/en-us/download/details.aspx?id=26831

It's a list of all supported network devices for SCOM.

This is a keep in mind to spread over your network mates :)