A couple of useful websites for Lync 2013.
Dr Rez has the following list of resources for use with Lync 2013
http://blogs.technet.com/b/nexthop/p/lync2013resources.aspx
and some very cool tools, some free and some to buy from Microsoft Lync solutions..
Came across this product from Sangoma (means witch doctor in Zulu!) and it looks pretty cool.
You can have a look at the specs here:
http://www.sangoma.com/products/microsoft_unified_communications/lync_express/overview.html
There is a lot of really cool stuff you can do with with the Lync 2013 SDK.
Have a look here for code samples, documentation and how to get started:
http://msdn.microsoft.com/en-us/library/lync/jj162980%28v=office.15%29.aspx
Original post = http://ms-uc.herber.co/?p=281
While Lync Server 2013 is now in RTM, it’s also becoming time to watch for the certification path to be able to prove your Lync 2013 knowledge. Microsoft has redesigned it’s certification process and levels (again) and for Lync 2013, you want to become “Microsoft Certified Solutions Expert (MCSE): Communication”, the equalevent to “MCITP: Lync Server Administrator”. To become this, you need to pass some certifications again:
The noticed exams are not available (yet), but those are the ones to look out for! But…also notice the “MCSA: Windows Server 2012″ requirement. This is to prove your (basic) skills for Windows Server 2012. To become those certificates, you need to pass the following exams:
Happily, there is also an upgrade path for current Windows Server 2008 and/or Lync MCITP’s:
So, to be complete, for current MCITP’s to become an “MCSE: Communication” the following exams have to be followed:
The exams for Lync are already available, based on the Lync 2013 Public Preview and Server 2012 Preview:
The exams should be available for exam takers starting November 6th, 2012; However, they seem already to be bookable on the Prometric site!
(Borrowed the pictures from Microsoft: http://www.microsoft.com/learning/en/us/certification/cert-lync-server.aspx)
Well its a new product so there are always going to be some “teething” problems!
Microsoft are on top of it and you can find some known issues here:
http://officepreview.microsoft.com/en-us/help/lync-2013-known-issues-HA102919641.aspx
Lync 2013 is upon us and it IS the coolest version so far.. Have a look here for all the goodies:
Thought that this might be useful as a reference.
What Can I Do With Windows PowerShell?
A Task-Based Guide to Windows PowerShell Cmdlets
The beta has been out for a while but its worth looking at again! you can find more information about the service pack here:
A preview of some of the features:
With Windows 8, Windows RT devices, and Windows Phone 8 coming soon, we are excited to give you a preview of the new client management capabilities and vision we’ll be delivering in the coming months:
Configuration Manager SP1 enhancements include:
One of the most common places we take notes is at work during meetings. We do this to remind ourselves about how was there, what was said, and also who had been tasked with an action.
This has been made really easy with One Note 2013. Have a look at this blog to get the low down..
http://blogs.msdn.com/b/descapa/archive/2012/08/28/taking-meeting-notes-in-onenote-2013.aspx
Was looking for some PowerShell scripts and found this website which I found useful..
You can download slide decks and lab materials which are designed to train developers on using the development models and APIs associated with Lync 2013, UCMA 4.0, UC Web API, Persistent Chat, and Outlook apps.
http://www.microsoft.com/en-us/download/details.aspx?id=30465
I decided to install Windows 8 Enterprise soon after it was released. The install was painless, but activating turned out to be a bit of a pain to say the least.
To activate you have 2 options. You can use either use the command line or the gui.
To use the command line, open a command line and type in the following:
slmgr -ipk xxxxx-xxxxx-xxxxx-xxxxx-xxxxx
to get to the run command use the windows key combination : Windows key + R
Using the gui:
use the key combination described above and type in :
slui.exe 3
and you get the following dialogue box which makes it easy to activate your copy of Windows 8
Hope that helps!
A very useful place to go to learn about Private clouds…
http://technet.microsoft.com/en-gb/evalcenter/hh913622.aspx?ocid=&wt.mc_id=TEC_113_1_24
One of the cool new features of Lync 2013 is the ability to use VDI. As ever there is an article on Dr Rez. What this allows you to do is to is have enterprise grade, real-time audio and video communication in a virtual desktop infrastructure (VDI) environment, either from your thin clients, or from your thick clients
Read about it here:
I was asked by a delegate this week on how to manage Apple Mac’s and iPads in a corporate environment.
Here is a product that will do that for you:
Its called The Casper Suite and is by Jamf and you can see it here:
SCCM 2012 uses SQL 2008 R2 for storing its config and data. SCCM 2012 also use SQL Reporting Services 2008 R2 and there is a bug when you try edit a report.
this is taken from
so to save myself some typing here is his blog post:
For System Center Configuration Manager 2012 with SQL Server 2008 R2, you will receive this error while create/edit report from Console.
Error: Report Builder not available
Report Builder 2.0 is not installed as a click-once application on report server
To fix this, please change registry value to :
[HKLM\SOFTWARE\Wow6432Node\Microsoft\ConfigMgr10\AdminUI\Reporting]
"ReportBuilderApplicationManifestName"="ReportBuilder_3_0_0_0.application"
Download : ReportBuilder3.reg
P/S: after fix the registry still not work, please run Console as Administrator
If you need standalone Report Builder 3.0, download here.
More details from Microsoft
Lync 2013 preview is available for download and I have been testing it out and so far really like it! If you want to download and test then do an internet search and download as part of the Office 2013 suite.
To see the new features have a look at the Lync Team Blog which is:
http://blogs.technet.com/b/lync/archive/2012/08/03/what-s-new-in-lync-2013.aspx
This is a question I get a lot!
I used to go with the official Microsoft line of saying that it was an optional role, but after reading this article and having Keith Hanna from Microsoft do a superb explanation of how Directors work I have changed my mind. But I am not telling! Make your own mind up.
Here is the article: http://blogs.technet.com/b/dodeitte/archive/2010/12/26/another-reason-to-include-a-director-in-your-lync-server-2010-deployment.aspx?PageIndex=3#comments
As it says in the title! The master at work!
http://blogs.technet.com/b/jenstr/archive/2010/10/13/what-is-central-management-store-cms.aspx
Was looking into this recently with Thibaut and he sent me this link.
Makes it all very clear so have a look here:
As it says, and they can found here:
http://blogs.technet.com/b/scvmm/archive/2012/06/20/system-center-2012-guided-hands-on-labs.aspx
Have a look here for some really good link to resources about SC VMM 2012, App Controller and System Center Advisor.
Really good blog..
For a taster about re-routing incoming calls to auto attendant using MSPL scripting
http://ucken.blogspot.com/2012/02/re-routing-incoming-calls-to.html
See this here, is really useful..
http://ucken.blogspot.com/2012/04/inbound-number-normalization-bug-in.html
An interoperability scenario with Cisco CUCM and Lync with two different voicemail systems? How do you consolidate down to one voicemail platform? Well Exchange UM secondary dial plans might be the answer.
http://voipnorm.blogspot.com/2011/06/using-exchange-unified-messaging.html
For details about when the Kindle Fire will be released in the UK then see the Kindle users forum at:
Have a look here for:
“The Reference Architecture for Private Cloud document collection helps the IT service provider make the transformational journey to the cloud. The materials have a solid foundation in key business drivers. They provide a structured approach to making architectural decisions. The aim is to improve the quality of private cloud infrastructure design while realizing the efficiency gains possible with cloud computing.”
Quote from Microsoft website
I thought that I would remind myself about this site.
Really good site, check out the downloads….
Last week I was lucky enough to be be able to attend the private cloud jump start. The speaker were all brilliant, sadly for me it was also at night, but I can now review the videos and so can you!
Thanks to Symon and the other speakers! Great event.
Details of the videos are here:
Have a look at the following to help with Microsoft Cloud and virtulization training.
Get Certified in Microsoft Virtualization
Find a Learning Partner near you, and ask to take the Virtualization Challenge.
– http://aka.ms/10215-Locator
Need help preparing for your exam?
–Register now for this accelerated class to prepare for exam 70-659!
–Exam 70-659: Windows Server 2008 R2, Server Virtualization Jump Start
• Register now: http://aka.ms/70-659-Jump
Private Cloud Certification Resources
Learn more about your options for training, exams and certifications related to Microsoft Private Cloud
http://aka.ms/PrivateCloudCert
Trial Download
http://www.microsoft.com/en-us/server-cloud/evaluate/trial-software.aspx
http://technet.microsoft.com/en-us/evalcenter/hh505660.aspx
System Center 2012 Virtual Labs
http://technet.microsoft.com/en-us/virtuallabs/bb539977
Need more Conversational Cloud Confidence?
Check out our “Cloud 101 Jump Start”
–Learning experience tailored for business leaders
(CxOs, VPs, Directors, Sales & Marketing)
–Participate in the strategic conversation about why and how the
Cloud is transforming business.
–http://aka.ms/Cloud101
Quite often I want to test out something for one of the Microsoft products and don't have a server handy, never mind one configured with the products I want to test on!
Microsoft have thoughtfully provided some help for this and you can find them here:
handler wrapping in IIS
SMS Commander
SC BLOGs app for smartphone
eminetntware.com for patching non MS products
I have just spent the week teaching the new Beta Microsoft course for System Center Configuration Manager 2012.
Its an much improved product and as usual the courseware is very good…
Have a look at the Solution Accelerators, they are really useful in understand and deploying SCCM 2012.
Some interesting items to look at:
http://www.windows-noob.com/forums/index.php?app=portal for a step by step on installing various Microsoft products including SCCM 2012
http://www.paloaltonetworks.com/index.php for some really cool layer 7 firewall stuff
http://www.fireeye.com/ for fighting malware zero-day and targeted APT attacks that bypass traditional defences, such as next generation Firewalls, IPS, AV and Web gateways.
http://anoopmannur.blogspot.com/2011/04/configuration-manager-2012-sccm-2012.html for various SCCM 2012 bits.
I was talking to Darrel on my course this week and he was telling me about drupal which I had never heard off. So I had a look and it looks very interesting, so you can have a look here
He also told me about a free webhosting site: Have a look at www.000webhost.com
Found this website which looked interesting! It’s been around for a while I guess but sometimes a reminder about older products is useful..
In these difficult times it is often difficult to find a site that is focused in the right direction. If you need a job and are a Microsoft trained person then have a look at MSEmploy who you can find here:
http://www.msemploy.co.uk/browse-jobs/it-professional/
Hope this helps!!!!
I got this from Kevin Holman’s blog and wanted to keep it as a reference for myself….
Large Table query. (I am putting this at the top, because I use it so much – to find out what is taking up so much space in the OpsDB or DW)
SELECT TOP 1000
a2.name AS [tablename], (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.rows as row_count, a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,
(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
a3.name AS [schemaname]
FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN (SELECT it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
Database Size and used space. (People have a lot of confusion here – this will show the DB and log file size, plus the used/free space in each)
USE OperationsManager
select a.FILEID,
[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
[GROWTH_MB]=convert(decimal(12,2),round(a.growth/128.000,2)),
NAME=left(a.NAME,15),
FILENAME=left(a.FILENAME,60)
from dbo.sysfiles a
Operational Database Section:
Alerts Section:
Number of console Alerts per Day:
SELECT CONVERT(VARCHAR(20), TimeAdded, 102) AS DayAdded, COUNT(*) AS NumAlertsPerDay
FROM Alert WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102)
ORDER BY DayAdded DESC
Top 20 Alerts in an Operational Database, by Alert Count
SELECT TOP 20 SUM(1) AS AlertCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
FROM Alertview WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
ORDER BY AlertCount DESC
Top 20 Alerts in an Operational Database, by Repeat Count
SELECT TOP 20 SUM(RepeatCount+1) AS RepeatCount, AlertStringName, AlertStringDescription, MonitoringRuleId, Name
FROM Alertview WITH (NOLOCK)
WHERE Timeraised is not NULL
GROUP BY AlertStringName, AlertStringDescription, MonitoringRuleId, Name
ORDER BY RepeatCount DESC
Number of console Alerts per Day by Resolution State:
SELECT
CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1) THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeAdded, 102) END AS [Date],
CASE WHEN(GROUPING(ResolutionState) = 1) THEN 'All Resolution States' ELSE CAST(ResolutionState AS VARCHAR(5)) END AS [ResolutionState],
COUNT(*) AS NumAlerts
FROM Alert WITH (NOLOCK)
WHERE TimeRaised is not NULL
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102), ResolutionState WITH ROLLUP
ORDER BY DATE DESC
(Note: There will be more alerts in the "Alert" table in the form of rows, than exist in the console. This is because there are non-console alerts where TimeRaised is NULL - these have to do with driving state change records, and are not included in the above queries by design)
Events Section:
All Events by count by day, with total for entire database: (this tells us how many events per day we are inserting - and helps us look for too many events, event storms, and the result after tuning rules that generate too many events)
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeAdded, 102)) = 1)
THEN 'All Days'
ELSE CONVERT(VARCHAR(20), TimeAdded, 102) END AS DayAdded,
COUNT(*) AS EventsPerDay
FROM EventAllView
GROUP BY CONVERT(VARCHAR(20), TimeAdded, 102) WITH ROLLUP
ORDER BY DayAdded DESC
Most Common Events by event number: (This helps us know which event ID's are the most common in the database)
SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents
FROM EventView with (NOLOCK)
GROUP BY Number
ORDER BY TotalEvents DESC
Most common events by event number and event publishername: (This gives us the event source name to help see what is raising these events)
SELECT top 20 Number as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource
FROM EventAllView eav with (nolock)
GROUP BY Number, Publishername
ORDER BY TotalEvents DESC
Most common events, grouped by identical event number, publishername, and event parameters: (This shows use completely redundant events with identical data - but might be different than the above queries... you need to see both data outputs to fully tune)
SELECT top 100 Number as EventID, COUNT(*) AS TotalEvents, Publishername as EventSource, EventParameters
FROM EventAllView with (NOLOCK)
GROUP BY Number, Publishername, EventParameters
ORDER BY TotalEvents DESC
Computers generating the most events: (This shows us which computers create the most event traffic and use the most database space)
SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer
ORDER BY TotalEvents DESC
Computers generating the most events, by event number: (This shows the noisiest computers, group by unique event numbers)
SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, Number as EventID
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer, Number
ORDER BY TotalEvents DESC
Computers generating the most events, grouped by identical event number and publishername:
SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, PublisherName as EventSource, Number as EventID
FROM EventallView with (NOLOCK)
GROUP BY LoggingComputer, PublisherName, Number
ORDER BY TotalEvents DESC
Performance Section:
Performance insertions per day:
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeSampled, 102)) = 1)
THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeSampled, 102)
END AS DaySampled, COUNT(*) AS PerfInsertPerDay
FROM PerformanceDataAllView with (NOLOCK)
GROUP BY CONVERT(VARCHAR(20), TimeSampled, 102) WITH ROLLUP
ORDER BY DaySampled DESC
Top 20 performance insertions by perf object and counter name:
select top 20 pcv.ObjectName, pcv.CounterName, count (pcv.countername) as Total
from performancedataallview as pdv, performancecounterview as pcv
where (pdv.performancesourceinternalid = pcv.performancesourceinternalid)
group by pcv.objectname, pcv.countername
order by count (pcv.countername) desc
To view all performance insertions for a given computer:
select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = 'omterm.opsmgr.net'
order by countername, timesampled
To refine a the above query to pull all perf data for a given computer, object, counter, and instance:
select Path, ObjectName, CounterName, InstanceName, SampleValue, TimeSampled
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where path = 'omterm.opsmgr.net' AND
objectname = 'LogicalDisk' AND
countername = 'Free Megabytes'
order by timesampled
State Section:
To find out how old your StateChange data is:
declare @statedaystokeep INT
SELECT @statedaystokeep = DaysToKeep from PartitionAndGroomingSettings WHERE ObjectName = 'StateChangeEvent'
SELECT COUNT(*) as 'Total StateChanges',
count(CASE WHEN sce.TimeGenerated > dateadd(dd,-@statedaystokeep,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as 'within grooming retention',
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-@statedaystokeep,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as '> grooming retention',
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-30,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as '> 30 days',
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-90,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as '> 90 days',
count(CASE WHEN sce.TimeGenerated < dateadd(dd,-365,getutcdate()) THEN sce.TimeGenerated ELSE NULL END) as '> 365 days'
from StateChangeEvent sce
Cleanup old statechanges for disabled monitors:
State changes per day:
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), TimeGenerated, 102)) = 1)
THEN 'All Days' ELSE CONVERT(VARCHAR(20), TimeGenerated, 102)
END AS DayGenerated, COUNT(*) AS StateChangesPerDay
FROM StateChangeEvent WITH (NOLOCK)
GROUP BY CONVERT(VARCHAR(20), TimeGenerated, 102) WITH ROLLUP
ORDER BY DayGenerated DESC
Noisiest monitors changing state in the database in the last 7 days:
select distinct top 50 count(sce.StateId) as NumStateChanges,
m.DisplayName as MonitorDisplayName,
m.Name as MonitorIdName,
mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join monitorview m with (nolock) on s.MonitorId = m.Id
join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
-- Scoped to within last 7 days
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate())
group by m.DisplayName, m.Name,mt.typename
order by NumStateChanges desc
Noisiest Monitor in the database – PER Object/Computer in the last 7 days:
select distinct top 50 count(sce.StateId) as NumStateChanges,
bme.DisplayName AS ObjectName,
bme.Path,
m.DisplayName as MonitorDisplayName,
m.Name as MonitorIdName,
mt.typename AS TargetClass
from StateChangeEvent sce with (nolock)
join state s with (nolock) on sce.StateId = s.StateId
join BaseManagedEntity bme with (nolock) on s.BasemanagedEntityId = bme.BasemanagedEntityId
join MonitorView m with (nolock) on s.MonitorId = m.Id
join managedtype mt with (nolock) on m.TargetMonitoringClassId = mt.ManagedTypeId
where m.IsUnitMonitor = 1
-- Scoped to specific Monitor (remove the "--" below):
-- AND m.MonitorName like ('%HealthService%')
-- Scoped to specific Computer (remove the "--" below):
-- AND bme.Path like ('%sql%')
-- Scoped to within last 7 days
AND sce.TimeGenerated > dateadd(dd,-7,getutcdate())
group by s.BasemanagedEntityId,bme.DisplayName,bme.Path,m.DisplayName,m.Name,mt.typename
order by NumStateChanges desc
Performance Signature Section:
To find the rules collecting the most Performance Signature data in the database:
select managementpack.MPName, ruleview.DisplayName,
count(*) AS TotalPerfSig
from performancesignaturedata with (nolock)
inner join performancesignaturehistory with (nolock)
on performancesignaturedata.performancesignaturehistoryid = performancesignaturehistory.performancesignaturehistoryid
inner join performancesignature with (nolock)
on performancesignaturehistory.performancesignatureid = performancesignature.performancesignatureid
inner join ruleview with (nolock)
on ruleview.id = performancesignature.learningruleid
inner join managementpack with(nolock)
on ruleview.managementpackid = managementpack.managementpackid
group by managementpack.mpname, ruleview.Displayname
order by TotalPerfSig DESC, managementpack.mpname, ruleview.DisplayName
To find all Performance Signature Collection rules:
select managementpack.mpname, rules.rulename
from performancesignature with (nolock)
inner join rules with (nolock)
on rules.ruleid = performancesignature.learningruleid
inner join managementpack with(nolock)
on rules.managementpackid = managementpack.managementpackid
group by managementpack.mpname, rules.rulename
order by managementpack.mpname, rules.rulename
Management Pack info:
Rules section:
To find a common rule name given a Rule ID name:
SELECT DisplayName from RuleView
where name = 'Microsoft.SystemCenter.GenericNTPerfMapperModule.FailedExecution.Alert'
-- change the 'name' value above to the Rule ID shown in an alert
Rules per MP:
SELECT mp.MPName, COUNT(*) As RulesPerMP
FROM Rules r
INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID
GROUP BY mp.MPName
ORDER BY RulesPerMP DESC
Rules per MP by category:
SELECT mp.MPName, r.RuleCategory, COUNT(*) As RulesPerMPPerCategory
FROM Rules r
INNER JOIN ManagementPack mp ON mp.ManagementPackID = r.ManagementPackID
GROUP BY mp.MPName, r.RuleCategory
ORDER BY RulesPerMPPerCategory DESC
To find all Rules per MP that generate an alert:
declare @mpid as varchar(50)
select @mpid= managementpackid from managementpack where
mpName='Microsoft.Exchange.2007'
select rl.rulename,rl.ruleid,md.modulename from rules rl, module md
where md.managementpackid = @mpid
and rl.ruleid=md.parentid
and moduleconfiguration like '%<AlertLevel>50</AlertLevel>%'
To find all rules per MP with a given alert severity:
declare @mpid as varchar(50)
select @mpid= managementpackid from managementpack where
mpName='Microsoft.Exchange.Server.2003.Monitoring'
select rl.rulename,rl.ruleid,md.modulename from rules rl, module md
where md.managementpackid = @mpid
and rl.ruleid=md.parentid
and moduleconfiguration like '%<Severity>2</Severity>%'
Rules are stored in a table named Rules. This table has columns linking rules to classes and Management Packs. To find all rules in a Management Pack use the following query and substitute in the required Management Pack name:
SELECT * FROM Rules WHERE ManagementPackID = (SELECT ManagementPackID from ManagementPack WHERE MPName = 'Microsoft.Windows.Server.2003')
To find all rules targeted at a given class use the following query and substitute in the required class name:
SELECT * FROM Rules WHERE TargetManagedEntityType = (SELECT ManagedTypeId FROM ManagedType WHERE TypeName = 'Microsoft.Windows.Computer')
Monitors Section:
Monitors Per MP:
SELECT mp.MPName, COUNT(*) As MonitorsPerMPPerCategory
FROM Monitor m
INNER JOIN ManagementPack mp ON mp.ManagementPackID = m.ManagementPackID
GROUP BY mp.MPName
ORDER BY COUNT(*) Desc
To find your Monitor by common name:
select * from Monitor m
Inner join LocalizedText LT on LT.ElementName = m.MonitorName
where LTValue = 'Monitor Common Name'
To find your Monitor by ID name:
select * from Monitor m
Inner join LocalizedText LT on LT.ElementName = m.MonitorName
where m.monitorname = 'Monitor ID name'
To find all monitors targeted at a specific class:
SELECT * FROM monitor WHERE TargetManagedEntityType = (SELECT ManagedTypeId FROM ManagedType WHERE TypeName = 'Microsoft.Windows.Computer')
Groups Section:
To find all groups for a given computer/object (change “computername” in the query below):
SELECT SourceMonitoringObjectDisplayName AS 'Group'
FROM RelationshipGenericView
WHERE TargetMonitoringObjectDisplayName like ('%computername%')
AND (SourceMonitoringObjectDisplayName IN
(SELECT ManagedEntityGenericView.DisplayName
FROM ManagedEntityGenericView INNER JOIN
(SELECT BaseManagedEntityId
FROM BaseManagedEntity WITH (NOLOCK)
WHERE (BaseManagedEntityId = TopLevelHostEntityId) AND (BaseManagedEntityId NOT IN
(SELECT R.TargetEntityId
FROM Relationship AS R WITH (NOLOCK) INNER JOIN
dbo.fn_ContainmentRelationshipTypes() AS CRT ON R.RelationshipTypeId = CRT.RelationshipTypeId
WHERE (R.IsDeleted = 0)))) AS GetTopLevelEntities ON
GetTopLevelEntities.BaseManagedEntityId = ManagedEntityGenericView.Id INNER JOIN
(SELECT DISTINCT BaseManagedEntityId
FROM TypedManagedEntity WITH (NOLOCK)
WHERE (ManagedTypeId IN
(SELECT DerivedManagedTypeId
FROM dbo.fn_DerivedManagedTypes(dbo.fn_ManagedTypeId_Group()) AS fn_DerivedManagedTypes_1))) AS GetOnlyGroups ON
GetOnlyGroups.BaseManagedEntityId = ManagedEntityGenericView.Id))
ORDER BY 'Group'
To find all members of a given group (change the group name below):
select SourceMonitoringObjectDisplayName as 'Group Name',
TargetMonitoringObjectDisplayName as 'Group Members'
from RelationshipGenericView
where isDeleted=0
AND SourceMonitoringObjectDisplayName = 'Agent Managed Computer Group'
ORDER BY TargetMonitoringObjectDisplayName
Find find the entity data on all members of a given group (change the group name below):
SELECT bme.*
FROM BaseManagedEntity bme
INNER JOIN RelationshipGenericView rgv WITH(NOLOCK) ON bme.basemanagedentityid = rgv.TargetMonitoringObjectid
WHERE bme.IsDeleted = '0'
AND rgv.SourceMonitoringObjectDisplayName = 'Agent Managed Computer Group'
ORDER BY bme.displayname
Management Pack general:
To find all installed Management Packs and their version:
SELECT MPName, MPFriendlyName, MPVersion, MPIsSealed
FROM ManagementPack WITH(NOLOCK)
ORDER BY MPName
Number of Views per Management Pack:
SELECT mp.MPName, v.ViewVisible, COUNT(*) As ViewsPerMP
FROM [Views] v
INNER JOIN ManagementPack mp ON mp.ManagementPackID = v.ManagementPackID
GROUP BY mp.MPName, v.ViewVisible
ORDER BY v.ViewVisible DESC, COUNT(*) Desc
How to gather all the views in the database, their ID, MP location, and view type:
select vv.id as 'View Id',
vv.displayname as 'View DisplayName',
vv.name as 'View Name',
vtv.DisplayName as 'ViewType',
mpv.FriendlyName as 'MP Name'
from ViewsView vv
inner join managementpackview mpv on mpv.id = vv.managementpackid
inner join viewtypeview vtv on vtv.id = vv.monitoringviewtypeid
--where mpv.FriendlyName like '%default%'
--where vv.displayname like '%operating%'
order by mpv.FriendlyName, vv.displayname
Classes available in the DB:
SELECT * FROM ManagedType
Classes available in the DB for Microsoft Windows type:
SELECT * FROM ManagedType WHERE TypeName LIKE 'Microsoft.Windows.%'
Every property of every class:
SELECT * FROM MT_Computer
All instances of all types once discovered
SELECT * FROM BaseManagedEntity
To get the state of every instance of a particular monitor the following query can be run, (replace <MonitorName> with the name of the monitor):
SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = =<MonitorName>‘)
For example, this gets the state of the Microsoft.SQLServer.2005.DBEngine.ServiceMonitor for each instance of the SQL 2005 Database Engine class.
SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = 'Microsoft.SQLServer.2005.DBEngine.ServiceMonitor')
To find the overall state of any object in OpsMgr the following query should be used to return the state of the System.EntityState monitor:
SELECT bme.FullName, bme.DisplayName, s.HealthState FROM state AS s, mt_managedcomputer AS mt, BaseManagedEntity as bme WHERE s.basemanagedentityid = bme.basemanagedentityid AND s.monitorid IN (SELECT MonitorId FROM Monitor WHERE MonitorName = 'System.Health.EntityState')
The Alert table contains all alerts currently open in OpsMgr. This includes resolved alerts until they are groomed out of the database. To get all alerts across all instances of a given monitor use the following query and substitute in the required monitor name:
SELECT * FROM Alert WHERE ProblemID IN (SELECT MonitorId FROM Monitor WHERE MonitorName = 'Microsoft.SQLServer.2005.DBEngine.ServiceMonitor')
To retrieve all alerts for all instances of a specific class use the following query and substitute in the required table name, in this example MT_DBEngine is used to look for SQL alerts:
SELECT * FROM Alert WHERE BaseManagedEntityID IN (SELECT BaseManagedEntityID from MT_DBEngine)
To determine which table is currently being written to for event and performance data use the following query:
SELECT * FROM PartitionTables WHERE IsCurrent = 1
To retrieve events generated by a specific rule use the following query and substitute in the required rule ID:
SELECT * FROM Event_00 WHERE RuleId = (SELECT RuleId FROM Rules WHERE RuleName = 'Microsoft.Windows.Server.2003.OperatingSystem.CleanShutdown.Collection ')
To retrieve all events generated by rules in a specific Management Pack the following query can be used where the Management Pack name is substituted with the required value:
SELECT * FROM EventAllView WHERE RuleID IN (SELECT RuleId FROM Rules WHERE ManagementPackId = (SELECT ManagementPackId FROM ManagementPack WHERE MPName = 'Microsoft.Windows.Server.2003'))
Number of instances of a type: (Number of disks, computers, databases, etc that OpsMgr has discovered)
SELECT mt.ManagedTypeID, mt.TypeName, COUNT(*) AS NumEntitiesByType
FROM BaseManagedEntity bme WITH(NOLOCK)
LEFT JOIN ManagedType mt WITH(NOLOCK) ON mt.ManagedTypeID = bme.BaseManagedTypeID
WHERE bme.IsDeleted = 0
GROUP BY mt.ManagedTypeID, mt.TypeName
ORDER BY COUNT(*) DESC
To retrieve all performance data for a given rule in a readable format use the following query: (change the r.RuleName value – get list from Rules Table)
SELECT bme.Path, pc.ObjectName, pc.CounterName, ps.PerfmonInstanceName, pdav.SampleValue, pdav.TimeSampled
FROM PerformanceDataAllView AS pdav with (NOLOCK)
INNER JOIN PerformanceSource ps on pdav.PerformanceSourceInternalId = ps.PerformanceSourceInternalId
INNER JOIN PerformanceCounter pc on ps.PerformanceCounterId = pc.PerformanceCounterId
INNER JOIN Rules r on ps.RuleId = r.RuleId
INNER JOIN BaseManagedEntity bme on ps.BaseManagedEntityID = bme.BaseManagedEntityID
WHERE r.RuleName = 'Microsoft.Windows.Server.2003.LogicalDisk.FreeSpace.Collection'
GROUP BY PerfmonInstanceName, ObjectName, CounterName, SampleValue, TimeSampled, bme.path
ORDER BY bme.path, PerfmonInstanceName, TimeSampled
To determine what discoveries are still associated with a computer – helpful in finding old stale computer objects in the console that are no longer agent managed, or desired.
select BME.FullName, DS.DiscoveryRuleID, D.DiscoveryName from typedmanagedentity TME
Join BaseManagedEntity BME ON TME.BaseManagedEntityId = BME.BaseManagedEntityId
JOIN DiscoverySourceToTypedManagedEntity DSTME ON TME.TypedManagedEntityID = DSTME.TypedManagedEntityID
JOIN DiscoverySource DS ON DS.DiscoverySourceID = DSTME.DiscoverySourceID
JOIN Discovery D ON DS.DiscoveryRuleID=D.DiscoveryID
Where BME.Fullname like '%ComputerName%'
To dump out all the rules and monitors that have overrides, and display the context and instance of the override:
select rv.DisplayName as WorkFlowName, OverrideName, mo.Value as OverrideValue,
mt.TypeName as OverrideScope, bme.DisplayName as InstanceName, bme.Path as InstancePath,
mpv.DisplayName as ORMPName, mo.LastModified as LastModified
from ModuleOverride mo
inner join managementpackview mpv on mpv.Id = mo.ManagementPackId
inner join ruleview rv on rv.Id = mo.ParentId
inner join ManagedType mt on mt.managedtypeid = mo.TypeContext
left join BaseManagedEntity bme on bme.BaseManagedEntityId = mo.InstanceContext
Where mpv.Sealed = 0
UNION ALL
select mv.DisplayName as WorkFlowName, OverrideName, mto.Value as OverrideValue,
mt.TypeName as OverrideScope, bme.DisplayName as InstanceName, bme.Path as InstancePath,
mpv.DisplayName as ORMPName, mto.LastModified as LastModified
from MonitorOverride mto
inner join managementpackview mpv on mpv.Id = mto.ManagementPackId
inner join monitorview mv on mv.Id = mto.MonitorId
inner join ManagedType mt on mt.managedtypeid = mto.TypeContext
left join BaseManagedEntity bme on bme.BaseManagedEntityId = mto.InstanceContext
Where mpv.Sealed = 0
Order By mpv.DisplayName
Agent Info:
To find all managed computers that are currently down and not pingable:
SELECT bme.DisplayName,s.LastModified as LastModifiedUTC, dateadd(hh,-5,s.LastModified) as 'LastModifiedCST (GMT-5)'
FROM state AS s, BaseManagedEntity AS bme
WHERE s.basemanagedentityid = bme.basemanagedentityid
AND s.monitorid
IN (SELECT MonitorId FROM Monitor WHERE MonitorName = 'Microsoft.SystemCenter.HealthService.ComputerDown')
AND s.Healthstate = '3' AND bme.IsDeleted = '0'
ORDER BY s.Lastmodified DESC
All managed computers count:
SELECT COUNT(*) AS NumManagedComps FROM (
SELECT bme2.BaseManagedEntityID
FROM BaseManagedEntity bme WITH (NOLOCK)
INNER JOIN BaseManagedEntity bme2 WITH (NOLOCK) ON bme2.BaseManagedEntityID = bme.TopLevelHostEntityID
WHERE bme2.IsDeleted = 0
AND bme2.IsDeleted = 0
AND bme2.BaseManagedTypeID = (SELECT TOP 1 ManagedTypeID FROM ManagedType WHERE TypeName = 'microsoft.windows.computer')
GROUP BY bme2.BaseManagedEntityID
) AS Comps
To find a computer name from a HealthServiceID (guid from the Agent proxy alerts)
select DisplayName, Path, basemanagedentityid from basemanagedentity where basemanagedentityid = 'guid'
To view the agent patch list (all hotfixes applied to all agents)
select bme.path AS 'Agent Name', hs.patchlist AS 'Patch List' from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
order by path
To view all agents missing a specific hotfix (change the KB number below to the one you are looking for):
select bme.path AS 'Agent Name', hs.patchlist AS 'Patch List' from MT_HealthService hs
inner join BaseManagedEntity bme on hs.BaseManagedEntityId = bme.BaseManagedEntityId
where hs.patchlist not like '%951380%'
order by path
Here is a query to see all Agents which are manually installed:
select bme.DisplayName from MT_HealthService mths
INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId
where IsManuallyInstalled = 1
Here is a query that will set all agents back to Remotely Manageable:
UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1
Now – the above query will set ALL agents back to “Remotely Manageable = Yes” in the console. If you want to control it agent by agent – you need to specify it by name here:
UPDATE MT_HealthService
SET IsManuallyInstalled=0
WHERE IsManuallyInstalled=1
AND BaseManagedEntityId IN
(select BaseManagedEntityID from BaseManagedEntity
where BaseManagedTypeId = 'AB4C891F-3359-3FB6-0704-075FBFE36710'
AND DisplayName = 'servername.domain.com')
Get the instance space of all agents (Thanks to Hui and Michael Pearson)
DECLARE @RelationshipTypeId_Manages UNIQUEIDENTIFIER
SELECT @RelationshipTypeId_Manages = dbo.fn_RelationshipTypeId_Manages()
SELECT bme.FullName, dt.TopLevelEntityName, dt.BaseEntityName, dt.TypedEntityName
FROM BaseManagedEntity bme
RIGHT JOIN (
SELECT
HBME.BaseManagedEntityId AS HS_BMEID,
TBME.FullName AS TopLevelEntityName,
BME.FullName AS BaseEntityName,
TYPE.TypeName AS TypedEntityName
FROM BaseManagedEntity BME WITH(NOLOCK)
INNER JOIN TypedManagedEntity TME WITH(NOLOCK) ON BME.BaseManagedEntityId = TME.BaseManagedEntityId AND BME.IsDeleted = 0 AND TME.IsDeleted = 0
INNER JOIN BaseManagedEntity TBME WITH(NOLOCK) ON BME.TopLevelHostEntityId = TBME.BaseManagedEntityId AND TBME.IsDeleted = 0
INNER JOIN ManagedType TYPE WITH(NOLOCK) ON TME.ManagedTypeID = TYPE.ManagedTypeID
LEFT JOIN Relationship R WITH(NOLOCK) ON R.TargetEntityId = TBME.BaseManagedEntityId AND R.RelationshipTypeId = @RelationshipTypeId_Manages AND R.IsDeleted = 0
LEFT JOIN BaseManagedEntity HBME WITH(NOLOCK) ON R.SourceEntityId = HBME.BaseManagedEntityId
) AS dt ON dt.HS_BMEID = bme.BaseManagedEntityId
ORDER BY bme.FullName, BaseEntityName
Get the discovered instance count of the top 50 agents (Thanks to Hui and Michael Pearson)
DECLARE @RelationshipTypeId_Manages UNIQUEIDENTIFIER
SELECT @RelationshipTypeId_Manages = dbo.fn_RelationshipTypeId_Manages()
SELECT TOP 50 bme.DisplayName, SUM(1) AS HostedInstances
FROM BaseManagedEntity bme
RIGHT JOIN (
SELECT
HBME.BaseManagedEntityId AS HS_BMEID,
TBME.FullName AS TopLevelEntityName,
BME.FullName AS BaseEntityName,
TYPE.TypeName AS TypedEntityName
FROM BaseManagedEntity BME WITH(NOLOCK)
INNER JOIN TypedManagedEntity TME WITH(NOLOCK) ON BME.BaseManagedEntityId = TME.BaseManagedEntityId AND BME.IsDeleted = 0 AND TME.IsDeleted = 0
INNER JOIN BaseManagedEntity TBME WITH(NOLOCK) ON BME.TopLevelHostEntityId = TBME.BaseManagedEntityId AND TBME.IsDeleted = 0
INNER JOIN ManagedType TYPE WITH(NOLOCK) ON TME.ManagedTypeID = TYPE.ManagedTypeID
LEFT JOIN Relationship R WITH(NOLOCK) ON R.TargetEntityId = TBME.BaseManagedEntityId AND R.RelationshipTypeId = @RelationshipTypeId_Manages AND R.IsDeleted = 0
LEFT JOIN BaseManagedEntity HBME WITH(NOLOCK) ON R.SourceEntityId = HBME.BaseManagedEntityId
) AS dt ON dt.HS_BMEID = bme.BaseManagedEntityId
GROUP by BME.displayname
order by HostedInstances DESC
Instance space per class (not happy with this – includes duplicates for clusters)
DECLARE @RelationshipTypeId_Manages UNIQUEIDENTIFIER
SELECT @RelationshipTypeId_Manages = dbo.fn_RelationshipTypeId_Manages()
SELECT TypedEntityName, SUM(1) AS InstanceCount
FROM BaseManagedEntity bme
RIGHT JOIN (
SELECT
HBME.BaseManagedEntityId AS HS_BMEID,
TBME.FullName AS TopLevelEntityName,
BME.FullName AS BaseEntityName,
TYPE.TypeName AS TypedEntityName
FROM BaseManagedEntity BME WITH(NOLOCK)
INNER JOIN TypedManagedEntity TME WITH(NOLOCK) ON BME.BaseManagedEntityId = TME.BaseManagedEntityId AND BME.IsDeleted = 0 AND TME.IsDeleted = 0
INNER JOIN BaseManagedEntity TBME WITH(NOLOCK) ON BME.TopLevelHostEntityId = TBME.BaseManagedEntityId AND TBME.IsDeleted = 0
INNER JOIN ManagedType TYPE WITH(NOLOCK) ON TME.ManagedTypeID = TYPE.ManagedTypeID
LEFT JOIN Relationship R WITH(NOLOCK) ON R.TargetEntityId = TBME.BaseManagedEntityId AND R.RelationshipTypeId = @RelationshipTypeId_Manages AND R.IsDeleted = 0
LEFT JOIN BaseManagedEntity HBME WITH(NOLOCK) ON R.SourceEntityId = HBME.BaseManagedEntityId
) AS dt ON dt.HS_BMEID = bme.BaseManagedEntityId
GROUP by TypedEntityName
order by InstanceCount DESC
Misc OpsDB:
To view grooming info:
SELECT * FROM PartitionAndGroomingSettings WITH (NOLOCK)
Information on existing User Roles:
SELECT UserRoleName, IsSystem from userrole
Operational DB version:
select DBVersion from __MOMManagementGroupInfo__
To view all Run-As Profiles, their associated Run-As account, and associated agent name:
select srv.displayname as 'RunAs Profile Name',
srv.description as 'RunAs Profile Description',
cmss.name as 'RunAs Account Name',
cmss.description as 'RunAs Account Description',
cmss.username as 'RunAs Account Username',
cmss.domain as 'RunAs Account Domain',
mp.FriendlyName as 'RunAs Profile MP',
bme.displayname as 'HealthService'
from dbo.SecureStorageSecureReference sssr
inner join SecureReferenceView srv on srv.id = sssr.securereferenceID
inner join CredentialManagerSecureStorage cmss on cmss.securestorageelementID = sssr.securestorageelementID
inner join managementpackview mp on srv.ManagementPackId = mp.Id
inner join BaseManagedEntity bme on bme.basemanagedentityID = sssr.healthserviceid
order by srv.displayname
To clean up old StateChangeEvent data for state changes that are older than the defined grooming period, such as monitors currently in a disabled, warning, or critical state. By default we only groom monitor statechangeevents where the monitor is enabled and healthy at the time of grooming.
USE [OperationsManager]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
SET NOCOUNT ON
DECLARE @Err int
DECLARE @Ret int
DECLARE @DaysToKeep tinyint
DECLARE @GroomingThresholdLocal datetime
DECLARE @GroomingThresholdUTC datetime
DECLARE @TimeGroomingRan datetime
DECLARE @MaxTimeGroomed datetime
DECLARE @RowCount int
SET @TimeGroomingRan = getutcdate()
SELECT @GroomingThresholdLocal = dbo.fn_GroomingThreshold(DaysToKeep, getdate())
FROM dbo.PartitionAndGroomingSettings
WHERE ObjectName = 'StateChangeEvent'
EXEC dbo.p_ConvertLocalTimeToUTC @GroomingThresholdLocal, @GroomingThresholdUTC OUT
SET @Err = @@ERROR
IF (@Err <> 0)
BEGIN
GOTO Error_Exit
END
SET @RowCount = 1
-- This is to update the settings table
-- with the max groomed data
SELECT @MaxTimeGroomed = MAX(TimeGenerated)
FROM dbo.StateChangeEvent
WHERE TimeGenerated < @GroomingThresholdUTC
IF @MaxTimeGroomed IS NULL
GOTO Success_Exit
-- Instead of the FK DELETE CASCADE handling the deletion of the rows from
-- the MJS table, do it explicitly. Performance is much better this way.
DELETE MJS
FROM dbo.MonitoringJobStatus MJS
JOIN dbo.StateChangeEvent SCE
ON SCE.StateChangeEventId = MJS.StateChangeEventId
JOIN dbo.State S WITH(NOLOCK)
ON SCE.[StateId] = S.[StateId]
WHERE SCE.TimeGenerated < @GroomingThresholdUTC
AND S.[HealthState] in (0,1,2,3)
SELECT @Err = @@ERROR
IF (@Err <> 0)
BEGIN
GOTO Error_Exit
END
WHILE (@RowCount > 0)
BEGIN
-- Delete StateChangeEvents that are older than @GroomingThresholdUTC
-- We are doing this in chunks in separate transactions on
-- purpose: to avoid the transaction log to grow too large.
DELETE TOP (10000) SCE
FROM dbo.StateChangeEvent SCE
JOIN dbo.State S WITH(NOLOCK)
ON SCE.[StateId] = S.[StateId]
WHERE TimeGenerated < @GroomingThresholdUTC
AND S.[HealthState] in (0,1,2,3)
SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT
IF (@Err <> 0)
BEGIN
GOTO Error_Exit
END
END
UPDATE dbo.PartitionAndGroomingSettings
SET GroomingRunTime = @TimeGroomingRan,
DataGroomedMaxTime = @MaxTimeGroomed
WHERE ObjectName = 'StateChangeEvent'
SELECT @Err = @@ERROR, @RowCount = @@ROWCOUNT
IF (@Err <> 0)
BEGIN
GOTO Error_Exit
END
Success_Exit:
Error_Exit:
END
Data Warehouse Database Section:
Alerts Section:
To get all raw alert data from the data warehouse to build reports from:
select * from Alert.vAlertResolutionState ars
inner join Alert.vAlertDetail adt on ars.alertguid = adt.alertguid
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
To view data on all alerts modified by a specific user:
select ars.alertguid, alertname, alertdescription, statesetbyuserid, resolutionstate, statesetdatetime, severity, priority, managedentityrowID, repeatcount
from Alert.vAlertResolutionState ars
inner join Alert.vAlert alt on ars.alertguid = alt.alertguid
where statesetbyuserid like '%username%'
order by statesetdatetime
To view a count of all alerts closed by all users:
select statesetbyuserid, count(*) as 'Number of Alerts'
from Alert.vAlertResolutionState ars
where resolutionstate = '255'
group by statesetbyuserid
order by 'Number of Alerts' DESC
Events Section:
To inspect total events in DW, and then break it down per day: (this helps us know what we will be grooming out, and look for partitcular day event storms)
SELECT CASE WHEN(GROUPING(CONVERT(VARCHAR(20), DateTime, 101)) = 1)
THEN 'All Days'
ELSE CONVERT(VARCHAR(20), DateTime, 101) END AS DayAdded,
COUNT(*) AS NumEventsPerDay
FROM Event.vEvent
GROUP BY CONVERT(VARCHAR(20), DateTime, 101) WITH ROLLUP
ORDER BY DayAdded DESC
Most Common Events by event number: (This helps us know which event ID's are the most common in the database)
SELECT top 50 EventDisplayNumber, COUNT(*) AS TotalEvents
FROM Event.vEvent
GROUP BY EventDisplayNumber
ORDER BY TotalEvents DESC
Most common events by event number and raw event description (this will take a very long time to run but it shows us not only event ID - but a description of the event to help understand which MP is the generating the noise)
SELECT top 50 EventDisplayNumber, Rawdescription, COUNT(*) AS TotalEvents
FROM Event.vEvent evt
inner join Event.vEventDetail evtd on evt.eventoriginid = evtd.eventoriginid
GROUP BY EventDisplayNumber, Rawdescription
ORDER BY TotalEvents DESC
To view all event data in the DW for a given Event ID:
select * from Event.vEvent ev
inner join Event.vEventDetail evd on ev.eventoriginid = evd.eventoriginid
inner join Event.vEventParameter evp on ev.eventoriginid = evp.eventoriginid
where eventdisplaynumber = '528'
To search for all computers who have NOT logged a specific event in the DW:
select distinct elc.computername from Event.vEvent ev
inner join vEventLoggingComputer elc on elc.eventloggingcomputerrowid = ev.loggingcomputerrowid
where NOT eventdisplaynumber = '223'
Performance Section:
Raw data – core query:
select top 10 *
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Raw data - More selective of “interesting” output data:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Raw data - Scoped to a ComputerName (FQDN)
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE Path = 'OMDB.opsmgr.net'
Raw data - Scoped to a Counter:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = 'Private Bytes'
Raw data - Scoped to a Computer and Counter:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = 'Private Bytes'
AND Path = 'OMDB.OPSMGR.NET'
Raw data - Ordered By DateTime:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, DateTime
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = 'Private Bytes'
AND Path = 'OMDB.OPSMGR.NET'
Order By DateTime DESC
Raw data - Modified DateTime relative to Central Time zone:
select top 10 Path, FullName, ObjectName, CounterName, InstanceName, SampleValue, dateadd(hh,-5,DateTime) as 'DateTime (GMT-5)'
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
WHERE CounterName = 'Private Bytes'
AND Path = 'OMDB.OPSMGR.NET'
Order By DateTime DESC
Raw data - How to get all the possible optional data to modify these queries above, in a list:
Select Distinct Path
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Select Distinct Fullname
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Select Distinct ObjectName
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Select Distinct CounterName
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Select Distinct InstanceName
from Perf.vPerfRaw pvpr
inner join vManagedEntity vme on pvpr.ManagedEntityRowId = vme.ManagedEntityRowId
inner join vPerformanceRuleInstance vpri on pvpr.PerformanceRuleInstanceRowId = vpri.PerformanceRuleInstanceRowId
inner join vPerformanceRule vpr on vpr.RuleRowId = vpri.RuleRowId
Grooming in the DataWarehouse:
Grooming no longer uses SQL agent jobs. Grooming is handled by scheduled stored procedures, that run much more frequently, which provides less impact than in the previous version.
Default grooming for the DW for each dataset, to examine Data Warehouse grooming settings:
SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes FROM StandardDatasetAggregation
The first row is the interval in minutes.
NULL is raw data, 60 is hourly, and 1440 is daily.
The second and third row shows what data it is
MaxDataAgeDays has the retention period in days - this is the field to update if the administrator wants to lower the days of retention.
RAW alert – 400 days
RAW event – 100 days
RAW perf – 10 days (hourly and daily perf = 400 days)
RAW state – 180 days (hourly and daily state = 400 days)
Here is a better view of the current data retention in your data warehouse:
select ds.datasetDefaultName AS 'Dataset Name', sda.AggregationTypeId AS 'Agg Type 0=raw, 20=Hourly, 30=Daily', sda.MaxDataAgeDays AS 'Retention Time in Days'
from dataset ds, StandardDatasetAggregation sda
WHERE ds.datasetid = sda.datasetid
ORDER by ds.datasetDefaultName
To view the number of days of total data of each type in the DW:
SELECT DATEDIFF(d, MIN(DWCreatedDateTime), GETDATE()) AS [Current] FROM Alert.vAlert
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Event.vEvent
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfRaw
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfHourly
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM Perf.vPerfDaily
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM State.vStateRaw
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM State.vStateHourly
SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current] FROM State.vStateDaily
To view the oldest and newest recorded timestamps of each data type in the DW:
select min(DateTime) from Event.vEvent
select max(DateTime) from Event.vEvent
select min(DateTime) from Perf.vPerfRaw
select max(DateTime) from Perf.vPerfRaw
select min(DWCreatedDateTime) from Alert.vAlert
select max(DWCreatedDateTime) from Alert.vAlert
AEM Queries (Data Warehouse):
Default query to return all RAW AEM data:
select * from [CM].[vCMAemRaw] Rw
inner join dbo.AemComputer Computer on Computer.AemComputerRowID = Rw.AemComputerRowID
inner join dbo.AemUser Usr on Usr.AemUserRowId = Rw.AemUserRowId
inner join dbo.AemErrorGroup EGrp on Egrp.ErrorGroupRowId = Rw.ErrorGroupRowId
Inner join dbo.AemApplication App on App.ApplicationRowId = Egrp.ApplicationRowId
Count the raw crashes per day:
SELECT CONVERT(char(10), DateTime, 101) AS "Crash Date (by Day)", COUNT(*) AS "Number of Crashes"
FROM [CM].[vCMAemRaw]
GROUP BY CONVERT(char(10), DateTime, 101)
ORDER BY "Crash Date (by Day)" DESC
Count the total number of raw crashes in the DW database:
select count(*) from CM.vCMAemRaw
Default grooming for the DW for the AEM dataset: (Aggregated data kept for 400 days, RAW 30 days by default)
SELECT AggregationTypeID, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes
FROM StandardDatasetAggregation WHERE BuildAggregationStoredProcedureName = 'AemAggregate'
Misc Section:
Simple query to display large tables, to determine what is taking up space in the database:
SELECT so.name,
8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb,
Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE 'U' = so.type GROUP BY so.name ORDER BY data_kb DESC
Is SQL broker enabled?
SELECT is_broker_enabled FROM sys.databases WHERE name = 'OperationsManager'
How to identify your version of SQL server:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
SQL 2005:
SQL Server 2005 RTM 2005.90.1399
SQL Server 2005 SP1 2005.90.2047
SQL Server 2005 SP1 plus 918222 2005.90.2153
SQL Server 2005 SP2 2005.90.3042
How to identify your version of OpsMgr 2007:
RTM: 6.0.5000.0
SP1-RC: 6.0.6246.0
SP1: 6.0.6278.0
To get better performance manually:
Update Statistics (will help speed up reports and takes less time than a full reindex):
EXEC sp_updatestats
Show index fragmentation (to determine how badly you need a reindex – logical scan frag > 10% = bad. Scan density below 80 = bad):
DBCC SHOWCONTIG
DBCC SHOWCONTIG WITH FAST (less data than above – in case you don’t have time)
Reindex the database:
USE OperationsManager
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"
Table by table:
DBCC DBREINDEX (‘TableName’)
Query to view the index job history on domain tables in the databases:
select *
from DomainTable dt
inner join DomainTableIndexOptimizationHistory dti
on dt.domaintablerowID = dti.domaintableindexrowID
ORDER BY optimizationdurationseconds DESC
Query to view the update statistics job history on domain tables in the databases:
select *
from DomainTable dt
inner join DomainTableStatisticsUpdateHistory dti
on dt.domaintablerowID = dti.domaintablerowID
ORDER BY UpdateDurationSeconds DESC
Data Warehouse query to examine the index and statistics history - run the following query for the Alert, Event, Perf, and State tables (these are non-domain tables):
select basetablename, optimizationstartdatetime, optimizationdurationseconds,
beforeavgfragmentationinpercent, afteravgfragmentationinpercent,
optimizationmethod, onlinerebuildlastperformeddatetime
from StandardDatasetOptimizationHistory sdoh
inner join StandardDatasetAggregationStorageIndex sdasi
on sdoh.StandardDatasetAggregationStorageIndexRowId = sdasi.StandardDatasetAggregationStorageIndexRowId
inner join StandardDatasetAggregationStorage sdas
on sdasi.StandardDatasetAggregationStorageRowId = sdas.StandardDatasetAggregationStorageRowId
ORDER BY optimizationdurationseconds DESC
I got a Microsoft TechNet flash email the other day with some cool cloud resources so thought I would note it here for easy reference purposes.
https://www.systemcenteradvisor.com/
http://www.microsoft.com/learning/en/us/certification/cert-private-cloud.aspx
http://technet.microsoft.com/en-gb/evalcenter/hh478202.aspx
http://technet.microsoft.com/en-us/evalcenter/hh505660.aspx?prod=SC&tech-CL&type=DL&prog=Eval
I was told about this product today and I thought that it was a wind up. What a name! Anyway decided to have a look and it looks pretty good. Its an alternative to the normal culprits if nothing else.
And you can find it here:
With little fanfare the SCCM 2012 Beta Courseware is available for MCT’s to download and deliver. For those not lucky enough to have access to the MCT site your friendly Microsoft Training centre (spelt correctly!) will be able to help.
Here at Global Knowledge I will be delivering the first run of course 10747 Administering SCCM 2012 stating on 5 March 2012
To see the course outlines visit
For 10747:
http://globalknowledge.co.uk/courses/microsoft/network_and_operation_management/m10747.html
For 10748
http://globalknowledge.co.uk/courses/microsoft/network_and_operation_management/m10748.html
Wally Mead has posted two links to new Configuration Manager 2012 TechNet Virtual Labs in the Configuration Manager 2012 beta forum. The two new labs are built with the Release Candidate of Configuration Manager 2012. So you can play with Configuration Manager 2012 Release Candidate without having to building your own lab environment.
Check out the labs via the links below and learn about Configuration Manager 2012 and how you are able to migrate to Configuration Manager 2012 without building a whole lab yourself.
After completing the Introduction lab, you will:
After completing the Migration lab, you will:
