cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
pskup
Engaged Sweeper III
Hello everyone,
I tried to create a report showing all pc with a specific software installed. In addition, I want to know if a specific tool (on these machines) is started via autostart.

I tried to exclude all unwanted entrys in autostart via the where clause "tblAutorunUni.Caption Like N'MyAutostartTool'". But then the pc without these entrys weren't shown, of course.

I tried "left join", "not exist", "not in", "case else" but I can not achieve the desired result.

So i need your help

I have reduced the query to the required entries:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
tblAutorunUni.Caption
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAutorun On tblAssets.AssetID = tblAutorun.AssetID
Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI = tblAutorun.AutorunUNI
Where tblSoftwareUni.softwareName Like 'MySoftware' And
tblAutorunUni.Caption Like N'MyAutostartTool'


I get this table:

PC1 / MySoftware / MyAutostartTool
PC3 / MySoftware / MyAutostartTool

But I want this:

PC1 / MySoftware / MyAutostartTool
PC2 / MySoftware /
PC3 / MySoftware / MyAutostartTool
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You can do this with a CASE statement and a subquery. Otherwise your filter will only show the computers having the software in the autostart.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
Case When tblAssets.AssetID In (Select tblAutorun.AssetID
From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
tblAutorun.AutorunUNI
Where tblAutorunUni.Caption Like 'MySoftware%') Then 'yes' Else 'no'
End As [autorun MySoftware]
From tblAssets
Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like 'MySoftware%'

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
You can do this with a CASE statement and a subquery. Otherwise your filter will only show the computers having the software in the autostart.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblSoftwareUni.softwareName As software,
Case When tblAssets.AssetID In (Select tblAutorun.AssetID
From tblAutorun Inner Join tblAutorunUni On tblAutorunUni.AutorunUNI =
tblAutorun.AutorunUNI
Where tblAutorunUni.Caption Like 'MySoftware%') Then 'yes' Else 'no'
End As [autorun MySoftware]
From tblAssets
Inner Join tblSoftware On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where tblSoftwareUni.softwareName Like 'MySoftware%'