
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-24-2025 04:19 PM
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
- Labels:
-
Asset Management
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-28-2025 12:05 AM
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'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-27-2025 08:32 PM
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/
