verify multiple installed software in all the windows 10 machines

[nectw_mis]

Hi Sir

My company request install specific softwares in the machine.

I need to check all the softwares installed status for client machines in companny. 

Now I only know how to show one software status in the report. But I want to show multi software status in the report.

The follow sheet is what I want to show in the report.

Could you help me how to archieve my request?



asset name

username

lastname

firstname

title

department

Widnows edition

Version

Firstseen

Lastseen

7-zip

7-zip version

lsagent version

lsagent version

3CX

3CX version

112775N1

wisley-huang

huang

wisley

engineer

MIS

Windows 10 enterprise

22H1

2020/1/1

20230/02/03

YES

1.1.1

YES

10.0.1.1

NO

NA

123456P1

david

cheng

david

director

MIS

Windows 10 enterprise

21h2

2020/1/1

20230/02/03

NO

N/A

YES

8.9.0.1

YES

5.1

[nectw_mis]

Hi 
update the report, now I could show the software's installed status in the report.
But I want to know which version of the software are installed in the report. 
Everyboy could help me to modify the report?

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tblADusers.Lastname,
tblADusers.Firstname,
tblassets.IPAddress,
tblOperatingsystem.Caption As [Windows edition],
tblOperatingsystem.Version As Build,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
When '10.0.19043' Then '21H1'
When '10.0.19044' Then '21H2'
When '10.0.19045' Then '22H2'
Else '?'
End As [OS Version],
tblassets.Lastseen,
Case
When Tanium.AssetID Is Not Null Then 'Yes'
Else 'No'
End As [Tanium Installed],
Case
When Malwarebytes.AssetID Is Not Null Then 'Yes'
Else 'No'
End As [Malwarebytes Installed],
Case
When Lsagent.AssetID Is Not Null Then 'Yes'
Else 'No'
End As [Lsagent installed],
Case
When CX.AssetID Is Not Null Then 'Yes'
Else 'No'
End As [3CX installed]
From tblassets
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Inner Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tblADComputers On tblassets.AssetID = tblADComputers.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Tanium%') Tanium On
Tanium.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Malwarebytes%') Malwarebytes On
Malwarebytes.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Lsagent%') Lsagent On
Lsagent.AssetID = tblassets.AssetID
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%3CX%') CX On CX.AssetID =
tblassets.AssetID
Inner Join tblADusers On tblassets.Username = tblADusers.Username
Inner Join tblOperatingsystem On
tblassets.AssetID = tblOperatingsystem.AssetID
Inner Join tblSoftware On tblassets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblNetwork.Description Like '%' And tblassetcustom.State = 1
Group By tblassets.AssetID,
tblassets.AssetName,
tblassets.Username,
tblADusers.Lastname,
tblADusers.Firstname,
tblassets.IPAddress,
tblOperatingsystem.Caption,
tblOperatingsystem.Version,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
When '10.0.19041' Then '2004'
When '10.0.19042' Then '20H2'
When '10.0.19043' Then '21H1'
When '10.0.19044' Then '21H2'
When '10.0.19045' Then '22H2'
Else '?'
End,
tblassets.Lastseen,
Case
When Tanium.AssetID Is Not Null Then 'Yes'
Else 'No'
End,
Case
When Malwarebytes.AssetID Is Not Null Then 'Yes'
Else 'No'
End,
Case
When Lsagent.AssetID Is Not Null Then 'Yes'
Else 'No'
End,
Case
When CX.AssetID Is Not Null Then 'Yes'
Else 'No'
End