Microsoft - musc@> $daniele.rant

Three quarters of 2015, my IT career and various ramblings

Daniele Muscetta America, Coding, Cross Platform, Family, General, Linux, Microsoft, MOM, MOM2005, Networking, OpInsights, OpsMgr2007, OpsMgr2012, Personal, PowerShell, Rant, Security, System Center Operations Manager 2007, System Center Operations Manager 2012, xplat October 5, 2015July 23, 20192015

September is over. The first three quarters of 2015 are over.
This has been a very important year so far – difficult, but revealing. Everything has been about change, healing and renewal.

We moved back to Europe first, and you might have now also read my other post about leaving Microsoft, more recently.

This was a hard choice – it took many months to reach the conclusion this is what I needed to do.

Most people have gone thru strong programming: they think you have to be ‘successful’ at something. Success is externally defined, anyhow (as opposed to satisfaction which we define ourselves) and therefore you are supposed to study in college a certain field, then use that at work to build your career in the same field… and keep doing the same thing.

I was never like that – I didn’t go to college, I didn’t study as an ‘engineer’. I just saw there was a market opportunity to find a job when I started, studied on the job, eventually excelled at it. But it never was *the* road. It just was one road; it has served me well so far, but it was just one thing I tried, and it worked out.
How did it start? As a pre-teen, I had been interested in computers, then left that for a while, did ‘normal’ high school (in Italy at the time, this was really non-technological), then I tried to study sociology for a little bit – I really enjoyed the Cultural Anthropology lessons there, and we were smoking good weed with some folks outside of the university, but I really could not be asked to spend the following 5 or 10 years or my life just studying and ‘hanging around’ – I wanted money and independence to move out of my parent’s house.

So, without much fanfare, I revived my IT knowledge: upgraded my skill from the ‘hobbyist’ world of the Commodore 64 and Amiga scene (I had been passionate about modems and the BBS world then), looked at the PC world of the time, rode the ‘Internet wave’ and applied for a simple job at an IT company.

A lot of my friends were either not even searching for a job, with the excuse that there weren’t any, or spending time in university, in a time of change, where all the university-level jobs were taken anyway so that would have meant waiting even more after they had finished studying… I am not even sure they realized this until much later.
But I just applied, played my cards, and got my job.

When I went to sign it, they also reminded me they expected hard work at the simplest and humblest level: I would have to fix PC’s, printers, help users with networking issues and tasks like those – at a customer of theirs, a big company.
I was ready to roll up my sleeves and help that IT department however I would be capable of, and I did.
It all grew from there.

And that’s how my IT career started. I learned all I know of IT on the job and by working my ass off and studying extra hours and watching older/more expert colleagues and making experience.

I am not an engineer.
I am, at most, a mechanic.
I did learn a lot of companies and the market, languages, designs, politics, the human and technical factors in software engineering and the IT marketplace/worlds, over the course of the past 18 years.

But when I started, I was just trying to lend a honest hand, to get paid some money in return – isn’t that what work was about?

Over time IT got out of control. Like Venom, in the Marvel comics, that made its appearance as a costume that SpiderMan started wearing… and it slowly took over, as the ‘costume’ was in reality some sort of alien symbiotic organism (like a pest).

You might be wondering what I mean. From the outside I was a successful Senior Program Manager of a ‘hot’ Microsoft product.
Someone must have mistaken my diligence and hard work for ‘talent’ or ‘desire of career’ – but it never was.
I got pushed up, taught to never turn down ‘opportunities’.

But I don’t feel this is my path anymore.
That type of work takes too much metal energy off me, and made me neglect myself and my family. Success at the expense of my own health and my family’s isn’t worth it. Some other people wrote that too – in my case I stopped hopefully earlier.

So what am I doing now?

First and foremost, I am taking time for myself and my family.
I am reading (and writing)
I am cooking again
I have been catching up on sleep – and have dreams again
I am helping my father in law to build a shed in his yard
We bought a 14-years old Volkswagen van that we are turning into a Camper
I have not stopped building guitars – in fact I am getting setup to do it ‘seriously’ – so I am also standing up a separate site to promote that activity
I am making music and discovering new music and instruments
I am meeting new people and new situations

