→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Sergey_Uvarov
Engaged Sweeper
Hello. I need help with some specific report.
I need to create a custom report, which would be displayed in parallel columns version and edition of the installed Microsoft SQL Server and the version of another specific software that is also installed on the computer. All that I have previously tried to create, displays only one type of software, but I need to line where the basic information on the computer and the versions of these 2 applications will be. Thank you in advance for your help!
3 REPLIES 3
RCorbeil
Honored Sweeper II
FROM
...
INNER JOIN tblComputerSystem ON tblComputerSystem.AssetID = tblAssets.AssetID
...
WHERE
tblassetcustom.State = 1
AND tblComputerSystem.DomainRole > 1
DomainRole 0 and 1 are standalone and member workstations, respectively. DomainRole 2, 3, 4, 5 are servers.

If you want to display the role name,
FROM
...
INNER JOIN tblDomainRoles ON tblDomainRoles.DomainRole = tblComputerSystem.DomainRole
and add tblDomainRoles.DomainRoleName to your report.
Sergey_Uvarov
Engaged Sweeper
Big thanks for the report. One small question - what parameter must be added so that the result is shown only server based on Windows Server?
RCorbeil
Honored Sweeper II
Select Top 1000000
tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblSqlServers.displayVersion As [SQL Server version],
tblSqlServers.skuName As [SQL SKU name],
tblSqlServers.fileVersion As [SQL file version],
s1.SoftwarePublisher AS [soft1 Publisher],
s1.SoftwareName AS [soft1 Name],
s1.SoftwareVersion AS [soft1 Version],
s2.SoftwarePublisher AS [soft2 Publisher],
s2.SoftwareName AS [soft2 Name],
s2.SoftwareVersion AS [soft2 Version],
tblassets.Lastseen,
tblassets.Lasttried
From
tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblSqlServers On tblSqlServers.AssetID = tblAssets.AssetID
Left Join (SELECT
tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.SoftwareName,
tblSoftware.SoftwareVersion
FROM tblSoftware
Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftwareUni.SoftwareName LIKE '%software name 1%'
) AS s1 ON s1.AssetID = tblAssets.AssetID
Left Join (SELECT
tblSoftware.AssetID,
tblSoftwareUni.SoftwarePublisher,
tblSoftwareUni.SoftwareName,
tblSoftware.SoftwareVersion
FROM tblSoftware
Inner Join tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
WHERE
tblSoftwareUni.SoftwareName LIKE '%software name 2%'
) AS s2 ON s2.AssetID = tblAssets.AssetID
Where
tblassetcustom.State = 1
Order by
tblAssets.AssetName,
tblSqlServers.displayVersion,
tblSqlServers.skuName