Have you ever wanted to check if AD Integration is enabled for an Agent using a SQL query? Today I wanted to do that and I created the next SQL Query. You may find it usefull. /Find all Agent where AD Integration is configuredRun on OperationsManager DatabaseAuthor: Stefan Stranger/SELECT bme.DisplayName, mths.ActiveDirectoryManagedFROM MT_HealthService mths INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId WHERE ActiveDirectoryManaged = 1ORDER BY DisplayName /Find all Agent where AD Integration is not configuredRun on OperationsManager DatabaseAuthor: Stefan Stranger/SELECT bme.DisplayName, mths.ActiveDirectoryManagedFROM MT_HealthService mthsINNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityIdWHERE ActiveDirectoryManaged = 0ORDER BY DisplayName

Today I stumbled on this video about PowerWF (a visual PowerShell Development tool) creating an OpsMgr MP. The video shows how PowerWF can be used with PowerShell scripts and converts it to a workflow,and creating a MP with the data for OpsMgr.   Visual PowerShell Development with PowerWF Studio makes Microsoft’s PowerShell accessible to everyone. PowerWF Studio allows users to drag-and-drop PowerShell commands to create powerful workflows that are easier to understand and modify than conventional scripts.  

Source: Edge.technet.com In this video on Microsoft Technet, Ananthanarayan Sundaram (Senior Product Manager – System Center) is talking about some (new) monitoring scenario’s for OpsMgr vNext. The scenario’s he is talking about are:

Monitoring the end-user experience in a distributed cloud environment. Monitoring Health models and dependencies for .Net Applications and Java Enterprise Editions on the Windows Platform. Enhanced monitoring on Service oriented Architecture. Preliminary network monitoring.

Source: Words and Software I case you maybe missed this blog. Jeanie Decker is announcing that the  Operator Basics SuperFlow for Operations Manager 2007 is available for download! “Superflow, you wonder? Configuration Manager pioneered this format for delivering information. It provides a complete set of content, which can include videos and animations and interactivity, in a single package. The Operator Basics SuperFlow is intended for front-line operators who are new to Operations Manager and, when they look at all of the content on TechNet, think "where the heck do I start?" Read more on source.

As you probably know OpsMgr quite some people use the local system account as Action Account. And if things are not working as expected you sometimes want to run a script or other actions under the local system account. I used to use the Task Scheduler to have scripts running under the Local System Account, but now I learned you can easily use the PSExec tool of SysInternals to do the same:-) How does this work? You can download the tool and install it on the systems you want to do your troubleshooting or just use the live share on http://live.sysinternals.com/ I created a quick and dirty PowerShell script that writes the owner of the PowerShell process to the PowerShell eventlog.

######################################################################################## # Write Owner of PowerShell Process to PowerShell Eventlog # Authors: Stefan Stranger # ScriptName: UserAccountDebugging.ps1 # v1.000 - 24/03/2010 - stefstr - initial sstranger's release (quick & dirty version)

######################################################################################## #Function Write-EventLog($Description) # #Writes Owner of PowerShell process to PowerShell Eventlog. ############################################################################################## function Write-EventLog($Description) {     $source = "PowerShell(PowerShell)"     [string]$type = "Information"     [int]$eventid = 999         if(![System.Diagnostics.EventLog]::SourceExists($source))         {             [System.Diagnostics.EventLog]::CreateEventSource($source,'Windows PowerShell')         }         else          {                 $log = New-Object System.Diagnostics.EventLog                  $log.set_log("Windows PowerShell")                  $log.set_source($source)                 $log.WriteEntry($Description,$type,$eventid)         }

} $processes = Get-WmiObject Win32_Process -Filter "name='powershell.exe'" $appendedprocesses = foreach ($process in $processes) {Add-Member -MemberType NoteProperty -Name Owner  -Value ($process.GetOwner().User) -InputObject $process -PassThru} $owners = ($appendedprocesses | select owner) foreach ($owner in $owners) {     $evtdescription = "PowerShell process is being run under the next account: "  + $owner.Owner     Write-EventLog $evtdescription }

Do whatever you wanted to do in the PowerShell script for your OpsMgr environent

