→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Chad
Engaged Sweeper II
Hello everyone, I am new to building reports with Lansweeper and was curious if someone could point me in the right direction.

I would like to be able to run a report of all "Dell" branded PC's regardless of model and show the OS that they are running as well.


1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Using the default base query as a starting point:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'

View solution in original post

4 REPLIES 4
RCorbeil
Honored Sweeper II
I got fed up trying to link to the software directly without returning too many or too few results so opted to join to a sub-select.

First, I suggest you run this:
SELECT Distinct
softwarename
FROM
tblSoftwareUni
WHERE
SoftwarePublisher LIKE 'Microsoft%'
AND softwareName LIKE 'Microsoft Office%'
AND softwareName NOT LIKE '%Viewer%'
AND softwareName NOT LIKE '%Visio %'
AND softwareName NOT LIKE '%Project%'
AND softwareName NOT LIKE '%web comp%'
AND softwareName NOT LIKE '%connector%'
AND softwareName NOT LIKE '%click-to-run%'
AND softwareName NOT LIKE '%validat%'
AND softwareName NOT LIKE '%live meet%'

Microsoft has named way too many programs and components "Microsoft Office something-or-other" that aren't actually Microsoft Office. I've included several substrings to search for that are in my inventory. If you turn up anything other than actual Microsoft Office results with the above, you'll want to add to the list of exceptions. You'll need that list for the actual query you're after.

Adding to the "list me the Dells" query:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType Like 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType Like 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
msoffice.SoftwarePublisher,
msoffice.softwareName,
msoffice.softwareVersion
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
LEFT JOIN ( SELECT
tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.SoftwareName,
tblSoftware.SoftwareVersion
FROM
tblSoftware
INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftwareUni.SoftwarePublisher LIKE 'Microsoft Corp%'
AND tblSoftwareUni.softwareName LIKE 'Microsoft Office%'
AND tblSoftwareUni.softwareName NOT LIKE '%Viewer%'
AND tblSoftwareUni.softwareName NOT LIKE '%Visio %'
AND tblSoftwareUni.softwareName NOT LIKE '%Project%'
AND tblSoftwareUni.softwareName NOT LIKE '%web comp%'
AND tblSoftwareUni.softwareName NOT LIKE '%connector%'
AND tblSoftwareUni.softwareName NOT LIKE '%click-to-run%'
AND tblSoftwareUni.softwareName NOT LIKE '%validat%'
AND tblSoftwareUni.softwareName NOT LIKE '%live meet%'
) AS msoffice ON msoffice.AssetID = tblAssets.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'

(Inclusion of the publisher and version number are optional, of course.)

If you came up with more exceptions that you had to add to the short query above, add your new exceptions to the list in the big query.
Chad
Engaged Sweeper II
If I wanted to keep all of the data that is on there so far as well as add the ability to see if Microsoft Office is on the machine, is that possible as well?
RCorbeil
Honored Sweeper II
Using the default base query as a starting point:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'
Chad
Engaged Sweeper II
RC62N wrote:
Using the default base query as a starting point:
SELECT Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 AS icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tsysOS.OSname,
CASE
WHEN tblComputersystem.SystemType LIKE 'X86%' THEN '32-bit'
WHEN tblComputersystem.SystemType LIKE 'x64%' THEN '64-bit'
ELSE ''
END AS Bitness,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
INNER JOIN tsysOS ON tblAssets.OScode = tsysOS.OScode
INNER JOIN tblComputersystem ON tblAssets.AssetID = tblComputersystem.AssetID
WHERE
tblAssetCustom.State = 1
AND tblAssetCustom.Manufacturer LIKE 'Dell%'


Incredible, did exactly what I wanted. This will help me immensely, thank you very much!