SMSUG.ca

...your virtual communITy
Welcome to SMSUG.ca Sign in | Join | Help
in Search

Garth Jones

May 2007 - Posts

  • How Long?

    It took 2 hours, 4 minutes and 8 second to return 524031 rows from a db with ~3500PCs and isqlw.exe consumed just under 560MB of RAM. Don’t try to run the “All programs for each PC within a collection” as a web report, it will not work!

     

  • What is the average number or items within the Add/Remove Per PC?

    During a resent discussion within the mailing list this request was posted.

     

    “I need to run a report of all software installed for each computer in a specific collection (or an AD site..Whichever)…   Similar to the “Summary of Installed Software in a Specific Collection” report that comes with SP2, but rather than a summary, I want each computer name listed, with all installed software for each computer”

     

    The report in question would look something like this

    COMPUTER1       App1

    COMPUTER1       App2

    COMPUTER1       App3

    COMPUTER2       App1

    COMPUTER2       App2

    COMPUTER2       App3

     

    The problem I have with this is: Based on the average number or items with in my test database, each PC would have 186 rows! This makes this report unusable! However management and auditor continue to ask for this type of report without knowing what it really means.

     

    What they should be asking for is a high level count of all applications, then be able to drill down to a list of PCs with that application.

     

    So what is your average number of rows per PC?

     

    SELECT Count(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) AS 'Count0'

    into #avge

    FROM v_GS_ADD_REMOVE_PROGRAMS v_GS_ADD_REMOVE_PROGRAMS, v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM

    WHERE v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID

    GROUP BY v_GS_COMPUTER_SYSTEM.Name0

     

    select avg(count0) from #avge

    drop table #avge

     

     

    BTW if really want to kill a few trees here is the query that will produce the huge report requested above.

     

     SELECT v_GS_COMPUTER_SYSTEM.Name0,

                v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,

                v_GS_ADD_REMOVE_PROGRAMS.ProdID0,

                v_GS_ADD_REMOVE_PROGRAMS.Publisher0,

                v_GS_ADD_REMOVE_PROGRAMS.InstallDate0

    FROM

                v_CM_RES_COLL_SMS00001 v_CM_RES_COLL_SMS00001,

                v_GS_ADD_REMOVE_PROGRAMS v_GS_ADD_REMOVE_PROGRAMS,

                v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM

    WHERE

                v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID

                AND v_GS_COMPUTER_SYSTEM.ResourceID = v_CM_RES_COLL_SMS00001.ResourceID

    Order by

                v_GS_COMPUTER_SYSTEM.Name0,

                v_GS_ADD_REMOVE_PROGRAMS.ProdID0

  • Firewalls and SMS

    Recently there have been a number of questions about how to have SMS working with firewalls. These KBs and documents will help you set up your firewall correctly.


    Scenarios and Procedures for Microsoft Systems Management Server 2003: Security
    http://www.microsoft.com/downloads/details.aspx?FamilyID=3d81b520-a203-4376-a72d-fd34a6c4a44c&DisplayLang=en

     

    Service overview and network port requirements for the Windows Server system
    http://support.microsoft.com/kb/832017

     

    Ports that Systems Management Server 2003 uses to communicate through a firewall or through a proxy server
    http://support.microsoft.com/kb/826852

     

    How To Restrict TCP/IP Ports on Windows 2000 and Windows XP

    http://support.microsoft.com/kb/300083/

     

  • Announcing: MatchIT

    From an email to me:

    What is MatchIT?

    MatchIT is an online environment where charities and not-for-profit organizations can connect with Developers, IT Pro’s, Designers, Students or other technical individuals willing to volunteer their time and expertise to bring technology solutions to these organizations.  Our goal is to enable them to be more effective with their limited resources and focus their efforts on having a positive impact in society.

    What does MatchIT do?

    MatchIT will allow charities across the country to post virtual "want ads" describing opportunities to our audiences that have inputted their volunteer profile to assist these organizations.  Through a unique taxonomy process developed by our friends at Navantis, only true matches will be put forward to awaiting volunteers, so users will not have to wade through all the opportunities to find a good fit. 

    In addition to assisting in aligning these volunteer opportunities, Microsoft Canada Community Affairs will provide the applicable Microsoft software to the charities in order to ensure that the job is done right!

    How can I get involved?

    I already mentioned we need volunteers but also - help us spread the word!  The more volunteers with technical skills we have ready for opportunities, the better as the need for technical assistance in the charitable sector is astounding.  If you know a Developer or IT Pro looking for ways they can help Canadian communities, or if you are interested in applying your technical skills to incredible Canadian charities in a volunteer capacity, simply visit http://matchit.imaginecanada.ca/MSImagine/Home.aspx and "Sign Up"! 

    (note:  the matchit.ca domain is in the process of coming online, so for the short-term please direct to http://matchit.imaginecanada.ca/MSImagine - long term it will be matchit.ca )

     

  • Find PCs without .Net 2.0 or .Net 3.0

    select distinct SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select distinct SMS_G_System_COMPUTER_SYSTEM.ResourceID
        from  SMS_G_System_COMPUTER_SYSTEM inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_G_System_COMPUTER_SYSTEM.ResourceId
        where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%.NET Framework 2.0%") and SMS_R_System.ResourceId not in (select distinct SMS_G_System_COMPUTER_SYSTEM.ResourceID
        from  SMS_G_System_COMPUTER_SYSTEM inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_G_System_COMPUTER_SYSTEM.ResourceId
        where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%.NET Framework 3.0%") order by SMS_G_System_SYSTEM.Name
  • Find PCs without .Net 3.0

    select distinct SMS_G_System_SYSTEM.Name from  SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select distinct SMS_G_System_COMPUTER_SYSTEM.ResourceID
        from  SMS_G_System_COMPUTER_SYSTEM inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_G_System_COMPUTER_SYSTEM.ResourceId
        where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%.NET Framework 3.0%")order by SMS_G_System_SYSTEM.Name
  • Find PC with .Net 3.0

    select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%.NET Framework 3.0%" order by SMS_G_System_COMPUTER_SYSTEM.Name
  • Find PCs with .Net 2.0

    select distinct SMS_G_System_COMPUTER_SYSTEM.Name 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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%.NET Framework 2.0%" order by SMS_G_System_COMPUTER_SYSTEM.Name
  • Find all PCs without .Net 2.0

    select distinct
     SMS_G_System_SYSTEM.Name
    from 
     SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
    where
     SMS_R_System.ResourceId not in (select distinct
       SMS_G_System_COMPUTER_SYSTEM.ResourceID
         from 
       SMS_G_System_COMPUTER_SYSTEM inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_G_System_COMPUTER_SYSTEM.ResourceId
         where
       SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%.NET Framework 2.0%")
    order by
     SMS_G_System_SYSTEM.Name
  • Count of Collected Files

    This will give you a count of collected files.

    SELECT vCollectedFile.FileName, Count(vCollectedFile.FileName) AS 'Count'
    FROM vCollectedFile vCollectedFile
    GROUP BY vCollectedFile.FileName
    ORDER BY vCollectedFile.FileName

  • Free Courses from Microsoft

     

     

    Clinic 2263: Exam Preparation for the MCDST Certification

     

    Summary: This online clinic prepares students who are currently providing end-user support in a Microsoft Windows® XP operating environment with a review of the objectives covered on the Microsoft Certified Desktop Support Technician (MCDST) certification exams.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 1 year

     

     

    Clinic 2955: First Look: Microsoft® BizTalk® Server 2006 for IT Professionals

     

    Summary: This clinic provides IT professionals with an introduction to BizTalk Server 2006 and how to deploy and manage applications in a BizTalk environment.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 90 days

     

     

    Collection 5934: Introducing Microsoft Windows Server Code Name "Longhorn" (Beta 3)

     

    Summary: In this online collection composed of four clinics, you are introduced to the new features and functionality in Windows Server "Longhorn". This includes server virtualization, security and policy management, branch office management, centralized application access, and server management.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 90 days

     

    Clinic 2855: Managing Security Enhancements in Windows Server™ 2003 Service Pack 1

     

    Summary: This online clinic will show systems administrators and system engineers how to implement, deploy, support, and configure Windows Server 2003 SP1 security enhancements. It will provide in-depth information on the security changes in SP1 over previous versions.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 4360: Getting Started with Microsoft® Windows Server™ 2003 R2

     

    Summary: This clinic provides students you with an understanding of the features in Windows Server™ 2003 R2 including branch office considerations, Federated Identity Management, and storage management.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 90 days

     

     

    Clinic 2801: Microsoft® Security Guidance Training I

     

    Summary: This online clinic provides students with introductory knowledge and skills essential for the design and implementation of a secure computing environment. It also provides students with prescriptive guidance on security update management and best practices for implementing security on Microsoft Windows® server and client computers.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2802: Microsoft® Security Guidance Training II

     

    Summary: This online clinic builds on existing knowledge of server and client security and provides students with the knowledge and skills to apply best practices to implement perimeter and network defenses and enhance security for applications and Microsoft® Windows Server System™ components. It also provides students with prescriptive guidance to enhance security for Microsoft Windows® server and client computers and practical strategies for implementing security for wireless networks.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2803: Microsoft® Security Guidance Training III

     

    Summary: This online clinic builds on existing knowledge of server and client security and provides students with the knowledge and skills to apply security best practices for Microsoft® Exchange Server and to use Microsoft Internet Security and Acceleration (ISA) Server 2004 to improve security at the network perimeter. It also provides students with prescriptive guidance to implement layered defenses against malicious software and measures to increase security for Microsoft Windows NT® 4.0 and Windows® 98 network clients.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2804: Microsoft® Security Guidance Training IV

     

    Summary: This online clinic provides students with prescriptive guidance that builds on their knowledge of security essentials to help them improve security for their infrastructures.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2806: Microsoft® Security Guidance Training for Developers

     

    Summary: This online clinic provides students with knowledge and skills essential for the creation of applications with enhanced security. Students will learn about the need for implementing security at every stage of the development process and best practices for applying security principles. Students will also learn how to use established threat modeling methodologies and tools with other best practices to minimize vulnerabilities and limit damage from attacks. Finally, students will learn how to implement security features to enhance security for Web applications and Web services that are built by using Microsoft ASP.NET.
    Audience: Developer
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2807: Microsoft® Security Guidance Training for Developers II

     

    Summary: This clinic discusses best practices for building security into the design, development, testing, and deployment phases of the software development life cycle. It is targeted towards individual with at least one to two years of programming experience. The course will provide developers with the skills and knowledge to design and develop more secure Microsoft solutions.
    Audience: Developer
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2808: Microsoft® Security Guidance Training V

     

    Summary: This clinic builds on existing knowledge of server and client security and provides students with the knowledge and skills to apply best practices to securing Microsoft® Exchange Server clients, protecting e-mail and data content, securing services and critical accounts, and securing administrative accounts and remote access using smart cards.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 2855: Managing Security Enhancements in Windows Server™ 2003 Service Pack 1

     

    Summary: This online clinic will show systems administrators and system engineers how to implement, deploy, support, and configure Windows Server 2003 SP1 security enhancements. It will provide in-depth information on the security changes in SP1 over previous versions.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 180 days

     

    Clinic 6078: Deploying Microsoft® Forefront™ Client Security

     

    Summary: This online clinic will provide the knowledge necessary to understand the key features of Microsoft Forefront Client Security, as well as the considerations for deploying Forefront Client Security in a network environment.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 90 days

     

    Clinic 6079: Managing and Troubleshooting Operations in Microsoft® Forefront™ Client Security

     

    Summary: This online clinic will provide the knowledge necessary to manage Microsoft Forefront Client Security in a networked environment, as well as how to troubleshoot issues that may occur in a Forefront Client Security deployment.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 90 days

     

    Clinic 6080: Deploying Microsoft® Forefront™ Server Security Products

     

    Summary: This online clinic will provide the knowledge necessary to understand the key features of Microsoft Forefront Server Security for Exchange, and Microsoft Forefront Server Security for SharePoint.
    Audience: IT Pro
    Available Offline: Yes
    Price: FREE
    Subscription: 90 days

     

     

  • Advanced Client Cache

    Thanks goes out to Dave Berry, who sent in this query.

     

     

    SELECT

    v_GS_COMPUTER_SYSTEM.Name0 as 'PC Name',

    v_GS_SMS_ADVANCED_CLIENT_CACH.Location0,

    v_GS_SMS_ADVANCED_CLIENT_CACH.Size0

    FROM

    v_GS_SMS_ADVANCED_CLIENT_CACH, v_GS_COMPUTER_SYSTEM

    Where

    v_GS_SMS_ADVANCED_CLIENT_CACH.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

    ORDER BY

    v_GS_COMPUTER_SYSTEM.Name0

  • Cancelled --- Microsoft System Centre Essentials 2007 User Group Tour

    The Ottawa User Group Meeting of “Microsoft System Centre Essentials 2007 User Group Tour” had to be CANCELLED and will be re-scheduled.

  • NHL has signed an agreement to use System Center.

    What more do I need to say?

     

  • Ottawa is going to the Cup!!!!

More Posts Next page »

This Blog

Syndication

News

Training

Powered by Community Server (Non-Commercial Edition), by Telligent Systems