There’s a lot of folks out there who either think I am crazy (they might be right, but I am happy this way), or think this is some sort of lateral move – I am not searching for another IT job, thanks. Stop the noise on LinkedIn please: I don’t fit in your algorithms, I just made you believe I did, all these years.

Capturing your knowledge/intelligence should be SIMPLE

Daniele Muscetta Microsoft, MOM, MOM2005, OpInsights, OpsMgr2012, Personal, System Center Operations Manager 2012 November 22, 2014November 23, 2014Advisor, Operations Manager, opinsights, opsmgr, opsmgr2012, SCA, scom, sysctr, system center, System Center Advisor

Lately this blog has been very personal. This post is about stuff I do at work, so if you are not one of my IT readers, don’t worry.

For my IT readers, an interruptions from guitars and music on this blog to share some personal reflection on OpInsights and SCOM.

SCOM is very powerful. You know I have always been a huge fan of 2007 and worked myself on the 2012 release. But, compared to its predecessor – MOM – in SCOM it has always been very hard to author management packs – multiple tools, a lot of documentation… here we are, more than 6 years later, and the first 2 comments on an old post on the momteam blog still strike me hard every time I read it:

You would think that things have changed, but SCOM is fundamentally complex, and even with the advances in tooling (VSAE, MPAuthor, etc) writing MPs is still black magic, if you ask some users.

I already blogged about me exporting and MP and converting its event-based alerting rules to OpInsights searches.

Well, writing those alerting rules in SCOM needs a lot of complex XML – you might not need to know how to write it (but you often have to attempt dechipering it) and even if you create rules with a wizard, it will produce a lot of complex XML for you.

In the screenshot below, the large XML chunk that is needed to pick up a specific eventId from a specific log and a specific source: the key/important information is only a small fraction of it, while the rest is ‘packaging’:

I want OpInsights to be SIMPLE.

If there is one thing I want the most for this project, is this.

That’s why the same rule can now be expressed with a simple filter search in OpInsights, where all you need is just that key information

EventID=1037 Source=”Microsoft-Windows-IIS-W3SVC” EventLog=System

and you essentially don’t have to care about any sort of packaging nor mess with XML.

Click, click – filters/facets in the UI let you refine your criteria. And your saved searches too. And they execute right away, there is not even a ‘Done’ button to press. You might just be watching those searches pinned to tiles in your dashboard. All it took was identify the three key pieces of info, no complex XML wrapping needed!

Ok, granted – there ARE legitimate, more complex, scenarios for which you need complex data sources/collectors and specialized/well thought data shaping, not just events – and we use those powerful capabilities of the MMA agent in intelligence packs. But at its core, the simple search language and explor-ability of the data are meant to bring back SIMPLE to the modern monitoring world. Help us prioritize what data sources you need first!

PS – if you have no idea what I was talking about – thanks for making it till here, but don’t worry: either you are not an IT person, which means simply ignore this; or – if you are an IT person – go check out Azure Operational Insights!

APM in OpsMgr 2012: for Dev and for Ops

Daniele Muscetta Microsoft, OpsMgr2012, Personal, Rant, System Center Operations Manager 2012 February 5, 2012July 23, 2019APM, AVIcode, devops, ITIL, MOF, Rant, system center, thoughts

I recently wrote a couple of technical posts about the object model we have chosen for APM in OpsMgr 2012 and how to author granular alerting rules for APM in XML. That’s more the type of post that pertains on the momteam blog.

This one you are reading now, instead, is more “philosophical” than technical – I think that, going forward, I’ll keep more of this distinction by posting my rants here on my personal blog, as they are only partially related to the products and more about my point of view on things. The reasons explained below are just those that I perceive and what drives me – I don’t mean in any way to be speaking on behalf of my company, our strategists or product planners.

