Report to show certain fields please

[BradG]

Hi,

I am wanting to generate a report showing the following, if it all possible...

Computer Name, Department of User, Model of Equipment (Or if PC or Laptop), If they are a member of a specific AD Account, If they have a specific piece of software installed, and lastly the mobile number (Which is on the users AD Account, or blank if non)

I am pretty new to the reports and so far come up with the below ..

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.IPAddress,
tblAssetCustom.Model,
tblADusers.Department As Department,
tblADusers.Mobile,
tblADGroups.Description,
tblADGroups.Name,
tblAssets.Username
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,
tblADusers,
tblADGroups
Where tblAssetCustom.State = 1


From this I guess i could be able to filter out the machines i need and specify the group ... Though i am not sure if they are correctly linked up in the report .. Also, the 1000000 results I get back are all from the Lansweeper server, how do i choose not to include this ...

Hopefully someone can shine some light on the request please ? Running V.6022

Many thanks in advance !

[BradG]

Hi Mike,

Many thanks for the reply back, I have tried that and it looks good ! Seems to pull back everything I need

Thanks alot for your help !

Cheers.

[MikeMc]

This should be a good start for you.

• Line 10 - You can change the group name here.
  
• Line 17 - You can change the software name between the percent signs.

Also, the 1000000 results I get back are all from the Lansweeper server, how do i choose not to include this ...

I'm not sure what you mean by this part, but I believe this is automatically added when Lansweeper formats your SQL queries under the report editor.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblADusers.Name,
  tblADusers.Department,
  tblADusers.Mobile,
  tblAssetCustom.Model,
  (Select (Case When Exists(Select 1
      From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
          tblADMembership.ParentAdObjectID
      Where tblADMembership.ChildAdObjectID = tblADusers.ADObjectID And
        tblADGroups.Name = 'Domain Admins') Then 'True' Else 'False'
    End)) As GroupMembership,
  (Select (Case When Exists(Select 1
      From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblSoftware.softID
      Where tblSoftware.AssetID = tblAssets.AssetID And
        tblSoftwareUni.softwareName Like '%Java 8%') Then 'True' Else 'False'
    End)) As SoftwareInstalled
From tblAssets
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblADusers On tblADusers.Userdomain = tblAssets.Userdomain And
    tblADusers.Username = tblAssets.Username
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName