Need Help With a Specific Software Audit For Workstations Only

[NCI_Matt]

Hello all,

So I have used the code provided here for a long time when making reports on specific software. In this case though I need the report to only show workstations, and not any servers. Is that possible?

[NCI_Matt]

This works perfectly Kevin! Thanks!  I hate to ask but i'm also fighting with what is essentially a reverse of this report as well. It's a software missing audit as show here.  Is it also possible to make something like this for workstation only?

[KevinA-REJIS]

Sure thing, I've got a couple similar reports for missing software, see if this one will work:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblADusers.Displayname As [User],
  tsysOS.Image As icon
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
      tblComputersystem.Domainrole
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%ExampleSoftware%') And
  tblAssetCustom.State = 1 And tblDomainroles.Domainrolename In
  ('Stand-alone workstation', 'Member workstation')
Order By tblAssets.AssetName

[NCI_Matt]

Didnt quite work, unfortunately. This gave me a repeating list of every workstation repeating for every software they had that was not like %ExampleSoftware%

 So for example it listed one workstation 20 times because it had 20 software items that didnt match %ExampleSoftware%

I actually had a similar result about an hour ago while fighting with this.

[KevinA-REJIS]

I knew I should have tested that first, forgot an important word at the beginning:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblADusers.Displayname As [User],
  tsysOS.Image As icon
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
      tblComputersystem.Domainrole
Where tblAssets.AssetID Not In (Select Top 1000000 tblSoftware.AssetID
    From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%ExampleSoftware%') And
  tblAssetCustom.State = 1 And tblDomainroles.Domainrolename In
  ('Stand-alone workstation', 'Member workstation')
Order By tblAssets.AssetUnique

[NCI_Matt]

Still not working out after that last one unfortunately. I'm currently working off the code from here, but trying to add in the tblDomainroles.Domainrolename In ('Stand-alone workstation', 'Member workstation') from your original reply, but so far I havent had any luck.

[KevinA-REJIS]

Man, yesterday was not my day, I copied the wrong code the second time >_<.

I edited that reply, the report should (hopefully) work now. 

[NCI_Matt]

That got it! Thanks very much!

[KevinA-REJIS · Accepted solution]

Try this out:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.Username,
  tblADusers.Displayname As [User],
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  tblSoftwareUni.SoftwarePublisher As publisher,
  tsysOS.Image As icon
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
      tblComputersystem.Domainrole
Where tblSoftwareUni.softwareName Like '%ExampleSoftware%' And
  tblAssetCustom.State = 1 And tblDomainroles.Domainrolename In
  ('Stand-alone workstation', 'Member workstation')
Order By tblAssets.AssetName,
  software,
  version