I have heard statements from customers such as “AVIcode is a developer tool” or “APM is for QA/Test environments – if you need it in production you have not done your QA work well”and similar statements. People asked why we did bring together the two, for example, on the TechNet forums. Sure, it can be useful to employ such a tool also in a development and QA/test environment… but why not in production? With frequent deployments that the agile business demands, change control alone can’t slow down the business and sometimes bad things happen anyway – so we need solid monitoring to keep an eye on the behavior and performance on the system, exposed in a way that can quickly pinpoint where issues might be – be them in the infrastructure or in the code – in a way that enables people to efficiently triage and resolve them. Sergey points out how APM in OpsMgr 2012 is much easier to setup, simpler to configure and cheaper to maintain than the standalone AVIcode product ever was, and hints at the fact that a comprehensive solution encompassing both “traditional” systems management approach as well as Application Performance Monitoring is a good one. It is a good one, in its simplest form, because we have a simplified, unified and more cost-effective infrastructure. It is a good one – I add – because we can extract a lot of useful information from within the applications, only when those are running; when they are down altogether, APM is not very useful on its own, when it is not complemented by “traditional” OS and platform checks: before I wonder if my application is slow, I’d better ask “is IIS actually up and running? is my application running at all?”. Operations Manager has been historically very good, with its management packs, in answering those questions. APM adds the deep application perspective to it, to provide rich data that Developers and Operations need to have an overall picture of what is going on in their systems and applications.

In my opinion, in this world of continuous services improvement and cloud services, IT management is tearing down the walls between what traditionally has been two separate worlds of “Operations” (Ops) teams and Development (Dev) teams. So, while people ask why we brought what was more of a Developer tool into a pure System Management tool, it is clear to me that those areas are converging, and even other vendors who start from the opposite approach (APM) eventually go “back to the basics” and begin implementing server-level systems management such as showing disk space and CPU utilization, meaning that, whatever your starting point was or has been, everybody wants and feels the need to bring those two worlds and disciplines together.

This line of thoughts has even been given a name: “DevOps”.

What is this DevOps things anyway is one famous post that can be found on the web, where Stephen Nelson-Smith writes:

[…] On most projects I’ve worked on, the project team is split into developers, testers, release managers and sysadmins working in separate silos. From a process perspective this is dreadfully wasteful. It can also lead to a ‘lob it over the wall’ philosophy – problems are passed between business analysts, developers, QA specialists and sysadmins […] The Devops movement is built around a group of people who believe that the application of a combination of appropriate technology and attitude can revolutionize the world of software development and delivery […] these people understand the key point – we’re all on the same side! All of us – developers, testers, managers, DBAs, network technicians, and sysadmins – are all trying to achieve the same thing: the delivery of great quality, reliable software that delivers business benefit to those who commissioned it. […]

DevOps – the war is over if you want it is a presentation by Patrick Debois which I also encourage you to check out, as it is also very evocative thru images:

DevOps – 6 steps for improved collaboration

[…] The DevOps movement is a modern push from the software industry to instill better interaction and productivity between development (Dev) and IT operations (Ops). Instead of throwing applications “over the fence” blindly to operations, a fluid and much more effective DevOps process inserts transparency, efficiency and ownership into the art of developing, releasing and the production use of critical applications. It also binds the two traditionally siloed teams together. […]

Last but not least, 10+ Deploys Per Day: Dev and Ops Cooperation at Flickr (another presentation from a conference) is a real-world example of a large scale web site (Flickr) and how those practices are adopted.

When it comes to the DevOps ideas and concepts within Microsoft products, for what I can see, some customers really “get“ it, and would like to see more in this sense. For example I found this interesting blog post by James Dawson:

[…] The bulk of my work revolves around the Microsoft platform and to put it bluntly it is very much a second class citizen in terms of the available tooling.
Now I’ve fanned the flames, let me put some context around that. I don’t mean that as a criticism, in fact I view the status quo as an entirely natural result given where the movement grew out of and, to be frank, the mindset of the typical Microsoft IT shop. In a Microsoft environment there tends to be far greater reliance on big vendor products, whereas in the Linux/BSD world it is far more common to integrate a series of discrete tools into a complete tool chain that meets the needs for a given scenario. […]

I think James is right when saying this: he “gets” it, but we also have a vast user base of more “traditional” enterprise customers where the concepts have not been digested and understood yet. When it comes to traditional enterprises, what sometimes happens is well explained in this other article by Paul Krill:

[…] To protect the infrastructure, IT ops can put in place processes that seem almost draconian, causing developers to complain that these processes slow them down, says Glenn O’Donnell, an analyst at Forrester Research. Indeed, processes such as ITIL (IT Infrastructure Library) that provide a standardized way of doing things, such as handling change management, can become twisted into bureaucracy for its own sake. But sometimes, people "take a good idea too far, and that happens with ITIL, too." […]

