‎07-24-2023
09:22 PM
- last edited on
‎04-01-2024
12:42 PM
by
Mercedes_O
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?
Solved! Go to Solution.
‎07-24-2023 10:17 PM
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
‎07-24-2023 10:42 PM
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?
‎07-24-2023 11:13 PM
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
‎07-24-2023 11:34 PM
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.
‎07-24-2023 11:41 PM - edited ‎07-25-2023 03:07 PM
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
‎07-25-2023 02:47 AM
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.
‎07-25-2023 03:09 PM
Man, yesterday was not my day, I copied the wrong code the second time >_<.
I edited that reply, the report should (hopefully) work now.
‎07-25-2023 03:45 PM
That got it! Thanks very much!
‎07-24-2023 10:17 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now