SMSUG.ca

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

Garth Jones

October 2011 - Posts

  • Simple History query

    Select distinct
        SD.Name0 'Machine Name', 
        HARP.DisplayName0 as '32bit',
        HARP.Version0,  
        HARP64.DisplayName0 as '64bit',
        HARP64.Version0,
        SD.User_Name0 'User Name'  
    From
        v_R_System SD 
        Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID 
        Join v_Collection COL on FCM.CollectionID = COL.CollectionID 
        left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS HARP on SD.ResourceID = HARP.ResourceID
        left outer join dbo.v_HS_ADD_REMOVE_PROGRAMS_64 HARP64 on SD.ResourceID = HARP64.ResourceID
    Where
         COL.Name = 'All Systems' 
        and (HARP.DisplayName0 = 'Configuration Manager Client' or HARP64.DisplayName0 = 'Configuration Manager Client')
    Order By 
        SD.User_Name0
    Posted Oct 22 2011, 08:49 AM by Garth with no comments
    Filed under: , , , ,
  • Two Software Titles

    SELECT  DISTINCT
        a.Netbios_Name0 AS 'Computer Name',
        CASE WHEN  ARP.OMSA IS NULL THEN 'N/A' ELSE ARP.OMSA END AS 'Dell OMSA Version',
        CASE WHEN  ARP.Equallogic IS NULL THEN 'N/A' ELSE ARP.Equallogic END AS 'Broadcom Version'
     FROM 
        v_R_System_Valid a
        INNER JOIN v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = a.ResourceID
        INNER JOIN v_FullCollectionMembership FCM on FCM.ResourceID = a.ResourceID
      
    --INSTALLED SOFTWARE--
        LEFT OUTER JOIN (SELECT ResourceID, 
                    MAX(CASE WHEN DisplayName0 ='Dell OpenManage Server Administrator' THEN Version0 ELSE 'Not Installed' END) AS 'OMSA',
                    MAX(CASE WHEN DisplayName0 ='Dell Equallogic Host Integration Tools' THEN Version0 ELSE 'Not Installed' END) AS 'Equallogic'
                FROM 
                    v_ADD_REMOVE_PROGRAMS 
                WHERE 
                        ProdID0 LIKE 'Microsoft SQL Server%' 
                        OR DisplayName0 = 'Dell OpenManage Server Administrator'
                        OR DisplayName0 = 'Dell Equallogic Host Integration Tools'
                GROUP BY 
                    ResourceID) ARP ON ARP.ResourceID = a.ResourceID
     
     Where 
        FCM.CollectionID = 'SMS00001' 
     Order by 
        a.Netbios_Name0
  • This is a test of the a code plug-in for live writer.

    SELECT
        A.AdvertisementName, 
        A.ProgramName,
        R.Netbios_Name0 as Computername,
        SCUM.TopConsoleUser0 as MainUsername 
    FROM 
        dbo.v_R_System_Valid R
        join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on R.ResourceID = SCUM.ResourceID
        join dbo.v_ClientAdvertisementStatus CAS on R.ResourceID = CAS.ResourceID
        join dbo.v_Advertisement A on A.AdvertisementID = CAS.AdvertisementID
    .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }
  • Advertisements, Programs and Main User of all PC

    SELECT

    A

    .AdvertisementName,

    A

    .ProgramName,

    R

    .Netbios_Name0 as Computername,

    SCUM

    .TopConsoleUser0 as MainUsername FROM

    dbo

    .v_R_System_Valid R join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on R.ResourceID = SCUM.ResourceID join dbo.v_ClientAdvertisementStatus CAS on R.ResourceID = CAS.ResourceID join dbo.v_Advertisement A on A.AdvertisementID = CAS.AdvertisementID

This Blog

Syndication

News

Training

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