And I think that is exactly one of the reasons why, even if many of our teams “get” it, we need to talk more of the DevOps culture in those places where it hasn’t arrived yet, so that these integrated products are more successful and can help them solve problems – because some of these customers haven’t yet realized that it takes a culture shift before these new tools can be adopted. DevOps does not have critical mass today, but could have it tomorrow. Even Gartner says:

[…] by 2015, DevOps will evolve from a niche strategy employed by large cloud providers into a mainstream strategy employed by 20% of the Global 2000 organizations”. […]

So, back to suggesting that Microsoft produces more of this “goodness”, James again writes:

[…] I want to see the values espoused by DevOps spread far and wide, including the quietest backwaters of corporate IT, where Windows, Office and IE 6 reign supreme. To that end, the Microsoft infrastructure community needs to take a similar approach as the .NET community did and start bringing some of the goodness that we see in the Linux world to the Microsoft platform in a way that facilitates adoption for all and actually takes advantage of the platform’s innate richness and strengths. […]

So do I. And, for what I can tell, we are actually trying to bridge gaps and push the culture shift – integrating APM in OpsMgr is definitely an effort in this direction. But it might take some time. Is it too an “utopian” a vision? I don’t think it is; I think we can get there. But it will take some time. As this other article was saying:

[…] The DevOps approach is so radical it will take some time to cross the chasm, and indeed it will be actively resisted by many organizations where it threatens traditional delivery models and organizational structures. […]

Let’s get Dev and Ops talking to each other, also in the Enteprise! I am all for it.

Disclaimer

The information in this weblog is provided "AS IS" with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my own personal opinion. All code samples are provided "AS IS" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.
THIS WORK IS NOT ENDORSED AND NOT EVEN CHECKED, AUTHORIZED, SCRUTINIZED NOR APPROVED BY MY EMPLOYER, AND IT ONLY REPRESENT SOMETHING WHICH I’VE DONE IN MY FREE TIME. NO GUARANTEE WHATSOEVER IS GIVEN ON THIS. THE AUTHOR SHALL NOT BE MADE RESPONSIBLE FOR ANY DAMAGE YOU MIGHT INCUR WHEN USING THIS INFORMATION. If you want to see the official info from my employer about the topic above, go to http://www.microsoft.com/presspass/presskits/cloud/default.aspx

A couple of OpsMgr / APM Posts

Daniele Muscetta Microsoft, System Center Operations Manager 2012 January 24, 2012January 24, 2012APM, AVIcode, opsmgr2012, scom

Just some shameless personal plug here, pointing out that I recently wrote two technical posts on the momteam blog about the APM feature in Operations Manager 2012 – maybe you want to check them out:

APM object model – describes the object model that gets created by the APM Template/Wizard when you configure .NET application monitoring
Custom APM Rules for Granular Alerting – explains how you can leverage management pack authoring techniques to create alerting rules with super-granular criteria’s (building beyond what the GUI would let you do)

Hope you find them useful – if you are one of my “OpsMgr readers”

Operations Manager 2012 Release Candidate is out of the bag!

Daniele Muscetta Microsoft, MOM, OpsMgr2012, System Center Operations Manager 2012 November 10, 2011July 22, 2019APM, opsmgr, opsmgr2012

Go read the announcement at http://blogs.technet.com/b/server-cloud/archive/2011/11/10/system-center-operations-manager-2012-release-candidate-from-the-datacenter-to-the-cloud.aspx

This is the first public release since I am part of the team (I started in this role the day after the team had shipped Beta) and this is the first release that contains some direct output of my work. It feels so good!

Documentation has also been refreshed – it starts here http://technet.microsoft.com/en-us/library/hh205987.aspx

The part specifically about the APM feature is here http://technet.microsoft.com/en-us/library/hh457578.aspx

Enjoy!

Repost: Useful SetSPN tips

Daniele Muscetta Microsoft, Networking, OpsMgr2007, Security, System Center Operations Manager 2007 October 19, 2011authentication, kerberos, opsmgr, OpsMgr2007, setspn, SPN, Windows

