Reporting on Windows software since the addition of Windows Store software scanning in 9.3

[Bruce_B]

Since version 9.3, Lansweeper also scans installed Microsoft Store Apps as part of regular Windows software scanning.
Scanned Microsoft store software is listed among other software in asset-specific software overviews as well as general software overviews. Scanned MS store apps are also stored in the same tables, tblSoftware and tblSoftwareUni. You can distinguish between regular software and Windows store apps by using the field MsStore. When this field is set to 1, the software is an MS Store application. This information can also be accessed in our database documentation, which is included in each Lansweeper installation in the Reports\Database Documentation menu.




Below you can find some sample reports that make use of this field.


All scanned Windows software:

SELECT TOP 1000000 tblAssets.AssetID, 
                   tblAssets.AssetName, 
                   tblAssets.Domain, 
                   tblAssets.Username, 
                   tblAssets.Userdomain, 
                   COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon, 
                   tblAssets.IPAddress, 
                   tsysIPLocations.IPLocation, 
                   tsysOS.OSname AS OS, 
                   tblassets.Version AS [OS Version], 
                   tblAssets.Lastseen, 
                   tblAssets.Lasttried, 
                   tblSoftwareUni.softwareName AS Software, 
                   tblSoftware.softwareVersion AS Version, 
                   tblSoftwareUni.SoftwarePublisher AS Publisher,
                   CASE
                       WHEN tblSoftware.CurrentUser = 1
                       THEN 'No'
                       ELSE 'Yes'
                   END AS [Installed for All users?],
                   CASE
                       WHEN tblSoftware.MsStore = 1
                       THEN 'Yes'
                       ELSE 'No'
                   END AS [MS Store Software?]
FROM tblAssets
     INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
     INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
     INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
     INNER JOIN tblState ON tblState.State = tblAssetCustom.State
     INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
     INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
     LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
WHERE tblState.Statename = 'Active'
ORDER BY tblAssets.Domain, 
         tblAssets.AssetName, 
         tblSoftwareUni.SoftwareName, 
         tblSoftware.SoftwareVersion;

All scanned Microsoft Store apps:

SELECT TOP 1000000 tblAssets.AssetID, 
                   tblAssets.AssetName, 
                   tblAssets.Domain, 
                   tblAssets.Username, 
                   tblAssets.Userdomain, 
                   COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon, 
                   tblAssets.IPAddress, 
                   tsysIPLocations.IPLocation, 
                   tsysOS.OSname AS OS, 
                   tblassets.Version AS [OS Version], 
                   tblAssets.Lastseen, 
                   tblAssets.Lasttried, 
                   tblSoftwareUni.softwareName AS Software, 
                   tblSoftware.softwareVersion AS Version, 
                   tblSoftwareUni.SoftwarePublisher AS Publisher,
                   CASE
                       WHEN tblSoftware.CurrentUser = 1
                       THEN 'No'
                       ELSE 'Yes'
                   END AS [Installed for All users?]
FROM tblAssets
     INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
     INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
     INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
     INNER JOIN tblState ON tblState.State = tblAssetCustom.State
     INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
     INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
     LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
WHERE tblState.Statename = 'Active' and MsStore = 1
ORDER BY tblAssets.Domain, 
         tblAssets.AssetName, 
         tblSoftwareUni.SoftwareName, 
         tblSoftware.SoftwareVersion;

All scanned Windows software, excluding Microsoft store apps:

SELECT TOP 1000000 tblAssets.AssetID, 
                   tblAssets.AssetName, 
                   tblAssets.Domain, 
                   tblAssets.Username, 
                   tblAssets.Userdomain, 
                   COALESCE(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) AS icon, 
                   tblAssets.IPAddress, 
                   tsysIPLocations.IPLocation, 
                   tsysOS.OSname AS OS, 
                   tblassets.Version AS [OS Version], 
                   tblAssets.Lastseen, 
                   tblAssets.Lasttried, 
                   tblSoftwareUni.softwareName AS Software, 
                   tblSoftware.softwareVersion AS Version, 
                   tblSoftwareUni.SoftwarePublisher AS Publisher,
                   CASE
                       WHEN tblSoftware.CurrentUser = 1
                       THEN 'No'
                       ELSE 'Yes'
                   END AS [Installed for All users?]
FROM tblAssets
     INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
     INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
     INNER JOIN tsysIPLocations ON tsysIPLocations.LocationID = tblAssets.LocationID
     INNER JOIN tblState ON tblState.State = tblAssetCustom.State
     INNER JOIN tblSoftware ON tblSoftware.AssetID = tblAssets.AssetID
     INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
     LEFT JOIN tsysOS ON tsysOS.OScode = tblAssets.OScode
WHERE tblState.Statename = 'Active' and MsStore = 0
ORDER BY tblAssets.Domain, 
         tblAssets.AssetName, 
         tblSoftwareUni.SoftwareName, 
         tblSoftware.SoftwareVersion;