cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
suspect0
Engaged Sweeper
It took me a while to get this working, mainly due to my lack of knowledge in doing these queries. This will grab the version number of the Office 365 installation and give the total amount of clients with that version. In turn it can be used for chart widgets.

Select Top 1000000 tblSoftware.softwareVersion,
Count(tblAssets.AssetID) As Total,
tblSoftware.softwareVersion As Version
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft office%365%' And
tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tblSoftware.softwareVersion
Order By Version,
Total Desc
4 REPLIES 4
Sebastien_Petel
Engaged Sweeper II
Here's my tweaked version

Select Top 1000000 Count(tblAssets.AssetID) As Total,
tblSoftware.softwareVersion As Version
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft %365%' And
tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tblSoftware.softwareVersion
Order By Version,
Total Desc
studerje
Engaged Sweeper II
This works great.
It would be really nice if you could click the version and have it take you to a list of computers running that version.
Bruce_B
Lansweeper Alumni
Slightly modified the report as you were selecting softwareversion twice

Select Top 1000000 tblSoftware.softwareVersion As Version,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%microsoft office%365%' And
tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
Order By Version,
Total Desc
I also slightly tweaked the report to accommodate the app's name change to Microsoft 365 Apps. I think the syntax could be tweaked for efficiency, but this works.

Select Top 1000000 tblSoftware.softwareVersion As Version,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%microsoft office%365%') Or
(tblSoftwareUni.softwareName Like '%microsoft 365%' And tblAssetCustom.State =
1 And tblAssets.Assettype = -1)
Group By tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
Order By Version,
Total Desc