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

Garth Jones

February 2007 - Posts

  • Software Metering Console View - Part 2

    Today, we will take our base query and extend it just a bit by clean up by giving each column at title.

    There are 3 basis ways to do this.
    1) use a single quotes to encapsulate the title ‘’
    2) use square brackets []
    3) single word after the column

    To muddy the waters even farther you can use each of the above with or without the AS statement.

    I have highlighted each line of the query below to show each of the different ways to add a title to the query.

    Green is for a single word
    Yellow is for single quotes
    Blue is for square brackets

     MPR.ProductName AS Name,
     MPR.OriginalFileName AS 'Original File Name',
     MPR.FileName [File Name],
     MPR.FileVersion Version,
     MPR.LanguageID AS [LanguageID] ,
     MPR.SourceSite 'Site',
     MPR.ApplyToChildSites [Apply to Child Site],
     MPR.Enabled as 'Enabled',
     MPR.SecurityKey as 'Rule ID'
     v_MeteredProductRule MPR

    As you can see from the above query mixing and match style is difficult to follow. I prefer to always use the single quotes with the “AS” command.

     MPR.ProductName AS 'Name',
     MPR.OriginalFileName AS 'Original File Name',
     MPR.FileName AS 'File Name',
     MPR.FileVersion AS 'Version',
     MPR.LanguageID AS 'LanguageID',
     MPR.SourceSite AS 'Site',
     MPR.ApplyToChildSites AS 'Apply to Child Site',
     MPR.Enabled AS 'Enabled',
     MPR.SecurityKey AS 'Rule ID'
     v_MeteredProductRule MPR


  • Software Metering Console view.

    Over the next few days / weeks, I will show how to expand the basic query below to resemble this view within the SMS Console.














                v_MeteredProductRule MPR

    order by




  • Find all workstations that have not rebooting in a week - Part 2

    In yesterday’s Blog I showed how to find all workstations that have not rebooted in the last 7 days - a simple version. While writing this Blog I noticed that I have not shown how to expand simple queries to more complicated queries or to fix common issues with SMS and its data.


    Today I will expand this query to fix a common issue with yesterday’s Blog query. In order to truly understand why the query needs to be fixed you must understand how SMS works.


    By default whenever a PC is decommissioned or turned off its SMS data is kept within the SMS database until the ”Delete Aged Inventory History” task deletes its records, by default this is set to 90 days. The default hardware inventory is set to 7 days, although most organizations set it to 1 day. Now if you run the query from yesterday you will have to manually filter out all PCs which have been decommissioned for more than 7 days and less than 90 days. Now wouldn’t it be useful to have the query do that for you?


    Look at today’s query:



                CS.Name0 as 'PC Name',

                CS.UserName0 as 'User ID',

                OS.lastbootuptime0 as 'Boot Time'


                v_GS_COMPUTER_SYSTEM CS,

                v_GS_OPERATING_SYSTEM OS,

                v_GS_WORKSTATION_STATUS WS


                CS.ResourceID = OS.ResourceID

                and CS.ResourceID = WS.ResourceID

                and datediff(dd,lastbootuptime0, Getdate()) > 7

                and datediff(dd,WS.LastHWScan,getdate()) < 7


    I have added a new view “v_GS_WORKSTATION_STATUS” and done a date compare against the last hardware scan date.


  • Find all workstation that have not rebooting in a week.

    User this query to find all workstations that have not rebooted in a week, simple version.



                CS.Name0 as 'PC Name',

                CS.UserName0 as 'User ID',

                OS.lastbootuptime0 as 'Boot Time'


                v_GS_COMPUTER_SYSTEM CS,

                v_GS_OPERATING_SYSTEM OS


                CS.ResourceID = OS.ResourceID

                and datediff(dd,lastbootuptime0, Getdate()) > 7

  • Fun links

    The internet if full of stuff, most of it you will never see. Every so often I like to see what is out there and the best way I have found to find some new and interesting web site is to use a random site link.


    Random Site Links


    Here are a few links that I found with one of the link above.

    Posted Feb 24 2007, 02:03 PM by Garth with no comments
    Filed under: ,
  • Inventory Names

    Yesterday someone asked the following question within the SMS Mailing list. However I needed access to my SMS db before I could answer them, plus I though that this would be a good blog post for today.


    Does anyone know of the best way to export any customizations that you have added to the Software Inventory - Inventory Names section?  Transfer Site Settings wizard doesn’t seem to pick up that data.”


    Here is a quick SQL script to back and re-import all changes make to the Inventory names.



    'exec sp_InsertConversionRule "'+ ConversionRules.OriginalName +'", "'+ConversionRules.NewName+'", "'+ ConversionTypes.ConvertType+'"'





    ConversionTypes.ConvertTypeId = ConversionRules.ConvertTypeId


    BTW you will get errors if you try to re-import a duplicate names. Most of these will be from the MS Inventory Names

  • Airlift Day 2 aka final day

    Day two started off the same as day one, 2 hours of death by PowerPoint, just when you though that the day would be like that all Sajid started with the hands on labs.


    Today’s labs where:

    • Deploying Adobe Reader
    • Upgrading Adobe Reader (neat)
    • Troubleshooting tips


    To close out the day, Sajid and David ran a contest. We where given three packages to deploy and execute on a Windows XP desktop. Each package had at least one issue with it and you need to solve them. Since there is another airlift next week I will not spill the beans on how to solve the each of the issues but I will say.



    • There is more than one way to solve most of these problems
    • Listen very carefully when they talk about how to troubleshoot problems.


    What I though about the airlift.

    • Need better PCs
    • Although I like the way Sajid/David did the labs, it still would have been better to have lab manuals.
    • I REALLY  REALLY like the “Contest / testing your knowledge” and I hope they do it again for the next Canadian airlift!!! No I did not win maybe next time.
    • I think this was better than the SCOM airlift back in November
    • Also for the most part it was better than January’s SC airlift because of the hand on labs where that much better + my luggage don’t get lost this time. Smile [:)]
  • SoftGrid Airlift Day 1

    Nothing listed here falls under the NDA content presented at this airlift. I asked. ;-)


    1)      We need lab manuals!!!

    2)      Food is much better that at the MS Office!

    3)      AV needs work

    4)      Audio need a bit of work.


    Now for the SoftGrid stuff.

    I can see what this could be very useful for many clients particularly those that have lots of application compatibility issues and are hesitating to move to Vista. It could be useful for those with limited bandwidth and who also have SMS.


    The three hands on labs for today showed:

    • How to install SoftGrid
    • How to install / deploy and application with SoftGrid
    • How License Management works
  • webcasts

    We have enable a few of my past web cast on the site mostly on SMS 2.0. More will be added later so keep checking back.


    Windows 2003 R2 - What is New?



    Adding to SMS Machine Details Web Report



    Common Problems Encountered with SMS Web Reports



  • CPU Socket type

    The report will tell you what type of CPU socket types you have.



    socketdesignation0 as 'Socket type',

    count(socketdesignation0) as 'Count'



    group by


    order by


  • Another suggestion for MS - MS partners

    Here is another suggestion on how MS can help user groups. They can ask the local user group presidents to give a 5 minute speech at all MS partner events to introduce the User Groups to MS partners.


    This allows MS partners to find out about the local IT community and this allows the MS partners the opportunity to support the local IT community by:

    • providing a venue for UG events
    • provide presenters (ideally non-sales pitch type presentations, but that is up to each UG)
    • provide technical experts for panel discussions
    • provide technical advice within the forums/mailing list
    • spread the word about UG to their clients (for example add a blurb about the UG in their newsletter)
    • provide door prizes
    • sponsor the web site
    • sponsor an event
    • provide content to the UG web site in the form of items like:
      • news releases
      • articles
      • forum messages
      • blog post
      • free downloads, etc.
  • Keyboard Data aka SMS asset management

    SMS is a great tool for asset information, the key to SMS is under standing what information is available.


    Some people might ask why I should care about keyboard information!

    The answer is that at some time management will ask a question like:

    How may “TBITS-5 Bilingual Keyboard” do we have?


    It is always good to understand what data is within SMS, so that you can answer questions like this by saying “Give me a few minutes to write this query for you, would you like it as a web report or as a one off Excel spreadsheet?”



    v_GS_KEYBOARD_DEVICE.Description0 AS 'KB', Count(v_GS_KEYBOARD_DEVICE.Description0) AS 'Count'







  • Hard Drive Stats

    In yesterday blog I showed how you can sum the total hard drive space on each PC. Today blog goes along the same vain by  showing the minimum, maximum, average, and total of all hard drive within your SMS database.



                Min(v_GS_LOGICAL_DISK.Size0) AS 'Min of HD Size',

                Max(v_GS_LOGICAL_DISK.Size0) AS 'Max of HD Size',

                Avg(v_GS_LOGICAL_DISK.Size0) AS 'Avg of HD Size',

                Sum(v_GS_LOGICAL_DISK.Size0) AS 'Total of all HDs'



  • How much hard drive space and free space does each PC have?


     v_GS_COMPUTER_SYSTEM.Name0 AS 'PC Name',
     Sum(v_GS_LOGICAL_DISK.Size0) as 'HD Space',
     Sum(v_GS_LOGICAL_DISK.FreeSpace0) as 'HD Free Space',
     v_GS_COMPUTER_SYSTEM.UserName0 AS 'Last Logon User'
     v_GS_SYSTEM left join v_GS_LOGICAL_DISK on v_GS_SYSTEM.resourceid=v_GS_LOGICAL_DISK.resourceid
    Group By

  • Discovery Query

    This query will tell you, how many devices have been found by the various discovery methods in the pass 7 days.





                AgentName as 'Agent Name',

                count(AgentName) as 'count'




                Datediff(DD,agenttime,Getdate()) < 7

    group by


    order by


More Posts Next page »

This Blog




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