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=Exit Code

  Text English=Notepad exited with a code of 0

  Flags=00100100

end

item: Else Statement

end

item: Display Message

  Title English=Exit 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?