SMSUG.ca

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

Garth Jones

February 2008 - Posts

  • Newsgroup reply...

    Select distinct
     R.Netbios_Name0 AS "Computer Name",
     ISC.NormalizedName AS "Product Name",
     "Publisher" = CASE when (CAST(ISC.NormalizedPublisher as varchar) is NULL or CAST(ISC.NormalizedPublisher as varchar) = '-1') then 'Unknown' Else CAST(ISC.NormalizedPublisher as varchar) End,
     "Version" = CASE when (CAST(ISC.NormalizedVersion as varchar) is NULL or CAST(ISC.NormalizedVersion as varchar) = '-1') then 'Unknown' Else CAST(ISC.NormalizedVersion as varchar)End,
     "Language" = CASE when (CAST(ISC.Language0 as varchar) is NULL or CAST(ISC.Language0 as varchar) = '-1') then 'Unknown' Else CAST(ISC.Language0 as varchar) End,
     ISC.FamilyName AS "Product Family",
     ISC.CategoryName AS "Product Category",
     ISC.ProductID0 AS "ProductID",
     ISC.SoftwareID as "Software ID",
     ISC.CM_DSLID0 AS "DSL ID",
     "Installed Location" = CASE when (CAST(ISC.InstalledLocation0 as varchar) is NULL or CAST(ISC.InstalledLocation0 as varchar) = '-1') then 'Unknown' Else CAST(ISC.InstalledLocation0 as varchar) End,
     "Install Source" = CASE when (CAST(ISC.InstallSource0 as varchar) is NULL or CAST(ISC.InstallSource0 as varchar) = '-1') then 'Unknown' Else CAST(ISC.InstallSource0 as varchar) End,
     "Uninstall String" = CASE when (CAST(ISC.UninstallString0 as varchar) is NULL or CAST(ISC.UninstallString0 as varchar) = '-1') then 'Unknown' Else CAST(ISC.UninstallString0 as varchar) End,
     "Install Date" = CASE when (CAST(ISC.InstallDate0 as varchar) is NULL or CAST(ISC.InstallDate0 as varchar) = '-1') then 'Unknown' Else CAST(ISC.InstallDate0 as varchar) End,
     "Registered User" = CASE when (CAST(ISC.RegisteredUser0 as varchar) is NULL or CAST(ISC.RegisteredUser0 as varchar) = '-1') then 'Unknown' Else CAST(ISC.RegisteredUser0 as varchar) End
    FROM
     dbo.v_GS_INSTALLED_SOFTWARE_CATEGORIZED ISC INNER JOIN dbo.v_R_System R on R.ResourceID = ISC.ResourceID,
     dbo.v_FullCollectionMembership FCM
    Where
    R.ResourceID = FCM.ResourceID
    and FCM.CollectionID = 'SMS000DS'
    order by
     ISC.NormalizedName,
     Publisher,
     Version

  • 70-293 Self - Study group

    I’m starting up a virtual self study group for 70-293 with two others.

    This is roughly how we proceeding.

    • We are using the MS Press book “Windows Server 2003” Network Infrastructure” ISBN # 0-7356-1893-3
    • Each member writes a summary of a assigned section
    • All summaries will be combine into one big study guide
    • Follow weekly schedule. See forum message
    • Post any questions to the 70-293 forum on OWSUG
    • Hoping to setup a live meeting final setup session.

    If you would like join us or just follow along, let me know.

  • Adding OU to a report for MS Office installations

    SELECT DISTINCT
     dbo.v_RA_System_IPSubnets.IP_Subnets0,
     dbo.v_RA_System_SystemOUName.System_OU_Name0,
     dbo.v_R_System.Netbios_Name0,
     dbo.v_R_System.User_Name0,
     dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
    FROM
     dbo.v_GS_ADD_REMOVE_PROGRAMS LEFT OUTER JOIN dbo.v_CM_RES_COLL_SMS00004 ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_CM_RES_COLL_SMS00004.ResourceID
     INNER JOIN dbo.v_R_System ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_R_System.ResourceID
     INNER JOIN dbo.v_RA_System_IPSubnets ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_RA_System_IPSubnets.ResourceID
     INNER JOIN dbo.v_GS_SoftwareFile ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_GS_SoftwareFile.ResourceID
     left JOIN dbo.v_RA_System_SystemOUName ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID
    WHERE
     dbo.v_RA_System_IPSubnets.IP_Subnets0 NOT LIKE '%.0.0'
     AND ((dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%microsoft%office%pro%'
     OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%microsoft%office%stan%')
     AND NOT (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%visio%'
     OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Proof%'
     OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE '%Project%'))
    ORDER BY
     dbo.v_RA_System_IPSubnets.IP_Subnets0,
     dbo.v_R_System.Netbios_Name0

  • Location of last logon user

    This query will list most of the place where user name can be determined. Also sometimes I have seen people confuse RegisteredUser0 as the last logon user, this is not the case. This is the Registration name of used for Windows OS.

    SELECT
     v_GS_COMPUTER_SYSTEM.Name0,
     v_GS_COMPUTER_SYSTEM.UserName0,
     v_GS_SYSTEM_CONSOLE_USER.SystemConsoleUser0,
     v_GS_SYSTEM_CONSOLE_USAGE.TopConsoleUser0,
     v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0,
     v_R_System.User_Name0,
     v_R_System_Valid.User_Name0,
     v_GS_OPERATING_SYSTEM.RegisteredUser0
    FROM
     dbo.v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM,
     dbo.v_GS_OPERATING_SYSTEM v_GS_OPERATING_SYSTEM,
     dbo.v_GS_SYSTEM_CONSOLE_USAGE v_GS_SYSTEM_CONSOLE_USAGE,
     dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP,
     dbo.v_GS_SYSTEM_CONSOLE_USER v_GS_SYSTEM_CONSOLE_USER,
     dbo.v_R_System v_R_System,
     dbo.v_R_System_Valid v_R_System_Valid
    WHERE
     v_GS_OPERATING_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
     AND v_GS_SYSTEM_CONSOLE_USER.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
     AND v_GS_SYSTEM_CONSOLE_USAGE.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
     AND v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
     AND v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
     AND v_R_System_Valid.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID

  • How To Behave On An Internet Forum

    This is so true!! Everyone should watch this video..

    http://www.videojug.com/film/how-to-behave-on-an-internet-forum

     

  • 70-622 Chapter 2 PPT

    Here is the chapter 2 summary review.

    Posted Feb 19 2008, 09:54 AM by Garth with no comments
    Filed under:
  • Computers in a certain collection and their IP address

    SELECT DISTINCT
     CS.Name0,
     NAC.IPAddress0
    FROM
     dbo.v_FullCollectionMembership FCM,
     dbo.v_GS_COMPUTER_SYSTEM CS,
     dbo.v_GS_NETWORK_ADAPTER_CONFIGUR  NAC
    WHERE
     CS.ResourceID = FCM.ResourceID
     AND CS.ResourceID = NAC.ResourceID
     AND NAC.IPAddress0 Is Not Null
     AND FCM.CollectionID='SMS00004'
    ORDER BY
     CS.Name0

  • Ebook - TCP/IP Fundamentals for Microsoft Windows

    Brief Description
    This online book describes the fundamentals of TCP/IP in Windows Vista, Windows Server 2008, Windows XP, and Windows Server 2003.
     
     
  • Basic All in one.

    SELECT  distinct
     CS.name0 as 'Computer Name',
     CS.domain0 as 'Domain',
     CS.UserName0 as 'User',
     BIOS.SerialNumber0 as 'Bios serial',
     SE.SerialNumber0 as 'System Enclosure serial',
     CS.Manufacturer0 as 'Manufacturer',
     CS.Model0 as 'model',
     OS.Caption0 as 'OS',
     RAA.SMS_Assigned_Sites0 as 'Site',
     RAM.TotalPhysicalMemory0 as 'Total Memory',
     sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
     sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
     CPU.CurrentClockSpeed0 as 'CPU Speed'
    from 
      v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
     right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID 
     right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID 
     right join v_RA_System_SMSAssignedSites RAA on RAA.ResourceID = CS.ResourceID
     right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
     right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
     right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID   
     right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
    where
     LDisk.DriveType0 =3
    group by
     CS.Name0,
     CS.domain0,
     CS.Username0,
     BIOS.SerialNumber0,
     SE.SerialNumber0,
     CS.Manufacturer0,
     CS.Model0,
     OS.Caption0,
     RAA.SMS_Assigned_Sites0,
     RAM.TotalPhysicalMemory0,
     CPU.CurrentClockSpeed0

  • PC's heartbeat discovery data.

    select
     CS.Name0,
     CS.Username0,
     AGD.Agenttime
    from
     dbo.v_AgentDiscoveries AGD,
     dbo.v_GS_COMPUTER_SYSTEM CS
    Where
     CS.ResourceID = AGD.ResourceID
     and AGD.AgentName = 'Heartbeat Discovery'
    order by
     CS.Name0

    Posted Feb 02 2008, 09:05 PM by Garth with 1 comment(s)
    Filed under: , , ,

This Blog

Syndication

News

Training

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