I presume the title says it all.
I am trying to create a report that shows assets that DON'T have a specific software title included, but ONLY that title. I've gotten close, but my limited knowledge of the SQL language not only yields results of assets without the software I'm querying, but repeats each asset multiple times to included every OTHER software title installed on it.
I know it's because in my coding I use NOT LIKE '<software title>' to where the report is simply doing what is asked: to basically EXCLUDE that software title; but it also includes everything else.
- - - - - - -
When I click on a software title (any title) LS will instantly show all the assets that have that software title installed. Unfortunately, I can't "edit report" because technically it wasn't a report, but just a list based on a database entry.
Basically I want to run a comparative report of these results of HAVING this software installed vs. assets WITHOUT the software installed, but base it SOLELY on that software and to NOT include the entries of OTHER software titles on assets WITHOUT the software
- - - - - - -
I think there has to be an 'INNER JOIN' connection somewhere for comparative tables, but I'm not sure how to go about implementing it.
Here is the basic code, looking for assets WITHOUT "Malwarebytes Endpoint Agent". Again, based on my basic coding abilities it is doing exactly as it's asked: show assets without the software. But I need just the basic list of all the assets without duplicates and repeats because of the entries that are additionally being added...
Sorry if that's convoluted.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftware.SoftwareID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Where tblSoftware.SoftwareID Not Like 'Malwarebytes Endpoint Agent' And
tblAssetCustom.State = 1