Write-Host "Hello World" Save above script as UserAccountDebugging.ps1. If we run the above script with our logged on user account we get the next result:

Result in Eventviewer

    Now let’s start PSExec and run the PowerShell script with the local system account. Open Command prompt (as Administrator) and type: psexec –i –d –s powershell.exe

Now a new PowerShell Window will be opened as Local System Account.

Let’s now run the PowerShell script again and check the owner of the PowerShell process. Close all PowerShell sessions first ;-)

      Have fun using PSExec to debug OpsMgr Permissions issues with the local system account.

Sources: Steve Rachui’s Manageability blog – ConfigMgr/OpsMgr and                Savision Live Maps Blog Is this a coincidence or what? I’ve been asked to take a look at some new blog articles from Savision about adding a ‘location’ attribute to the Windows Server class. And you can now read their first blog article How-To: Import Computer Location Information Into OpsMgr (Part 1-3) in a series of three. Savision explains how this can be achieved in three steps: Create a management pack that adds a ‘location’ attribute to the Windows Server class Write a connector that reads asset information from a CSV file and updates OpsMgr Configure Live Maps to create dynamic location aware maps Steve Rachui also extends in his blog article the class attributes from a flat file. But he uses a discovery vbscript to read the flat file instead of the connector being used/explained (in later blog posts) by Savision. It’s up to you what you choose to use. Have fun extending class attributes!

I just created my first PowerGUI PowerPack which you can use together with the MP Authoring Console. Just like I blogged previously in my MP Authoring Helper PowerShell script here. Here a teaser screenshot of the PowerGUI PowerPack.   It’s just a first version I created and I would like to have it tested a little more before posting it on the PowerGUI PowerPack library. So if anybody is creating MP’s using the MP Authoring Console and wants to try to use the MP Authoring PowerPack within PowerGUI to help creating MP’s let me know via Twitter via a DM or use the contact form on my weblog and I’ll email my PowerPack for testing.

This week I’m having an internal MP Authoring Workshop from Brian Wren and I learned that it’s sometimes handy to have information available that’s not available when having the MP Authoring tool open. You can not switch to the Service Model when you have an other windows open. That’s why I created a PowerShell script that let’s you easily have a look at all the Classes, Discoveries, Relationships and Modules you already created in your Management Pack (if you have saved it). Just copy the script and save it to “MPAuthoringHelper_v1.003.ps1” ############################################################################### # Getting Classes from MP XML file and exporting it to csv for use in the # MP Authoring tool # Authors: Stefan Stranger # v1.001 - 27/01/2010 - sstranger - initial sstranger's release # v1.002 - 11/03/2010 - sstranger - added GetRelationshipTypes Function and changed GetClasTypes Function #                               - added GetDiscoveries Function # v1.003 - 11/03/2010 - sstranger - added GetModules Function ############################################################################### param ([string]$Path = $(read-host "Please enter MP XML path and file name")) $Path = "c:\TEMP\MPAuthoringWS\day4\StoreApp.stefstr.xml" #$Path = "C:\Users\stefstr\Documents\Customers\Rabobank\MPDumps\Microsoft.SQLServer.2008.Monitoring.xml" #$Path = "C:\Users\stefstr\Documents\Customers\Rabobank\MPDumps\Microsoft.Exchange.Server.2003.Monitoring.xml" #Globals $global:XmlMPFileDocument = new-object System.Xml.XmlDocument # Get MP XML file. # Make sure you saved your MP file to XML regularly. $XmlMPFileDocument.Load($Path) ############################################################################################## #Function GetClassTypes # #Get Class Types from MP XML file ############################################################################################# Function GetClassTypes {      $MyClassTypes = $XmlMPFileDocument.ManagementPack.TypeDefinitions.EntityTypes.ClassTypes.ClassType     $MyClassTypes | select @{n='Class Name';e='ID'},@{n='Base Class';e='Base'}, Abstract, Singleton } ############################################################################################## #Function GetRelationshipTypes # #Get RelationShip Types from MP XML file ############################################################################################# Function GetRelationshipTypes {     $myRelationshipTypes = $XmlMPFileDocument.ManagementPack.TypeDefinitions.EntityTypes.RelationshipTypes.RelationshipType     $myRelationshipTypes | select @{n='Name';e='ID'},@{n='Base Class';e='Base'}, Abstract, Source, Target } ############################################################################################## #Function GetDiscoveries # #Get Discoveries from MP XML file ############################################################################################# Function GetDiscoveries {      $myDiscoveries = @()     foreach ($Discovery in $XmlMPFileDocument.ManagementPack.Monitoring.Discoveries.Discovery)     {         $obj = new-object System.Management.Automation.PSObject         $obj = $obj | add-member -membertype NoteProperty -name "Name" -value $Discovery.ID -passthru         $obj = $obj | add-member -membertype NoteProperty -name "Target" -value $Discovery.Target -passthru         $obj = $obj | add-member -membertype NoteProperty -name "Discovery Class" -value $Discovery.DiscoveryTypes.DiscoveryClass.TypeID -passthru         $obj = $obj | add-member -membertype NoteProperty -name "DS TypeID" -value $Discovery.DataSource.TypeID -passthru         $myDiscoveries = $myDiscoveries + $obj     }     $myDiscoveries } ############################################################################################## #Function GetModules # #Get Modules from MP XML file ############################################################################################# Function GetModules {     $myModules = @()     if ($XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.ProbeActionModuleType)     {         foreach ($ProbeModule in $XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.ProbeActionModuleType)         {             $obj = new-object System.Management.Automation.PSObject             $obj = $obj | add-member -membertype NoteProperty -name "Module Type" -value "Probe" -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Name" -value $ProbeModule.ID -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Type ID" -value $ProbeModule.ModuleImplementation.Composite.MemberModules.ProbeAction.TypeID -passthru             $myModules = $myModules + $obj         }     }     if ($XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.DataSourceModuleType)     {         foreach ($DSModule in $XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.DataSourceModuleType)         {             $obj = new-object System.Management.Automation.PSObject             $obj = $obj | add-member -membertype NoteProperty -name "Module Type" -value "Data Source" -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Name" -value $DSModule.ID -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Type ID" -value $DSModule.ModuleImplementation.Composite.MemberModules.DataSource.TypeID -passthru             $myModules = $myModules + $obj         }     }     if ($XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.WriteActionModuleType)     {            foreach ($WAModule in $XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.WriteActionModuleType)         {             $obj = new-object System.Management.Automation.PSObject             $obj = $obj | add-member -membertype NoteProperty -name "Module Type" -value "Write Action" -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Name" -value $WAModule.ID -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Type ID" -value $WAModule.ModuleImplementation.Composite.MemberModules.WriteAction.TypeID -passthru             $myModules = $myModules + $obj         }     }     if ($XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.ConditionDetectionModuleType)     {            foreach ($ConditionModule in $XmlMPFileDocument.ManagementPack.TypeDefinitions.ModuleTypes.ConditionDetectionModuleType)         {             $obj = new-object System.Management.Automation.PSObject             $obj = $obj | add-member -membertype NoteProperty -name "Module Type" -value "Condition Dectection" -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Name" -value $ConditionModule.ID -passthru             $obj = $obj | add-member -membertype NoteProperty -name "Type ID" -value "See XML for more info" -passthru             $myModules = $myModules + $obj         }     }     $myModules } #Call Functions GetClassTypes | Out-GridView GetRelationshipTypes | Out-GridView GetDiscoveries | Out-GridView GetModules | Out-GridView It only needs one parameter and that’s the path and filename of the MP xml file you are creating. Remember to save your MP xml file regularly. Have fun creating new MP’s!

Did you all know there TechNet is now in a Wiki Beta? What is TechNet Wiki Beta? The TechNet Wiki is a library of information about Microsoft technologies written by the community for the community. Whether you write code, manage servers, keep mission-critical sites up and running, or just enjoy digging into details, we think you will be at home in the TechNet Wiki. This is a community site. For official documentation, see MSDN Library, TechNet Library or contact Microsoft Support. The Wiki is focused on Microsoft technologies. The community will edit or remove topics that get too far off track. We are inspired by Wikipedia. Anyone who joins can participate and contribute content. For more information including how you can participate see http://social.technet.microsoft.com/wiki/ But the cool thing is that there is also a SCOM Wiki page on TechNet. Go check it out and add content!

This week I’m having a MP Authoring Workshop from Brian Wren in the UK and one of the modules of this workshop is Discovery. And to my surprise Kevin Holman published a blog post on Basic troubleshooting of discovery script yesterday. It’s a good start but I’ve some more help on troubleshooting a Discovery script. Here are mine (with some help of Brian): Verify that MP has been committed on agent Check agent for eventlog errors Check management server for errors Verify that discovery is running Enable debug events in script Debug script on agent   Step 1. Verify that MP has been committed on agent. How? There are several ways. One of them is looking in the OpsMgr eventlog for EventId 1201. Step 2. Check for agent errors. How? Check for OpsMgr EventId’s like 31876 or 21405 or other Health Service Module source errors. Step 3. Check for management server errors How? Check for OpsMgr EventId’s 10801 Step 4. Verify that discovery is running How? You can look for your discovery script in the C:\Program Files\System Center Operations Manager 2007\Health Service State\ subdirectories with the next command: dir /B /S mycooldiscovery.vbs Did you found it? I hope so;-) More info here. Now you can use Process Monitor from SystInternals to monitor your cscript vb discovery script. For more info on how to use Process Monitor go to Jeevan Bish’s blog. Step 5. Enable debug events in script. How? You can add a debug function to you discovery script that writes info the eventlog using oAPI.LogScriptEvent in your discovery script. For more info on using oAPI.LogScriptEvent take a look at MSDN. Step 6. Debug script on Agent. How? See Kevin’s post on how to do this. You can alslo take a look at Brian Wren’s Demo Store App Management on OpsManJam for an LogDebugEvent Function. ' '================================================================================== ' Sub:        LogDebugEvent ' Purpose:    Logs an informational event to the Operations Manager event log '            only if Debug argument is true '================================================================================== Sub LogDebugEvent(EventNo,Message)     Message = VbCrLf & Message     If bDebug = True Then         Call oAPI.LogScriptEvent(SCRIPT_NAME,EventNo,EVENT_LEVEL_INFO,Message)     End If End Sub More blogposts on MP Authoring after this week of MP Authoring training.

Source: Solution Accelerators – Dashboards The Microsoft Solution Accelerators Team started a blog and they are currently focusing on dashboards. Check out their new weblog.

Source: Thomas LaRock Thomas LaRock has published a SQL query I had not seen before, about finding the OpsMgr Database Sizes. Could be handy to know. [Thomas LaRock] “I was asked last week if  knew a query that would return the size of the databases that have been discovered by Operations Manager. I went about digging through some old queries I put together and found the information thatw as requested. Inside the OperationsManager database look for a view named MTV_Database. All discovered databases will be there. There should be a column for DatabaseSize, but the column will have a GUID at the end of the name. Now, I forget where I found it, but a while back I came across one of the view definitions that helped me to return a lot of other information, such as SQL version. The view that has SQL version is named MTV_DBEngine, but in order to join the MTV_Database to the MTV_DBEngine I needed to add in a handful of relationship tables and filter on the BaseManagedTypeID. I was going mad trying to make everything work until I found that specific view definition and I don’t recall which one it is, but what I do have is the end result which I can share (your GUIDs will be different, I imagine):” Read more at source. Time to at this new query to your OneNote SQL database queries, just like I did ;-)

I helped a customer creating a OpsMgr Monitor for checking if the there has been a SQL Full or Differential Backup within a specified number of hours. David Scheltens created the SQL query. Remark: Please read information on System Center Central for more info on this article. This is how you could create this kind of monitor in your own environment. Steps:

Create a Timed Script Two State Monitor. Save Monitor in other MP than Default MP. Give the Monitor a Name, like “Custom – SQL Full or Differential Backup Check”. Target the Monitor to SQL Database. Select a suitable Parent Monitor like Availability. Disable Monitor (enable by an Override later). Configure how often this Monitor should run (default is 15 mins, but you could change this depending on the time your SQL backups run) Copy script from here. The script uses three parameters (or arguments) - Param 0: The SQL connection string for the server - Param 1: The Database to use - Param 2: The threshold (in hours) to use Configure the Parameters.    Configure Unhealthy Expression. Property[@Name='NumHours'] Greater Than 1 (threshold when your backup should have been run)
Configure Healthy Expression Property[@Name='NumHours'] Less than or equal to 1 (threshold when your backup should have been run)
Configure Health.
Configure Alerting I used the Reason, BackupType and Number of Hours in the Alert Description.
Open the MP XML file and change the data type in the xml configuration from string to integer. Since making this change, the alerts and state changes seems to occur when at the correct time.More info here: (thanks to Daniele Grandini)<ErrorExpression>             <SimpleExpression>               <ValueExpression>                 <XPathQuery Type="Integer">Property[@Name='NumHours'] </XPathQuery>               </ValueExpression>               <Operator>Greater</Operator>               <ValueExpression>                 <Value Type="Integer">20</Value>               </ValueExpression>             </SimpleExpression>           </ErrorExpression>           <SuccessExpression>             <SimpleExpression>               <ValueExpression>                 <XPathQuery Type="Integer">Property[@Name='NumHours'] </XPathQuery>               </ValueExpression>               <Operator>LessEqual</Operator>               <ValueExpression>                 <Value Type="Integer">20</Value>               </ValueExpression>             </SimpleExpression>           </SuccessExpression> 15. Enable the Monitor via an Override. Remark: You may need to Use a Run As Account with the right permission for this Monitor. Result:

Some time ago I created a Tutorial How to Associate a Run As Account to a Monitor.  

In Operations Manager 2007, Run As Profiles and Run As Accounts are used to select users with the privileges needed for running rules, tasks, and monitors. Management Pack authors create a rule, task, or monitor, and then associate it with a Run As Profile. The named Run As Profile is imported along with the Management Pack into Operations Manager 2007. The Operations Manager 2007 administrator creates a named Run As Account and specifies users and groups. The administrator then adds the Run As Account to the Run As Profile and specifies the target computers that the account should run on. The goal in this short tutorial is to create a Timed Script Two State Monitor and associate a run as account to this monitor and save it all in a Management Pack. The Timed Script Two State Monitor is a vbscript that uses WMI to check if BizTalk orchestrations are not started. I’ve got some great help from Jakub Oleksy (http://blogs.msdn.com/jakuboleksy/default.aspx) and Steve Wilson (http://www.authormps.com/dnn/) You can download the tutorial here.

As you all know the old MP Catalog site website is replaced by the new PinPoint website. And not everybody is used to the new way of searching for Management Packs yet ;-) And today I got a question about finding an overview of the latest Management Packs ordered by release date. As I was not able to create such a view on the new PinPoint MP Catalog website I turned to our good old Microsoft Download website. WARNING: Today I found out that my previous search on the Microsoft Download site not returned all expected results. I changed some of the search options and this is the best search result I can get at the moment. http://www.microsoft.com/downloads/en/results.aspx?freetext=management+pack&sortCriteria=Date&sortOrder=Descending&stype=ss_rr&displaylang=en&nr=10 Disclamer: please use your own search strings also, to check if the above search is returning all your MP searches!

It’s still not completely the same experience as our old MP Catalog website (no third-party MP's as you can imagine) but you can easily order by release date. Because this is something that’s missing on the PinPoint Website. Remark: I know you can find the latest Plugin’s on the PinPoint Website, but you still not see the release date immediately. I created an easy to remember URL for your good old Microsoft Download website: http://tinyurl.com/oldmpcatalog Please use this search as a starting point for your MP searches: http://www.microsoft.com/downloads/en/results.aspx?freetext=management+pack&sortCriteria=Date&sortOrder=Descending&stype=ss_rr&displaylang=en&nr=50 Have fun searching for your Management Packs!

Source: internal email Microsoft has acquired Opalis Software, a Toronto-based private company that is a leader in IT process automation software. Opalis will become a wholly-owned subsidiary of Microsoft. This acquisition will deliver great new functionality to the System Center family and will help win more datacenter deals by offering a more complete solution to our customers. Learn more about Opalis, visit http://www.opalis.com. Read the official blog post on the acquisition. Check out these 4 demo videos of Opalis : http://edge.technet.com/Media/Opalis-Solution-and-Component-Overview/ http://edge.technet.com/Media/Installing-and-Configuring-Opalis-Components/ http://edge.technet.com/Media/Writing-Opalis-Workflows-and-Utilizing-the-Databus/ http://edge.technet.com/Media/System-Center-Operations-Manager-to-BMC-Remedy-Integration-Pack-Demo/

As you all know the old MP Catalog site website is replaced by the new PinPoint website. And not everybody is used to the new way of searching for Management Packs yet ;-) And today I got a question about finding an overview of the latest Management Packs ordered by release date. As I was not able to create such a view on the new PinPoint MP Catalog website I turned to our good old Microsoft Download website. It’s still not completely the same experience as our old MP Catalog website but you can easily order by release date. Because this is something that’s missing on the PinPoint Website. Remark: I know you can find the latest Plugin’s on the PinPoint Website, but you still not see the release date immediately. I created an easy to remember URL for your good old Microsoft Download website: http://tinyurl.com/oldmpcatalog   Have fun searching for your Management Packs!

