Creating a dynamic Group based on software installed and version of software

[TxBrewer]

I am looking to create a dynamic group for anyone who has a software installed and is under the version we want all users to have.     Use Google Chrome as an example, all computers with Google Chrome installed that are under version X.

I can get the first part but can't find the dynamic group setting to limit to out of date versions

[Jacob_H]

Hey TX -  where there's a will, there's a way - you can use this query to report on anything that conforms to the standard versioning scheme, or modify it to use just three decimals as applicable.  Anything that doesn't conform will be garbage:

Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.softwareVersion As Version,
  b.LatestVersion As [Highest Version Detected],
  tblSoftware.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.SoftID,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.softwareVersion
    From (Select *,
          r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
          _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblSoftware.SoftID,
              Major = Convert(int,ParseName(tblSoftware.softwareVersion, 4)),
              Minor = Convert(int,ParseName(tblSoftware.softwareVersion, 3)),
              Build = Convert(int,ParseName(tblSoftware.softwareVersion, 2)),
              Revision = Convert(int,ParseName(tblSoftware.softwareVersion, 1)),
              tblSoftware.softwareVersion
            From tblSoftware
              Inner Join tblSoftwareUni On tblSoftware.SoftID =
                  tblSoftwareUni.SoftID
            Where tblSoftwareUni.softwareName Like ('%Google Chrome%')) _) __
    Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
      tblSoftware.softwareVersion <> b.LatestVersion
Where tblSoftwareUni.softwareName Like ('%Google Chrome%') And
  tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblMacApplications.Version As Version,
  b.LatestVersion As [Highest Version Detected],
  tblMacApplications.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblMacApplications On
      tblAssets.AssetID = tblMacApplications.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softid
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.SoftID,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.Version
    From (Select *,
          r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
          _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblMacApplications.SoftID,
              Major = Convert(int,ParseName(tblMacApplications.Version, 4)),
              Minor = Convert(int,ParseName(tblMacApplications.Version, 3)),
              Build = Convert(int,ParseName(tblMacApplications.Version, 2)),
              Revision = Convert(int,ParseName(tblMacApplications.Version, 1)),
              tblMacApplications.Version
            From tblMacApplications
              Inner Join tblSoftwareUni On tblMacApplications.SoftID =
                  tblSoftwareUni.SoftID
            Where tblSoftwareUni.softwareName Like ('%Google Chrome%')) _) __
    Where __.r = 1) b On tblMacApplications.SoftID = b.SoftID And
      tblMacApplications.Version <> b.LatestVersion
Where tblSoftwareUni.softwareName Like ('%Google Chrome%') And
  tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblLinuxSoftware.Version As Version,
  b.LatestVersion As [Highest Version Detected],
  tblLinuxSoftware.Lastchanged,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
  Inner Join tblSoftwareUni On
      tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join (Select __.SoftwareUniID,
      __.Major,
      __.Minor,
      __.Build,
      __.Revision,
      LatestVersion = __.Version
    From (Select *,
          r = Row_Number() Over (Partition By _.SoftwareUniID Order By
          _.Major Desc, _.Minor Desc, _.Build Desc, _.Revision Desc)
        From (Select Distinct tblLinuxSoftware.SoftwareUniID,
              Major = Convert(int,ParseName(Case
                When tblLinuxSoftware.Version Like '%-1%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('-',
                  tblLinuxSoftware.Version) - 1)
                When tblLinuxSoftware.Version Like '%R%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('R',
                  tblLinuxSoftware.Version) - 1)
                Else tblLinuxSoftware.Version
              End, 4)),
              Minor = Convert(int,ParseName(Case
                When tblLinuxSoftware.Version Like '%-1%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('-',
                  tblLinuxSoftware.Version) - 1)
                When tblLinuxSoftware.Version Like '%R%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('R',
                  tblLinuxSoftware.Version) - 1)
                Else tblLinuxSoftware.Version
              End, 3)),
              Build = Convert(int,ParseName(Case
                When tblLinuxSoftware.Version Like '%-1%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('-',
                  tblLinuxSoftware.Version) - 1)
                When tblLinuxSoftware.Version Like '%R%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('R',
                  tblLinuxSoftware.Version) - 1)
                Else tblLinuxSoftware.Version
              End, 2)),
              Revision = Convert(int,ParseName(Case
                When tblLinuxSoftware.Version Like '%-1%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('-',
                  tblLinuxSoftware.Version) - 1)
                When tblLinuxSoftware.Version Like '%R%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('R',
                  tblLinuxSoftware.Version) - 1)
                Else tblLinuxSoftware.Version
              End, 1)),
              Case
                When tblLinuxSoftware.Version Like '%-1%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('-',
                  tblLinuxSoftware.Version) - 1)
                When tblLinuxSoftware.Version Like '%R%' Then
                  Left(tblLinuxSoftware.Version, CharIndex('R',
                  tblLinuxSoftware.Version) - 1)
                Else tblLinuxSoftware.Version
              End As Version
            From tblLinuxSoftware
              Inner Join tblSoftwareUni On tblLinuxSoftware.SoftwareUniID =
                  tblSoftwareUni.SoftID
            Where tblSoftwareUni.softwareName = ('google-chrome-stable')) _) __
    Where __.r = 1) b On tblLinuxSoftware.SoftwareUniID = b.SoftwareUniID And
      Case
        When tblLinuxSoftware.Version Like '%-1%' Then
          Left(tblLinuxSoftware.Version, CharIndex('-',
          tblLinuxSoftware.Version) - 1)
        When tblLinuxSoftware.Version Like '%R%' Then
          Left(tblLinuxSoftware.Version, CharIndex('R',
          tblLinuxSoftware.Version) - 1)
        Else tblLinuxSoftware.Version
      End <> b.LatestVersion
Where tblSoftwareUni.softwareName = ('google-chrome-stable') And
  tblState.Statename = 'Active'

[DavidPK]

Hi there,

The feature you're looking for is currently unavailable in Lansweeper. However, we would be happy to assist you in submitting a feature request to our product team. To do so, we kindly ask that you contact our tech support team: https://www.lansweeper.com/contact-support/