This might NOT list all routers in your environment, and it will has lots of false positives too.

Of the 7 device marked as a router, only 3 are right, the remaining 4 are false positives.

In my test lab it found 3 out of 7, Routers /Switches.

Plus it lists

·        1 WS, that is not acting like routers. (I doubled checked)

·        1 Server was listed twice and it is not acting like a router. No unless you consider a WSUS server a router.

·        1 Network UPS???? And that is clearly not a router. And it did not detect the other network UPS.

Anyways have fun with it.

select SMS_R_System.* from  SMS_R_System where SMS_R_System.SystemRoles = "Router"

With the start of a new year, it is that time again to support the System Management Community, with that in mind I have made my yearly donation of $250 USD to MyITForum (MIF).

Why do I do it? Well as a past User Group leader for www.OWSUG.ca, I know how much it cost to host a small user group community. OWSUG is small in comparison to MIF! Our network traffic is nothing compared to what the MIF site gets. I’m will to bet that the network traffic to the MIF site in less than 12 hours, is the equivalent of what the www.OWSUG.ca gets in one year! I will also tell you that it cost more that $350 as year (USD) to host the www.OWSUG.ca site. Yes that is about $1 a day for a small user group site! I hate to see what MIF spends each month!

So, have you made a donation to your community yet? http://www.myitforum.com/donations/myITpaypal.asp

select
    R.* 
from 
    SMS_R_System R
where
    R.SystemGroupName != "GARTEK\\Domain Admins"
    and R.ResourceId not in (    select
                        R.ResourceId
                    from 
                        SMS_R_System R
                        inner join SMS_G_System_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceId = R.ResourceId
                    where
                        ARP.DisplayName = "CCM Framework Tools")

SELECT
    DisplayName0,
    Count(*) AS 'Count',
    Publisher0,
    @CollID as CollectionID

FROM
    v_Add_Remove_Programs arp
    JOIN v_FullCollectionMembership fcm on arp.ResourceID=fcm.ResourceID
WHERE
    fcm.CollectionID = @CollID
    AND (
        ARP.DisplayName0 not like '%hotfix%'
        AND ARP.DisplayName0 not like '%update%'
        AND ARP.DisplayName0 not like '%Screen Saver%'
        AND ARP.DisplayName0 not like '%silverlight%'
        AND ARP.DisplayName0 not like '%Windows Live%')
    AND (ARP.Publisher0 not like '%Intel%'
        AND ARP.Publisher0 not like '%Hewlet%'
        AND ARP.Publisher0 not like '%Apple%'
        AND ARP.Publisher0 not like '%ati%'
        AND ARP.Publisher0 not like '%eastman%'
        AND ARP.Publisher0 not like '%google%')
    OR ARP.Publisher0 is NULL
GROUP BY
    DisplayName0,
    Publisher0
ORDER BY
    Publisher0

Here is sample code on how to test for an exist code using SMS Installed.

item: Execute Program

  Pathname=%win%\notepad.exe

  Default Directory=%win%\

  Flags=00000010

end

item: If/While Statement

  Variable=PROCEXITCODE

  Value=0

end

item: Display Message

  Title English=Exist Code

  Text English=Notepad exited with a code of 0

  Flags=00100100

end

item: Else Statement

end

item: Display Message

  Title English=Exist Code

  Text English=Notepad exited with a code of 1

  Flags=00110000

end

item: End Block

end

I have to laugh at this item that is for sale on www.UsedOttawa.com !

Every so often I go looking to see what is for sale, anyways I keep running to the this AD where some guys is trying to sell “Brand new Microsoft Office System Beta 2 Kit 2003”! That right BETA 2! Now the first thing to note is that it is illegal to sell MS Beta software! The second and more importantly is, this software has built in expiry date, and I guarantee that date has long since come and gone!

Now it not like he is trying to sell it for $5, not he trying to get $100 for this! So what is he selling you?  A couple of DVDs with software that you can’t use! Hun?? Why? Who does he thing will buy this? My guess is a sucker, who want office on the Cheap! For $149 you can get “Microsoft Office Home & Student 2007”

So remember when buying anything from anyone the old say “Caveat emptor” or in English “Let the buyer beware!”, This is particularly true for stuff that you purchase from Internet website like UsedOttawa, Kijiji and Craigslist.

BTW, He renew the AD every month but when I see it, I report it to UsedOttawa team, however I’m not sure that they have ever done anything about the Ads or this guy! Here is the Ad if you want to laugh at it! http://www.usedottawa.com/classified-ad/8577745 Even funnier is it shows up when I search for “server”, What does Office 2003 have to do with servers?

Err, I’m not sure I like this diet or do I?

http://www.nytimes.com/2009/12/10/technology/10data.html?_r=2&ref=business