Source: Partner Readiness & Training Newsletter – Dec 2009   System Center Operation Manager TechNet Virtual Labs TechNet Virtual Lab: Building Management Packs with the Authoring Console TechNet Virtual Lab: Extending Operations Manager 2007 to Monitoring SAP on SQL TechNet Virtual Lab: Installing Operations Manager 2007 TechNet Virtual Lab: Monitoring Windows Server 2008, SQL Server 2008 and Exchange 2007 TechNet Virtual Lab: System Center Operations Manager 2007 Authoring Management Packs – Introduction (ITPro) TechNet Virtual Lab: System Center Operations Manager 2007 Management Pack Authoring – Advanced (ITPro) TechNet Virtual Lab: System Center Operations Manager 2007 R2 - Advanced TechNet Virtual Lab: System Center Operations Manager 2007 R2 - Introduction TechNet Virtual Lab: Understanding the Operations Manager 2007 SDK   Have fun learning about OpsMgr 2007!

A customer was asking for some information about the time it took for users to logon on their Terminal Server hosted Windows Desktops. Luckily there were two eventID’s created during the logon process.  And if you calculate the time between those eventID’s then you have some insight in the Logon time for a user. When a user logs on, Security Event 528 is created. Event 528 is logged whenever an account logs on to the local computer, except for in the event of network logons (see event 540). Event 528 is logged whether the account used for logon is a local SAM account or a domain account. On a Windows 7 client Security event 4624 is created. Example Screenshot: And then they used Event ID 865 — Software Restriction Policy Notification for the last Log on event. So the Log on Time could be calculated the time difference between those eventID’s. You should look for your own eventID if you want to create a simular report. So how can these Events be used to create a Logon Time Report? High Level steps: Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr. Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user. Create a Custom Logon Time OpsMgr Report with Visual Studio. Let’s start with a more detailed overview of the steps. For testing purposes I created a batch script, which you can use to create two dummy eventID’s in the Application Event Log. You can run this script using different credentials (users) with the SysInternals tool ShellRunAs. @echo off REM First EventID 528 LastEventID 865 Eventcreate /L Application /D "First EventID created" /T INFORMATION /ID 528 REM Create Random number between 1 -30 secs. Set max=30 Set min=1 :: Generate number between Min and Max Set /A rand=%random% %% (max - min + 1)+ min REM Wait for 1-30 secs to generate second EventID sleep %rand% Eventcreate /L Application /D "Last EventID created" /T INFORMATION /ID 865 The above script uses the sleep.exe command so you need to install it from the Reskit if you haven’t installed that yet. As you can see the time between the two events is a random number between 1 – 30 secs. Save above code to LogOndemo.cmd and run from command prompt. [Screenshots]   Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr Ok now we have the two dummy events are created we can create the Event Collection Rules. Make sure you created a new MP to store your Event Collection Rules. Don’t save them in the Default MP ;-) Disable Rule we enable the rules later for the servers we want to Collection Rules to be running on. Keep in mind these example Collection Rules are created for the dummy eventID’s. You should select your own EventID’s. Enable Rules for server(s) you need to have to Collection Rule running on. For EventID 865 follow the same steps as shown above. Don’t forget to enable the Rule with an Override!   Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user. And now the most difficult part of creating a Custom Report, creating the right Database SQL queries. I got some help from some SQL guru’s at the office, so you may want to talk to your local dba for some pointers about creating the right SQL queries. First we need to determine what we want to show in our Reports. Right? The customer wanted to see the Minimum, Maximum and Average LogOn time per user on a server. Something like this:   But first some steps I took to create the SQL queries. First I wanted to collect all the 528 and 865 events collected by the two Collection Rules (FirstLogOn Event 528 and LastLogOn Event 865). And I used the next SQL query from Anders Bengtsson for that and changed that a little. It collects all the Collected Events 528 and 865 within the selected period ('2009-12-01 00:00:00.000' and '2009-12-12 00:00:00.000') DECLARE @StartDate datetime DECLARE @EndDate   datetime SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-12 00:00:00.000'; USE OPERATIONSMANAGERDW SELECT     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription' FROM Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId WHERE vEventLevel.EventLevelTitle = 'Information' AND vEvent.Datetime between @StartDate and @EndDate AND (vEvent.EventDisplayNumber = 528 OR vEvent.EventDisplayNumber = 865)    Result Screenshot: It’s a start but not completely what we want ;-) As you can see to LogOn time for user OpsMgrDemo\OM_Admin is 22 seconds. So which query do we need to create to calculate the time difference between those two events per UserName? DECLARE @StartDate datetime DECLARE @EndDate   datetime SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-12 00:00:00.000'; WITH CTE AS     (SELECT     ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription'     FROM     Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId     WHERE vEventLevel.EventLevelTitle = 'Information'     AND vEvent.Datetime between @StartDate and @EndDate     AND (vEvent.EventDisplayNumber = 528     OR vEvent.EventDisplayNumber = 865)        ) SELECT * , (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime , DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime FROM CTE WHERE RowNum%2 = 1 Result Screenshot: As you can see, per UserName is calculated what the LogOn Time is. Again you see that it took 22 seconds for the LogOn for User OM_Admin. You could already start using this SQL query as Database SQL Query in your Custom OpsMgr Report in Visual Studio. You could for instance, Group the data on Computer or User to generate a Report on LogOn time for users on a specific Computer or User. Example Screenshot: But we wanted to create a Report with MIN, MAX and AVG LogOn time per user, right? For that we need the next SQL query. DECLARE @StartDate datetime DECLARE @EndDate   datetime SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-12 00:00:00.000'; -- Create TEMP table --(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LoggOffTime, LogTime) DECLARE @temp TABLE(     RowNum INTEGER,     DateTime DATETIME,     EventSource NVARCHAR(250),     Computer NVARCHAR(250),     Type NVARCHAR(250),     EventID INTEGER,     EventChannelTitle NVARCHAR(250),     UserName NVARCHAR(250),     EventDescription NVARCHAR(250),     LogOffTime DATETIME,     LogTime INTEGER ); WITH CTE AS     (SELECT        ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription'     FROM     Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId     WHERE vEventLevel.EventLevelTitle = 'Information'     AND vEvent.Datetime between @StartDate and @EndDate     AND (vEvent.EventDisplayNumber = 528     OR vEvent.EventDisplayNumber = 865) ) INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime) SELECT * , (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime , DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime FROM CTE WHERE RowNum%2 = 1 SELECT     Computer,     UserName,     COUNT(t1.LogTime) AS [NUMBEROFLOGINS],     MAX(t1.LogTime) AS [MAXLOGTIME],     MIN(t1.LogTime) AS [MINLOGTIME],     AVG(t1.LogTime) AS [AVGLOGTIME]     FROM @temp t1     GROUP BY Computer,UserName Screenshot Result: Yes! This is what we were looking for. Now we have the right Data SQL query, we can Open Visual Studio to create our Custom User LogOn Time Report. Create a Custom Logon Time OpsMgr Report with Visual Studio Let’s open SQL Server Business Intelligence Development Studio. Create a New Project and select Report Server Project Wizard and give your project a name. Select Next in the Welcome to the Report Wizard Screen Create a Data Source Click Edit button to enter the SQL Server information. And Test Connection if you want. Click Next in the Report Wizard Screen.   Enter the previously created SQL query with some changes (remove the Declare statements). DECLARE @temp TABLE(     RowNum INTEGER,     DateTime DATETIME,     EventSource NVARCHAR(250),     Computer NVARCHAR(250),     Type NVARCHAR(250),     EventID INTEGER,     EventChannelTitle NVARCHAR(250),     UserName NVARCHAR(250),     EventDescription NVARCHAR(250),     LogOffTime DATETIME,     LogTime INTEGER ); WITH CTE AS     (SELECT        ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription'     FROM     Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId     WHERE vEventLevel.EventLevelTitle = 'Information'     AND vEvent.Datetime between @StartDate and @EndDate     AND (vEvent.EventDisplayNumber = 528     OR vEvent.EventDisplayNumber = 865) ) INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime) SELECT * , (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime , DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime FROM CTE WHERE RowNum%2 = 1 SELECT     Computer,     UserName,     COUNT(t1.LogTime) AS [NUMBEROFLOGINS],     MAX(t1.LogTime) AS [MAXLOGTIME],     MIN(t1.LogTime) AS [MINLOGTIME],     AVG(t1.LogTime) AS [AVGLOGTIME]     FROM @temp t1     GROUP BY Computer,UserName Copy and paste above (or your own query) to the Query Builder window and click on Next. Select your Report Type and click on Next Select how you want to group your report. (I just kept the default settings) Select the Table Style of the Report and click on Next Enter Report server and Deployment folder info and click on Next. Give your Report a name and select Preview Report and click on Finish Change Report Parameters from Text to Date/Time Date Type. You need to change this for both parameters! Let’s check the Report. Go to Preview, select the Start Date and End Date and click on View Report. Ok it’s not exactly the way we would like it to be, but the results are there! Let’s Pimp this Report a little. So this could be the end result. And you can pimp it even more if you want…   Have fun creating Custom OpsMgr Reports!

This week a got a question from a customer about ‘flapping’ taking place from a Monitor. I found out that they meant the State Changes that took place for a newly created Monitor. They found out that for a certain machine the State changed quite often and this was caused by a Recovery Task that was part of this monitor. So they wanted to know if there was a way to see quickly if a Monitor caused a lot of State Changes. There are quite some SQL queries you can use to have a look at the State Changes taken place in your OpsMgr environment. Just take a look at some of the SQL queries on the weblog of my colleague Jonathan Almquist. But what if you only want to know the State Changes that took place for a specific Monitor? Again you can use one of Jonathan’s SQL queries and change them to fit your needs. And this is exactly what I’ve done but I also wanted to run the query from the OpsMgr Command Shell. The reason for this was that I easily wanted to retrieve the Monitor Name using the OpsMgr Get-Monitor Cmdlet.   So here is the PowerShell script I created. You can change the SQL query if you want, to fit your own needs ;-) Result screenshot: Have fun with OpsMgr and PowerShell!

Today I got a question from a customer if I would check if a number of servers had an OpsMgr agent installed? Because this was a large list and the number of servers being monitored by OpsMgr was also a very large list I didn’t wanted to to copy and paste all the server names for the text file and check them in the OpsMgr Console. So I created a PowerShell script to check if an OpsMgr Agent was installed for each of the servers in the file list. The first script I created was not very fast because it did a get-agent for each of the servers in the list and that takes quite some time in a large environment ;-) So I created a new one that’s much faster. Let’s start with the slow script: First you need to put all the servers you want to check in a file, like this:

servername1 servername2 servername3 servername4 servername5 servername6 servername7 servername8 servername9 servername10 servername11 dc8

Save this list in a text file like d:\temp\servers.txt Now run the next script in the OpsMgr Command shell:

$servers = get-content d:\temp\servers.txt foreach ($server in $servers) {get-agent | where {$_.ComputerName -eq $server} | select Name} As you can see it takes almost 12 seconds to do this the slow way.

And now the fast way.

$Servers = get-content c:\temp\servers.txt $Agents = get-agent | select ComputerName Foreach ($agent in $Agents) {if ($servers –contains $agent.ComputerName) {$agent | select ComputerName}}

So the next time someone is asking you if an OpsMgr agent is installed on their servers you now it in seconds. Have fun using PowerShell!