SMSUG.ca

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

Garth Jones

September 2011 - Posts

  • Serial Number, User Information and installation dates of the software

    SELECT

    adv

    .AdvertisementName,

    stat

    .LastStateName,

    adv

    .Comment AS C072,

    pkg

    .Name AS C062,

    adv

    .ProgramName AS C071,

    adv

    .SourceSite,

    adv

    .AdvertisementID,

    se

    .SerialNumber0,

    cs

    .UserName0,

    Stat

    .LastAcceptanceStatusTime FROM

    v_Advertisement adv

    JOIN v_Package pkg ON adv.PackageID = pkg.PackageID JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID JOIN v_R_System sys ON stat.ResourceID = sys.ResourceID join dbo.v_GS_SYSTEM_ENCLOSURE se on se.ResourceID = sys.ResourceID join dbo.v_GS_COMPUTER_SYSTEM cs on cs.ResourceID = sys.ResourceID

    WHERE

    sys.Netbios_Name0

    =@ComputerName
  • 10b–adding top console user

    if

    (@SoftwareID = '') set @SoftwareID = NULL; if (@Tag1Name = '') set @Tag1Name = NULL; if (@Tag2Name = '') set @Tag2Name = NULL; if (@Tag3Name = '') set @Tag3Name = NULL; select

    MEM

    .Netbios_Name0 [Computer Name],

    SCUM

    .TopConsoleUser0 as 'TCU',

    Soft

    .NormalizedName [Product Name],

    TG1

    .TagName [Label 1],

    TG2

    .TagName [Label 2],

    TG3

    .TagName [Label 3] from

    v_GS_INSTALLED_SOFTWARE_CATEGORIZED Soft

    inner join (select v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 from v_R_System_Valid join v_FullCollectionMembership ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID where v_FullCollectionMembership.CollectionID = @CollectionID )MEM ON MEM.ResourceID = Soft.ResourceID inner join v_LU_SoftwareList_Local ls ON ls.SoftwareID = Soft.SoftwareID left join v_LU_Tags TG1 ON TG1.TagID = ls.Tag1ID left join v_LU_Tags TG2 ON TG2.TagID = ls.Tag2ID left join v_LU_Tags TG3 ON TG3.TagID = ls.Tag3ID join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON SCUM.ResourceID = Soft.ResourceID where COALESCE (TG1.TagID, TG2.TagID, TG3.TagID) IS NOT NULL AND (@SoftwareID IS NULL OR Soft.SoftwareID = @SoftwareID) AND (@Tag1Name IS NULL OR TG1.TagName = @Tag1Name OR TG2.TagName = @Tag1Name OR TG3.TagName = @Tag1Name) AND (@Tag2Name IS NULL OR TG1.TagName = @Tag2Name OR TG2.TagName = @Tag2Name OR TG3.TagName = @Tag2Name) AND (@Tag3Name IS NULL OR TG1.TagName = @Tag3Name OR TG2.TagName = @Tag3Name OR TG3.TagName = @Tag3Name) order by

    MEM

    .Netbios_Name0,

    Soft

    .NormalizedName;
    Posted Sep 18 2011, 12:13 PM by Garth with no comments
    Filed under: , ,
  • Query to fetch the machines from a specific OU with the Specific Machine Model

    Select

    R

    .Name0

    From

    dbo

    .v_R_System R join dbo.v_GS_COMPUTER_SYSTEM CS on R.ResourceID = CS.ResourceID join dbo.v_RA_System_SystemOUName OU on R.ResourceID = OU.ResourceID

    Where

    CS

    .Model0 = 'Virtual Machine'

    Group

    by

    R

    .Name0,

    CS

    .Model0

    Having

    max(OU.System_OU_Name0) = 'GARTEK.TST/WORKSTATIONS'
  • MVA

    Have you seen Microsoft Virtual Academy (MVA): http://www.microsoftvirtualacademy.com/ ?

This Blog

Syndication

News

Training

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