I just saw that my former colleague (PFE) Tristan has posted an interesting note about the use of SetSPN “–A” vs SetSPN “–S”. I normally don’t repost other people’s content, but I thought this would be useful as there are a few SPN used in OpsMgr and it is not always easy to get them all right… and you can find a few tricks I was not aware of, by reading his post.

Check out the original post at http://blogs.technet.com/b/tristank/archive/2011/10/10/psa-you-really-need-to-update-your-kerberos-setup-documentation.aspx

A month in a new life

Daniele Muscetta Microsoft, Personal, Rant, System Center Operations Manager 2007 August 19, 2011August 19, 2011

Hey, I have just realized that I have been in my new PM role for a month already – time flies!

If you are one of my OpsMgr readers, in case you haven’t noticed, I have been silent here but I have published a post on the momteam blog – check it out: http://blogs.technet.com/b/momteam/archive/2011/08/12/application-performance-monitoring-in-opsmgr-2012-beta.aspx

If you are one of those few readers interested in following what I do, instead – I can tell you that I am loving the new job. Lot to do, of course, and that also applies to the private sphere – did you know that relocating to another continent takes some energy and effort? – but we are settling in nicely and things are going very smooth overall.

I have been chosen; Farewell my friends…

Daniele Muscetta Microsoft, OpsMgr2007, Personal, Rant, System Center Operations Manager 2007 July 7, 2011March 19, 2014alien, career, farewell, goodbye, HC, Health Check, life, Microsoft, opsmgr, OpsMgr health Check, scom, work

I have been in Premier Field Engineering for nearly 7 years (it was not even called PFE when I joined – it was just “another type of support”…) and I have to admit that it has been a fun, fun ride: I worked with awesome people and managed to make a difference with our products and services for many customers – directly working with some of those customers, as well as indirectly thru the OpsMgr Health Check program – the service I led for the last 3+ years, which nowadays gets delivered hundreds of times a year around the globe by my other fellow PFEs.

But it is time to move on: I have decided to go thru a big life change for me and my family, and I won’t be working as a Premier Field Engineer anymore as of next week.

But don’t panic – I am staying at Microsoft!

I have actually never been closer to Microsoft than now: we are packing and moving to Seattle the coming weekend, and on July 18th I will start working as a Program Manager in the Operations Manager product team, in Redmond. I am hoping this will enable me to make a difference with even more customers.

Exciting times ahead – wish me luck!

That said – PFE is hiring! If you are interested in working for Microsoft – we have open positions (including my vacant position in Italy) for almost all the Microsoft technologies. Simply visit http://careers.microsoft.com and search on “PFE”.

As for the OpsMgr Health Check, don’t you worry: it will continue being improved – I left it in the hands of some capable colleagues: Bruno Gabrielli, Stefan Stranger and Tim McFadden – and they have a plan and commitment to update it to OpsMgr 2012.

Improved ACS Partitions Query

Daniele Muscetta Microsoft, OpsMgr2007, System Center Operations Manager 2007 May 4, 2011May 4, 2011ACS, Audit, queries, query, SQL, TSQL

This has been sitting on my hard drive for a long time. Long story short, the report I posted at Permanent Link to Audit Collection Services Database Partitions Size Report had a couple of bugs:

it did not consider the size of the dtString_XXX tables but only the size of dtEvent_XXX tables – this would still give you an idea of the trends, but it could lead to quite different SIZE calculations
the query was failing on some instances that have been installed with the wrong (unsupported) Collation settings.

I fixed both bugs, but I don’t have a machine with SQL 2005 and Visual Studio 2005 anymore… so I can’t rebuild my report – but I don’t want to distribute one that only works on SQL 2008 because I know that SQL2005 is still out there. This is partially the reason that held this post back.

Without waiting so much longer, therefore, I decided I’ll just give you the fixed query. Enjoy

--Query to get the Partition Table
--for each partition we launch the sp_spaceused stored procedure to determine the size and other info

--partition list
select PartitionId,Status,PartitionStartTime,PartitionCloseTime 
into #t1
from dbo.dtPartition with (nolock)
order by PartitionStartTime Desc 

--sp_spaceused holder table for dtEvent
create table #t2 (
    PartitionId nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    rows nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    reserved nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    data nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    index_size nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    unused nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS    
)

