Assets WITHOUT A Specific Software Title

[mhammond]

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

[brandon_jones]

No problem. I've looked at simple SQL and could not figure it out. I get another pair of eyes to look at it and they are like you left out a ) or what ever. LOL

[mhammond]

Yep that did it!

That actually was simple now that I look at the code.

I'm always thinking backwards in SQL. LOL

Thank you so much!

[brandon_jones]

Try this. You will need to replace the words 'software name here' with the name of the software. The name of the software will need to be entered exactly and between the single quotes.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'software name here') And tblAssetCustom.State =
1
Order By tblAssets.Domain,
tblAssets.AssetName