Take the following query, if you only used v_R_System.Operating_System_Name_and0 then you would have no idea as to which PCs are installed with “Microsoft Windows 7 Ultimate" or “Microsoft Windows 7 Professional”.

Select

      R.Name0,

      R.Operating_System_Name_and0,

      OS.Caption0

From

      dbo.v_R_System r

      left outer join dbo.v_GS_OPERATING_SYSTEM OS on R.ResourceID = OS.ResourceId

Where

      R.Active0 = 1

      and client0 = 1

      and R.Operating_System_Name_and0 = 'Microsoft Windows NT Workstation 6.1'

Order by

      Name0

Ultimately the biggest issue with reporting is understanding a few items about ConfigMgr:

1.      Where is the data coming from, (hardware inventory vs. discovery data)

2.      How often is the data updated (How often is hardware and discovery data run?)

3.      What do I want the data for and who is it for? (How many manager know what Microsoft Windows NT Workstation 6.1 is?)

4.      What does it mean when there is no data (Can I trust the data I’m seeing?)

5.      Are my queries consistent? (Is the differences in data due to inconsistency in my queries? Or by chance have PCs update their data in-between running each report?)

6.      Are the inconsistent material to my reporting? (Assume that I have 1000 PCs and I’m seeing 500 PC with different results (50%), do I care, YES. Now assume that I have 100 000 PCs and I’m seeing 500 PC with different results (0.5%), do I care, NO.)

There are no right or wrong answer here but as an ConfigMgr / SMS admin who should prepare yourself to answer these questions when they come up.

SELECT    
    R.Netbios_Name0,
    os.Caption0,
    ARP.DisplayName0 As 'Softwarename',
    ARP.Publisher0 as 'Publisher',
    ARP.Version0 as 'version',
    ARP.ProdID0 as 'productID',
    ARP.InstallDate0 'InstallDate',
    R.AD_Site_Name0 as 'ADSite',
    R.User_Name0 as 'Last Logged on User',
    R.Operating_System_Name_and0 as 'OS'
FROM        
    dbo.v_R_System R
    inner JOIN dbo.v_Add_Remove_Programs ARP ON R.ResourceID = ARP.ResourceID
    inner join dbo.v_GS_OPERATING_SYSTEM OS on R.ResourceID = OS.ResourceID
Where
    R.Operating_System_Name_and0 like '%NT%Server%'
Order by
    R.Netbios_Name0,
    ARP.DisplayName0

If you are looking to take this course, I have been told that CTE Solutions will be running 6451a course the week of Dec 14th - 18th 2009.

http://www.ctesolutions.com/course/Systems-Center-Training/Systems-Center-Configuration-Manager-2007-Training.htm?l=ottawa%2Dtraining%2Dfacility&checkout

Don’t forget to ask for your User Group discount! ;-) (They love it, when I remind people about that. )

BTW if you are looking for a good place for lunch near CTE Solutions, check out the Foolish Chicken

Are you looking to become certified on ConfigMgr 2007 (70-401) or OpsMgr 2007 (70-400)?

Well you are in Luck! Prometric is offering a 20% discount on both exam!

http://www.prometric.com/Microsoft/CareerOffer.htm#vouchers20

So what are you waiting for?

Yesterday, I receive the typical spam message and every so often I go to the website just checking out the phishing site and to see how bad their really are!

Yesterday I went off to the SPAMMER website and was surprised that I found that the site was not blocked but IE! It look like the normal bank site.

Anyways I was trying to remember how to report bogus websites so that they would get blocked by IE.  I could not find the option within IE to reports this site, So I sent a quick email off to Bruce Cowper at MS Canada, I don’t remember what Bruce’s official title is but it is something like “Canadian Security Officer” or something like that.

Bruce replied with the steps to report bogus site. ( I have expanded them and added the screenshots)

From within IE and while you are on the site.

Select Safety from the tool bar.

Point to SmartScreen Filter then  select Report Unsafe Website.

On the MS site, check the appropriate check box

Type the CAPTCHA word and click submit.

Anyways today I thought that I would blog about this, mostly as a reminder to everyone that if you see a website that is bogus, then report them. Today this is what I got when I tried to get to the bogus site! Thanks Bruce.

Like many of you I applied SP2 a while back and promptly added  a few classes back to the sms_def.mof, however I had enable the Cal tracking too and it is enabled within the configuration.mof.

Today I realized that it was disable (default setting) because both the smsdef.mof and configuration.mof are over written as part of applying the service pack. Therefore if you did have it enable and you applied the SP for ConfigMgr, remember to re-enable it.