--sp_spaceused holder table for dtString
create table #t3 (
    PartitionId nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    rows nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    reserved nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    data nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    index_size nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS,
    unused nvarchar(MAX) Collate SQL_Latin1_General_CP1_CI_AS    
)

set nocount on

--vars used for building Partition GUID and main table name
declare @partGUID nvarchar(MAX)
declare @tblName nvarchar(MAX)
declare @tblNameComplete nvarchar(MAX)
declare @schema nvarchar(MAX)
DECLARE @vQuery NVARCHAR(MAX)

--cursor
declare c cursor for 
    select PartitionID from #t1
open c
fetch next from c into @partGUID

--start cursor usage
while @@FETCH_STATUS = 0
begin

--tblName - first usage for dtEvent
set @tblName = 'dtEvent_' + @partGUID

--retrieve the schema name
SET @vQuery = 'SELECT @dbschema = TABLE_SCHEMA from INFORMATION_SCHEMA.tables where TABLE_NAME = ''' + @tblName + ''''
EXEC sp_executesql @vQuery,N'@dbschema nvarchar(max) out, @dbtblName nvarchar(max)',@schema out, @tblname

--tblNameComplete
set @tblNameComplete = @schema + '.' + @tblName

INSERT #t2 
    EXEC sp_spaceused @tblNameComplete

--tblName - second usage for dtString
set @tblName = 'dtString_' + @partGUID

--retrieve the schema name
SET @vQuery = 'SELECT @dbschema = TABLE_SCHEMA from INFORMATION_SCHEMA.tables where TABLE_NAME = ''' + @tblName + ''''
EXEC sp_executesql @vQuery,N'@dbschema nvarchar(max) out, @dbtblName nvarchar(max)',@schema out, @tblname

--tblNameComplete
set @tblNameComplete = @schema + '.' + @tblName

INSERT #t3 
    EXEC sp_spaceused @tblNameComplete

fetch next from c into @partGUID
end
close c
deallocate c

--select * from #t2
--select * from #t3

--results
select #t1.PartitionId, 
    #t1.Status, 
    #t1.PartitionStartTime, 
    #t1.PartitionCloseTime, 
    #t2.rows,
    (CAST(LEFT(#t2.reserved,LEN(#t2.reserved)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t2.reserved,LEN(#t2.reserved)-3) AS NUMERIC(18,0))) as 'reservedKB', 
    (CAST(LEFT(#t2.data,LEN(#t2.data)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t3.data,LEN(#t3.data)-3) AS NUMERIC(18,0)))as 'dataKB', 
    (CAST(LEFT(#t2.index_size,LEN(#t2.index_size)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t3.index_size,LEN(#t3.index_size)-3) AS NUMERIC(18,0))) as 'indexKB', 
    (CAST(LEFT(#t2.unused,LEN(#t2.unused)-3) AS NUMERIC(18,0)) + CAST(LEFT(#t3.unused,LEN(#t3.unused)-3) AS NUMERIC(18,0))) as 'unusedKB'
from #t1
join #t2
on #t2.PartitionId = ('dtEvent_' + #t1.PartitionId)
join #t3
on #t3.PartitionId = ('dtString_' + #t1.PartitionId)
order by PartitionStartTime desc

--cleanup
drop table #t1
drop table #t2
drop table #t3

OpsMgr Agents and Gateways Failover Queries

Daniele Muscetta Microsoft, OpsMgr2007, System Center Operations Manager 2007 December 23, 2010agent, gateway, opsmgr, OpsMgr2007, queries, query, scom, SQL, System Center Operations Manager 2007, TSQL

The following article by Jimmy Harper explains very well how to set up agents and gateways’ failover paths thru Powershell http://blogs.technet.com/b/jimmyharper/archive/2010/07/23/powershell-commands-to-configure-gateway-server-agent-failover.aspx . This is the approach I also recommend, and that article is great – I encourage you to check it out if you haven’t done it yet!

Anyhow, when checking for the actual failover paths that have been configured, the use of Powershell suggested by Jimmy is rather slow – especially if your agent count is high. In the Operations Manager Health Check tool I was also using that technique at the beginning, but eventually moved to the use of SQL queries just for performance reasons. Since then, we have been using these SQL queries quite successfully for about 3 years now.

But this the season of giving… and I guess SQL Queries can be a gift, right? Therefore I am now donating them as Christmas Gift to the OpsMrg community

Enjoy – and Merry Christmas!

--GetAgentForWhichServerIsPrimary
SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as Server
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 
AND SourceBME.DisplayName not in (select DisplayName 
from dbo.ManagedEntityGenericView WITH (NOLOCK) 
where MonitoringClassId in (select ManagedTypeId 
from dbo.ManagedType WITH (NOLOCK) 
where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 
and IsDeleted ='0') 
AND SourceBME.DisplayName not in (select DisplayName from dbo.ManagedEntityGenericView WITH (NOLOCK) 
where MonitoringClassId in (select ManagedTypeId from dbo.ManagedType WITH (NOLOCK) 
where TypeName = 'Microsoft.SystemCenter.ManagementServer') 
and IsDeleted ='0') 
AND R.IsDeleted = '0'


--GetAgentForWhichServerIsFailover
SELECT SourceBME.DisplayName as Agent,TargetBME.DisplayName as Server
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication() 
AND SourceBME.DisplayName not in (select DisplayName 
from dbo.ManagedEntityGenericView WITH (NOLOCK) 
where MonitoringClassId in (select ManagedTypeId 
from dbo.ManagedType WITH (NOLOCK) 
where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 
and IsDeleted ='0') 
AND SourceBME.DisplayName not in (select DisplayName 
from dbo.ManagedEntityGenericView WITH (NOLOCK) 
where MonitoringClassId in (select ManagedTypeId 
from dbo.ManagedType WITH (NOLOCK) 
where TypeName = 'Microsoft.SystemCenter.ManagementServer') 
and IsDeleted ='0') 
AND R.IsDeleted = '0'


--GetGatewayForWhichServerIsPrimary
SELECT SourceBME.DisplayName as Gateway, TargetBME.DisplayName as Server
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceCommunication() 
AND SourceBME.DisplayName in (select DisplayName 
from dbo.ManagedEntityGenericView WITH (NOLOCK) 
where MonitoringClassId in (select ManagedTypeId 
from dbo.ManagedType WITH (NOLOCK) 
where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 
and IsDeleted ='0') 
AND R.IsDeleted = '0'
    

--GetGatewayForWhichServerIsFailover
SELECT SourceBME.DisplayName As Gateway, TargetBME.DisplayName as Server
FROM Relationship R WITH (NOLOCK) 
JOIN BaseManagedEntity SourceBME 
ON R.SourceEntityID = SourceBME.BaseManagedEntityID 
JOIN BaseManagedEntity TargetBME 
ON R.TargetEntityID = TargetBME.BaseManagedEntityID 
WHERE R.RelationshipTypeId = dbo.fn_ManagedTypeId_MicrosoftSystemCenterHealthServiceSecondaryCommunication() 
AND SourceBME.DisplayName in (select DisplayName 
from dbo.ManagedEntityGenericView WITH (NOLOCK) 
where MonitoringClassId in (select ManagedTypeId 
from dbo.ManagedType WITH (NOLOCK) 
where TypeName = 'Microsoft.SystemCenter.GatewayManagementServer') 
and IsDeleted ='0') 
AND R.IsDeleted = '0'


--xplat agents
select bme2.DisplayName as XPlatAgent, bme.DisplayName as Server
from dbo.Relationship r with (nolock) 
join dbo.RelationshipType rt with (nolock) 
on r.RelationshipTypeId = rt.RelationshipTypeId 
join dbo.BasemanagedEntity bme with (nolock) 
on bme.basemanagedentityid = r.SourceEntityId 
join dbo.BasemanagedEntity bme2 with (nolock) 
on r.TargetEntityId = bme2.BaseManagedEntityId 
where rt.RelationshipTypeName = 'Microsoft.SystemCenter.HealthServiceManagesEntity' 
and bme.IsDeleted = 0 
and r.IsDeleted = 0 
and bme2.basemanagedtypeid in (SELECT DerivedTypeId 
FROM DerivedManagedTypes with (nolock) 
WHERE BaseTypeId = (select managedtypeid 
from managedtype where typename = 'Microsoft.Unix.Computer') 
and DerivedIsAbstract = 0)

← Previous