Report that lists clients with have not a specific service

[Max90]

Hi,
I have to make a report that brings out all the pc names that do not have the "LMservice" service.
How can I do?

In this way I can then do a scheduled deployment on the PCs that do not have the service.

Thank you!

[Andy_Sismey]

Have you tried this report, this should give you a good starting point :- https://www.lansweeper.com/forum/yaf_postst12639_Report-that-lists-clients-with-a-specific-service-with-the-state-stopped-not-running.aspx#post45526

[Max90]

Andy.S wrote:
Have you tried this report, this should give you a good starting point :- https://www.lansweeper.com/forum/yaf_postst12639_Report-that-lists-clients-with-a-specific-service-with-the-state-stopped-not-running.aspx#post45526

Yes, i had seen.
I did this by adding "not like" instead of "like".
The problem is that so I pull out the asset repeated several times (once for each service of the PC).
I would need the name of the pc to come out only once.
i also tried with "distinct" but there is something wrong .. i am not a sql expert.

[Max90]

I have resolved.

Select Distinct Top 1000000 a.AssetID,
a.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
a.IPAddress,
a.Lastseen,
a.Lasttried
From tblAssets a
Inner Join tblAssetCustom On a.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = a.Assettype
Inner Join tblServices On a.AssetID = tblServices.AssetID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Where tblServicesUni.Caption Not Like '%LMservice%' And tblAssetCustom.State = 1
And Not Exists(Select Null
From tblAssets b Inner Join tblAssetCustom c On b.AssetID = c.AssetID
Inner Join tsysAssetTypes d On d.AssetType = b.Assettype Inner Join
tblServices e On b.AssetID = e.AssetID Inner Join tblServicesUni f On
f.ServiceuniqueID = e.ServiceuniqueID
Where f.Caption Like '%LMservice%' And c.State = 1 And b.AssetID =
a.AssetID)
Order By a.AssetName