cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

Bruce_B
Lansweeper Tech Support
Lansweeper Tech Support
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.

DB documentation


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;
0 REPLIES 0