→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎03-29-2022 03:28 PM
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;
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;
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;
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now