tag:blogger.com,1999:blog-24871227543257122742024-03-13T11:38:07.672-07:00IT howto's n all round stuffGary Hayhttp://www.blogger.com/profile/05746107943777132062noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-2487122754325712274.post-84773822420002457312010-07-22T05:06:00.000-07:002010-07-22T05:06:31.805-07:00SCCM Dashboard Queries 4: Machine Types in a collectionSo sometimes your manager might ask you "how many laptops/desktops are there at site X?"<br />
<br />
You probably have a collection setup for site X but how do you answer your managers question quickly and easily? <br />
<br />
Better yet, let him answer that question on his own!<br />
<br />
This is a view I use to show how many Notebooks/Desktops there are in a collection based on the enclosure chassistypes attribute.<br />
<br />
Your mileage may vary but have a look and see what you think.<br />
<br />
<strong>View: Machine Types for Collection X</strong><br />
<strong>Query:</strong><br />
<br />
DECLARE @CollectionViewName As nvarchar(100)<br />
SET @CollectionViewName = (SELECT MemberClassName FROM v_Collection WHERE Name = @ViewName)<br />
EXEC ('SELECT CASE ChassisTypes0 WHEN ''8'' THEN ''Notebooks'' WHEN ''9'' THEN ''Notebooks'' WHEN ''10'' THEN ''Notebooks'' ELSE ''Desktops'' END AS "Workstation Type", count(distinct sys.name0) as ClientCount from <br />
v_GS_SYSTEM_ENCLOSURE ENC INNER JOIN (SELECT DISTINCT ResourceId FROM dbo.' + @CollectionViewName + ') v ON ENC.ResourceId = v.ResourceId INNER JOIN v_R_System SYS ON ENC.ResourceID = SYS.ResourceID WHERE enc.chassistypes0 <> ''12'' and sys.client0=1 AND sys.obsolete0=0 AND active0=1 GROUP BY CASE ChassisTypes0 WHEN ''8'' THEN ''Notebooks'' WHEN<br />
''9'' THEN ''Notebooks'' WHEN ''10'' THEN ''Notebooks'' ELSE ''Desktops'' END ORDER BY 2 desc') <br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOS0xay23gutLMeF3uK_E2ugu1amxJ6GgW2LBPXMu9Od3Q4EPtJX1pL-GlXnAO04M9a76oTk7VNlbnWvwjH3oDff3ZimWbZ5AJ_9pXP5o2jvkI-SKDvNcn7DV7hRrS_3tErU8NSpYGf2jq/s1600/machine_type.bmp" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="212" hw="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOS0xay23gutLMeF3uK_E2ugu1amxJ6GgW2LBPXMu9Od3Q4EPtJX1pL-GlXnAO04M9a76oTk7VNlbnWvwjH3oDff3ZimWbZ5AJ_9pXP5o2jvkI-SKDvNcn7DV7hRrS_3tErU8NSpYGf2jq/s400/machine_type.bmp" width="400" /></a></div>I filter out chassistype 12 as that is a 'docking station' so I dont want to see those. I use a pie chart to display the data but tweak it as you like.Gary Hayhttp://www.blogger.com/profile/05746107943777132062noreply@blogger.com3tag:blogger.com,1999:blog-2487122754325712274.post-72167792647807849592010-07-13T13:01:00.000-07:002010-07-14T09:33:13.635-07:00SCCM Dashboard Queries 3: AD ViewsOne of the more obscure uses for the SCCM dashboard is reporting on AD.<br />
<br />
SCCM does afterall poll AD to collect your user accounts, groups and machine accounts.<br />
<br />
With a bit of tweaking we can get AD reports from the SCCM dashboard quite easily.<br />
<br />
<br />
Lets say your a good admin and your AD is fully populated with accurate user data like country, office etc we can use this to create charts of your AD user breakdown.<br />
<br />
<br />
<br />
<br />
Lets look at my AD account, my country/region is specified as United Kingdom. This value is not collected by the SCCM User Discovery by default so lets go change it.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRmW9FYIX3QBi0QVTb12DRmfb4XO5kIVEREdf4X5ZfAU0xQLEILN_R2AywpMV1o9AkbrMR7FrLWNh2CYF7yGSgLmCzfjX5nB9bxqWF9q6ncHXLsX8d9udQ_YKZEz4gy8DwxDcGCY0XeSCR/s1600/ad+account.bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5493485185397803250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRmW9FYIX3QBi0QVTb12DRmfb4XO5kIVEREdf4X5ZfAU0xQLEILN_R2AywpMV1o9AkbrMR7FrLWNh2CYF7yGSgLmCzfjX5nB9bxqWF9q6ncHXLsX8d9udQ_YKZEz4gy8DwxDcGCY0XeSCR/s320/ad+account.bmp" style="cursor: hand; float: right; height: 320px; margin: 0px 0px 10px 10px; width: 270px;" /></a><br />
<br />
Go into your SCCM console>site management>siteID>site settings>discovery methods<br />
<br />
<br />
If you double-click on the Active Directory User Discovery the properties box appears.<br />
<br />
<br />
Next we want to click on the Active Directory Attribute Tab.<br />
<br />
<br />
Click on the New Button (little yellow Star icon)<br />
<br />
<br />
It will ask you for the Attibute name. AD exposes the Country/Region Value as 'C' (just the letter on its own) and click ok.<br />
<br />
<br />
You should see it appear in your list now.<br />
<br />
<br />
<br />
You can either now wait for your polling schedule to kick in or tick the box 'run discovery as soon as possible' to speed things up.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD9Cb6PtkZqlEY7W95HGXk_GCgYPhTAK9TrkfRRf3ZdKavdQhl-A7PoH6a1Yfv1BkxHHEKetChLTDNCWmpFaqu7MCiKRHwYEOuy5_09fsZ6312AONqmF6hpvjD4-RF73JlbzuSkkmjjJHf/s1600/attributes.bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5493487855789790242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD9Cb6PtkZqlEY7W95HGXk_GCgYPhTAK9TrkfRRf3ZdKavdQhl-A7PoH6a1Yfv1BkxHHEKetChLTDNCWmpFaqu7MCiKRHwYEOuy5_09fsZ6312AONqmF6hpvjD4-RF73JlbzuSkkmjjJHf/s320/attributes.bmp" style="cursor: hand; float: right; height: 320px; margin: 0px 0px 10px 10px; width: 294px;" /></a><br />
<br />
<br />
So now all our AD users have a country we can use that to build a SQL query for our dashboard.<br />
<br />
<br />
<br />
This is a simple query:<br />
<br />
<br />
SELECT<br />
<br />
dbo.v_R_User.c0 as 'Country', count(*) AS 'Users'<br />
<br />
FROM dbo.v_R_User<br />
<br />
where dbo.v_R_User.c0 not like 'NULL'<br />
<br />
group by dbo.v_R_User.c0<br />
order by 'Users' desc<br />
<br />
<br />
<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4_DPpXimnMvHyBxqmjYR5ulrFgnBoOs5QaHBKqotwF0ecZo6KEYVTIgCBnmCb08zt7T-7fHF2R2QXBrS2LdC7GfIIsqDzN-nMo8l-2RW4B4TsAP4D8PrZGVEWItjBqnnPBTauEEz8Mg8U/s1600/ad+user+breakdown.bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5493489119457034754" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4_DPpXimnMvHyBxqmjYR5ulrFgnBoOs5QaHBKqotwF0ecZo6KEYVTIgCBnmCb08zt7T-7fHF2R2QXBrS2LdC7GfIIsqDzN-nMo8l-2RW4B4TsAP4D8PrZGVEWItjBqnnPBTauEEz8Mg8U/s400/ad+user+breakdown.bmp" style="cursor: hand; float: right; height: 232px; margin: 0px 0px 10px 10px; width: 400px;" /></a><br />
The country data is returned as the country short code, I present this data as a chart with a data grid below it but you can do as you please with it. You could use a CASE statement to translate the country code into something more elegant but for my purposes it works fine as is.Gary Hayhttp://www.blogger.com/profile/05746107943777132062noreply@blogger.com0tag:blogger.com,1999:blog-2487122754325712274.post-24385600337685710382010-07-13T12:32:00.000-07:002010-07-14T09:32:58.562-07:00SCCM Dashboard Queries 2: Office AppsSo in part 1 I covered 3 basic computer summary views I use.<br />
<br />
As in many businesses, my work like to keep an eye on the install count for various Microsoft apps like Office/Visio/Project for license reconciliation.<br />
<br />
SCCM has some good reporting but sometimes its better to just give managers an up to date pretty chart or a nice table to look at, thus freeing up your own time to focus on important stuff.<br />
<br />
Some of the App views I use in my SCCM Dashbaord are below, these are for Office and Visio/Project versions but can be adapted to any product displayname like Adobe Reader, AutoCAD etc<br />
<br />
I normally use a datagrid with the product name and count from these:<br />
<br />
<strong>View 1: Office Suite Versions</strong><br />
select<br />
DisplayName0,<br />
Count (Distinct arp.ResourceID) as 'Client Count'<br />
From<br />
dbo.v_Add_Remove_Programs ARP<br />
Where<br />
DisplayName0 in ( 'Microsoft Office Professional Edition 2003','Microsoft Office Standard Edition 2003','Microsoft Office Enterprise 2007','Microsoft Office Standard 2007','Microsoft Office Professional Plus 2007','Microsoft Office 2000 SR-1 Professional','Microsoft Office 2000 SR-1 Standard','Microsoft Office Professional Plus 2010','Microsoft Office Standard 2010','Microsoft Office Enterprise 2007')<br />
<br />
Group by DisplayName0<br />
Order by Count(*) desc<br />
<br />
<br />
<strong>View 2: Visio/Project Version Count</strong><br />
<br />
select<br />
DisplayName0,<br />
Count (Distinct arp.ResourceID) as 'Client Count'<br />
From<br />
dbo.v_Add_Remove_Programs ARP<br />
Where<br />
DisplayName0 in ('Microsoft Visio Standard 2010','Microsoft Visio 2010 (Technical Preview)','Microsoft Office Visio Standard 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Professional 2007 Trial','Microsoft Office Visio Professional 2007','Microsoft Office Visio Professional 2003','Microsoft Office Visio 2010','Microsoft Office Project Standard 2007 Trial','Microsoft Office Project Standard 2007','Microsoft Office Visio Professional 2007','Microsoft Office Project Professional 2010','Microsoft Office Project Professional 2007 Trial','Microsoft Office Project Professional 2007','Microsoft Office Project Professional 2003','Microsoft Office Visio Professional 2010','Microsoft Office Visio Professional 2010 Trial','Microsoft Office Project Standard 2010')<br />
<br />
Group by DisplayName0<br />
Order by Count(*) descGary Hayhttp://www.blogger.com/profile/05746107943777132062noreply@blogger.com4tag:blogger.com,1999:blog-2487122754325712274.post-57932088434194990312010-07-13T11:13:00.000-07:002010-07-14T09:32:36.926-07:00SCCM Dashboard Queries 1<div>So I recently went through the process of setting up the SCCM Dashboard from the MS Solution Accelerator team. I wont bore you with the setup as its well documented elsewhere.<br />
<div><div><br />
By default the dashboard only has a few built in XML files for things like Update compliance, software deployment status etc<br />
<br />
There wasnt much documentation to acompany the dashboard until recently when I saw this blog from this guy <a href="http://blogs.msdn.com/b/shitanshu/archive/2010/04/08/part-1-how-microsoft-it-using-configuration-manager-dashboard.aspx">http://blogs.msdn.com/b/shitanshu/archive/2010/04/08/part-1-how-microsoft-it-using-configuration-manager-dashboard.aspx</a><br />
<br />
So after viewing that blog I thought to myself, what sort of views do I think would be good in my Dashboard.<br />
<br />
Below are some of the views I have setup for Computer summary data.<br />
<br />
I am by no means a SQL guy so if any of these queries are really inefficient then post a comment with your better suggestions.<br />
<br />
<strong>View 1: Top 10 Computer Models</strong><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVVLndoyEUQWhpk23uSbS0P6QDbdkLvy_0RBzzaqXeMkl2u1KaM1XHz-VLTjuvVsmYtfwEInsRENlh-V5VuUTACobmcUKkdBgLwvehdErPa27x8WbkKLNCCUjpDtFSjW7DGxU1wC5jRbLx/s1600/Top10Models.bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5493463086106264674" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVVLndoyEUQWhpk23uSbS0P6QDbdkLvy_0RBzzaqXeMkl2u1KaM1XHz-VLTjuvVsmYtfwEInsRENlh-V5VuUTACobmcUKkdBgLwvehdErPa27x8WbkKLNCCUjpDtFSjW7DGxU1wC5jRbLx/s320/Top10Models.bmp" style="cursor: hand; float: right; height: 120px; margin: 0px 0px 10px 10px; width: 320px;" /></a></div><br />
<br />
<div></div><br />
<br />
<div>select top 10 machine.model0 as 'Computer Model', count (*) as 'Client Count'<br />
<br />
from SCCM_Ext.vex_GS_COMPUTER_SYSTEM machine<br />
<br />
group by machine.model0<br />
order by count (*) desc</div><br />
<div><br />
<strong>View 2: Machine Count per SMS Site</strong></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigsVefeC9EDFoQE_XKNIUPR_kYk0agtO5x_6S2pibnhjGE0GSFXMwkEXIAZypOMzckkSxGQpsDVdVl9rLrknL1WUXvBMkHSHaaV4DUD25zDZ_U7i6bK-JeERp58rbcZ1Sa1d1VIO46Cdwv/s1600/machinecount.bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5493473096823108114" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigsVefeC9EDFoQE_XKNIUPR_kYk0agtO5x_6S2pibnhjGE0GSFXMwkEXIAZypOMzckkSxGQpsDVdVl9rLrknL1WUXvBMkHSHaaV4DUD25zDZ_U7i6bK-JeERp58rbcZ1Sa1d1VIO46Cdwv/s320/machinecount.bmp" style="cursor: hand; float: right; height: 235px; margin: 0px 0px 10px 10px; width: 320px;" /></a><br />
<br />
<div></div><br />
<br />
<div></div><br />
SELECT<br />
CASE sms_assigned_sites0<br />
WHEN 'AAA' THEN 'Site1'<br />
WHEN 'BBB' THEN 'Site2'<br />
END AS "Region and SiteCode", count(distinct name0) as ClientCount<br />
FROM v_RA_System_SMSAssignedSites sas join v_R_System sys on sas.resourceID=sys.resourceID<br />
WHERE client0=1 AND obsolete0=0 AND Active0=1<br />
GROUP BY CASE sms_assigned_sites0<br />
<br />
WHEN 'AAA' THEN 'Site1'<br />
WHEN 'BBB' THEN 'Site2'<br />
<br />
END<br />
ORDER by 'ClientCount' desc </div><br />
<div></div><br />
<div></div><br />
<div></div><br />
<div></div><br />
<div><strong>View 3: Computer Chasis type</strong> (We only manage desktops/notebooks)</div><br />
<div></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizTsFM66Tu6NJcrFhLPr8Y7uB2fYxrYYZrPceBgcrCVk0N4iM24OHpojBIIUzeOSylXfjaY4OAxJmcC-FwV4N5IB5pxZvOiuerTfEA5FwC5ZBho8nC63h3wqlzpwhbnsLCbXKEckkjYf1V/s1600/machinetype.bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5493474304047209106" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizTsFM66Tu6NJcrFhLPr8Y7uB2fYxrYYZrPceBgcrCVk0N4iM24OHpojBIIUzeOSylXfjaY4OAxJmcC-FwV4N5IB5pxZvOiuerTfEA5FwC5ZBho8nC63h3wqlzpwhbnsLCbXKEckkjYf1V/s320/machinetype.bmp" style="cursor: hand; float: right; height: 222px; margin: 0px 0px 10px 10px; width: 320px;" /></a><br />
<div></div><br />
SELECT<br />
CASE ChassisTypes0<br />
WHEN '10' THEN 'Notebooks'<br />
ELSE 'Desktops'<br />
END AS "Workstation Type", count(distinct sys.name0) as ClientCount from<br />
v_GS_SYSTEM_ENCLOSURE ENC<br />
INNER JOIN<br />
v_R_System SYS ON ENC.ResourceID = SYS.ResourceID<br />
WHERE<br />
sys.client0=1 AND sys.obsolete0=0 AND active0=1<br />
GROUP BY<br />
CASE ChassisTypes0<br />
WHEN '10' THEN 'Notebooks'<br />
ELSE 'Desktops'<br />
END<br />
ORDER BY 2 desc</div><div></div><div></div><div>Thats all for this post, next time I will cover software summary data for Microsoft Office Apps.</div>Gary Hayhttp://www.blogger.com/profile/05746107943777132062noreply@blogger.com1tag:blogger.com,1999:blog-2487122754325712274.post-54438826073710187852010-07-13T11:01:00.000-07:002010-07-13T11:10:29.501-07:00My first blog....So its my first blog post......<br /><br />Well what will be the pupose of this blog?<br /><br />In my time as an Microsoft IT Pro I have used other peoples blogs for IT howto's, hints & tips, and all round general IT stuff. Sometimes spending ages looking through Google results trying to help find that alusive fix for a problem.<br /><br />So I thought why not make my mark and contribute something to help others during their time of need.<br /><br />I intend to keep this blog up to date with my experiences of using and troubleshooting mostly Microsoft products like AD, Exchange, Powershell, SCCM, SCOM and others<br /><br />Hopefully the info you find helps you along your way.Gary Hayhttp://www.blogger.com/profile/05746107943777132062noreply@blogger.com1