select distinct
R.Name,
SCU.TopConsoleUser
from
SMS_R_System R
inner join SMS_G_System_SYSTEM_CONSOLE_USAGE SCU on SCU.ResourceID = R.ResourceId
where
SCU.TopConsoleUser in ( select
     U.UniqueUserName 
    from 
     SMS_R_User U 
    where
     U.UserGroupName = "GARTEK\\Test1")

Thanks Cliff.. ;-) this Twitter add-on works great! Something tells me that I will be twittering more!

select

      CS.name0 as 'PC name',

      WS.LastHWScan as 'Last HW scan Date'

from

      dbo.v_GS_COMPUTER_SYSTEM CS

      join dbo.v_GS_WORKSTATION_STATUS WS on CS.resourceID = WS.resourceID

Where

      CS.resourceID not in (select R.resourceID from v_R_System R)

I don’t know how I originally found this RSS feed but here is a cool video of Ottawa in 30000 frames.

http://waa.loudandskittish.com/2009/11/14/an-ottawa-summer-in-30000-frames/

select
    SMS_R_System.ResourceId,
    SMS_R_System.ResourceType,
    SMS_R_System.Name,
    SMS_R_System.SMSUniqueIdentifier,
    SMS_R_System.ResourceDomainORWorkgroup,
    SMS_R_System.Client,
    SMS_G_System_SYSTEM_CONSOLE_USER.LastConsoleUse,
    SMS_G_System_COMPUTER_SYSTEM.TimeStamp
from 
    SMS_R_System
    inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId
    inner join SMS_G_System_SYSTEM_CONSOLE_USER on SMS_G_System_SYSTEM_CONSOLE_USER.ResourceId = SMS_R_System.ResourceId
where
    SMS_G_System_COMPUTER_SYSTEM.UserName = SMS_G_System_SYSTEM_CONSOLE_USER.SystemConsoleUser

:-) at the suggestion of a friend here is a query to find all non-client PCs.

select

               SMS_R_SYSTEM.ResourceID,

               SMS_R_SYSTEM.ResourceType,

               SMS_R_SYSTEM.Name,

               SMS_R_SYSTEM.SMSUniqueIdentifier,

               SMS_R_SYSTEM.ResourceDomainORWorkgroup,

               SMS_R_SYSTEM.Client

from

               SMS_R_System

where

               SMS_R_SYSTEM.ResourceID not in (Select SMS_R_SYSTEM.ResourceID from SMS_R_System where SMS_R_System.Client = 1)

select
    SMS_R_SYSTEM.ResourceID,
    SMS_R_SYSTEM.ResourceType,
    SMS_R_SYSTEM.Name,
    SMS_R_SYSTEM.SMSUniqueIdentifier,
    SMS_R_SYSTEM.ResourceDomainORWorkgroup,
    SMS_R_SYSTEM.Client
from
    SMS_R_System
    inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId
where
    SMS_G_System_WORKSTATION_STATUS.LastHardwareScan < Datediff(dd,Getdate(),-30)

Quick follow up to How to Perform a Basic Software Audit

Do you want to know how many ARP entries you have?

How many page of paper you will get if you print all of the entries?

What about bundles of paper?

Use this query to help you determine this for you.

declare @ARP BIGINT
Set @ARP = (select count(*) from dbo.v_Add_Remove_Programs)

Select
'Number of ARP entries ' + convert(varchar(10), @arp),
'Number of pages '+ convert(varchar(10), sum(Round(@arp/80,0)+1)),
'Number of papper Bundles '+ convert(varchar(10), round(sum(@arp/40000.0),2))

Based on the Microsoft Skills Measured web page for 70-401, here is a list of items that can help you with your studying for the exam.

Deploying a System Center Configuration Manager 2007 (SCCM) Server (9 percent)

· Set up and configure an Active Directory schema.

· Migrate from an SMS 2003 hierarchy to SCCM 2007.

· Configure an SCCM hierarchy.

· Set up and configure security accounts.

TechNet Virtual Lab: Deploying System Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Upgrading SMS 2003 SP2 to System Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Upgrading from Microsoft Systems Management Server 2003 to System Center Configuration Manager 2007

TechNet Virtual Lab: Introduction to System Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Introduction to System Center Configuration Manager (SCCM) 2007

How to Extend the Active Directory Schema for SMS 2003

Screencast (Part 2 of 4): Master Your Environment with System Center Configuration Manager 2007

Configuring an SCCM Infrastructure (19 percent)

· Configure client agents.

· Configure site boundaries.

· Configure core site system roles.

· Configure discovery methods.

· Configure client installation.

· Configure SCCM infrastructure for Internet-based client management.

TechNet Virtual Lab: Deploying System Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Configuring Configuration Manager 2007 Security including PKI

Managing Resources (14 percent)

· Build an SCCM collection by using queries.

· Meter software usage.

· Manage assets.

· Manage inventory flow.

· Manage client agents.

TechNet Virtual Lab: Generating Asset Intelligence Data with Configuration Manager 2007

Configuration Manager 2007: Collections

How to create a Query and Modify (SMS 2003)

How to Create Collections based on Active Directory Groups (SMS 2003)

Distributing Applications (17 percent)

· Manage packages.

· Manage programs.

· Manage advertisements.

· Schedule distribution.

· Track success and failure rates for distribution.

· Manage distribution points.

TechNet Virtual Lab: Implementing Branch Distribution Points in System Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Implementing Maintenance Windows for Software Distribution in System Center Configuration Manager (SCCM) 2007

Step by Step: Creating Packages, Collections & Advertisements (SMS 2003)

Deploying Operating Systems (13 percent)

· Capture a reference computer image.

· Manage task sequences.

· Configure site system roles related to deploying operating systems.

· Deploy operating system packages.

· Customize user state migration.

· Deliver applications.

Zero Touch Vista deployments in Belfast

SCCM2007 and Microsoft Deployment Toolkit Setup and Config

TechNet Webcast: Automated Windows Server 2008 Imaging and Deployment Using the Microsoft Deployment Accelerator (Level 300)

Securing a Network Infrastructure (13 percent)

· Configure Network Access Protection (NAP).

· Maintain NAP.

· Migrate from Inventory Tool for Microsoft Update (ITMU) to Windows Software Update Services (WSUS).

· Deploy software updates.

· Manage vulnerability compliance.

TechNet Virtual Lab: Managing Custom Application Updates using Systems Center Configuration Manager (SCCM) 2007

TechNet Virtual Lab: Managing Microsoft Updates with System Center Configuration Manager (SCCM) 2007

Introduction to Network Access Protection” white paper Webcast updated

Network Access Protection Part 2

Network Access Protection with 802.1x (Part 1)

Managing and Maintaining an SCCM Infrastructure (15 percent)

· Manage system health.

· Configure automatic maintenance tasks.

· Create custom reports.

· Maintain clients.

· Manage systems by using configuration management.

· Manage Wake on LAN.

TechNet Virtual Lab: Determining Client Health in Configuration Manager 2007 R2

TechNet Labcast On-Demand: Security Compliance Management Toolkit – Configure the Desired Configuration Management (DCM) feature

TechNet Labcast On-Demand: Security Compliance Management Toolkit – Use the Built-in and Customized Reporting Capabilities of Configuration Manager

TechNet Virtual Lab: Implementing Desired Configuration Management in System Center Configuration Manager (SCCM) 2007

Other Labs

TechNet Virtual Lab: Integrating Application Virtualization into Configuration Manager 2007 R2

TechNet Virtual Lab: Integrating SQL Server Reporting Services into Configuration Manager 2007 R2

TechNet Virtual Lab: Upgrading System Center Configuration Manager 2007 to Service Pack 1

Over on the Enhansoft Blog, we posted a blog on “How to Perform a Basic Software Audit”. This should help answer the question of how to perform a basic software Audit and why “I need a report of all software for all PCs” is a bad idea.

select
FCM.CollectionId,
C.Name
from
dbo.v_R_System r
join dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID
join dbo.v_Collection C on C.CollectionID = FCM.CollectionID
Where
R.Name0 = 'ES-03'

Now this is cool! I would have love this back when I had roommates!

http://blog.coretech.dk/opsmgr07/opsmgr-2007-r2-scom-coretech-coffee-monitor-management-pack-0001/

What Bing Maps sees.

http://www.bing.com/maps/?mkt=en-ca#JndoZXJlMT0xODAraG9sYW5kK2F2ZSZiYj00NS4yNjk3ODk0Mzg2NDQlN2UtNzUuNzYwMjgxMzMwNDgzMSU3ZTQ1LjI2Nzg2NjIwNjYzMDklN2UtNzUuNzYzNjE5NzEzNzUwNA==

What Google Sees.

http://maps.google.ca/maps?f=q&source=s_q&hl=en&q=150+Holland+Ave,+Ottawa,+Ottawa+Division,+Ontario&sll=45.324163,-75.712321&sspn=0.008449,0.022252&ie=UTF8&cd=1&geocode=Fa-5tAIdBW98-w&split=0&hq=&hnear=150+Holland+Ave,+Ottawa,+Ottawa+Division,+Ontario&ll=45.400936,-75.732644&spn=0.008437,0.022252&z=16&layer=c&cbll=45.401019,-75.732707&panoid=ILd5WdrsllLJTXZC1MoSzA&cbp=12,88.05,,2,-16.6

So what is the Foolish Chicken, Well this is great chicken and ribs place with fantastic desserts! Ellen and I try to go ever few weeks! If you are in Ottawa check it out!