User-based Inventory Report

[WildPh]

I would like generate a user-based Report which Shows me the following Information:

User - > Computer(s) in a 1:1 or 1:n releationship -> Installed Software

The Report may list only computers where the Name contains "wks" or "lap" and the manufaturer is either Lenovo or Dell.

The Software which is installed on the Computer(s) should be grouped into a) approved and b) not approved.

From user Portion the fields: Firstname, Lastname, Displayname, Department, Company and email should be listed.

thanks very much for your support.

Philipp

[Hemoco]

Simply add the below to the Criteria column for the SubQuery.Domain As Userdomain or tblCPlogoninfo.Domain expression.

= 'lsch_dom00'

[WildPh]

Thanks a lot for the two queries. Great support!
Since the second one generates a lot of rows, I will use the first one for the time being. Could I ask for one more filter in a where clause: Domain = lsch_dom00.

Best regards
Philipp

[Hemoco · Accepted solution]

Further information received via email. Below a copy of our reply. The customer was not asking for user relation reports, but user logon reports.

Use the reports below instead. We only listed logon events that occurred on computers that meet the criteria you specified: “The Report may list only computers where the Name contains "wks" or "lap" and the manufaturer is either Lenovo or Dell.”

To avoid confusion in the future, please do not refer to user logons as user “relations”, as this is what threw off our support staff. Lansweeper has a specific “relation” feature that allows you to manually link assets to other assets or users. When you asked for your report(s), we assumed this was what you were referring to. You are talking about user logons however, which have nothing to do with user “relations”.


Users, last computer users logged into and computer’s software

Select Top 1000000 SubQuery.Username,
SubQuery.Domain As Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
SubQuery1.logontime,
SubQuery1.AssetID,
SubQuery1.AssetName,
SubQuery1.Domain,
SubQuery1.IPAddress,
SubQuery1.Manufacturer,
SubQuery1.Model,
SubQuery1.[Authorization],
SubQuery1.softwareName,
SubQuery1.softwareVersion,
SubQuery1.SoftwarePublisher
From (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery
Left Join tblADusers On tblADusers.Username = SubQuery.Username And
tblADusers.Userdomain = SubQuery.Domain
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'approved'
When 2 Then 'not approved' End As [Authorization],
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where (tblAssets.AssetName Like '%wks%' Or tblAssets.AssetName Like '%lap%') And
(tblAssetCustom.Manufacturer Like '%lenovo%' Or tblAssetCustom.Manufacturer Like
'%dell%')) SubQuery1 On SubQuery1.Username = SubQuery.Username
And SubQuery1.Userdomain = SubQuery.Domain And SubQuery1.logontime =
SubQuery.LastLogon
Order By Userdomain,
SubQuery.Username,
SubQuery1.[Authorization],
SubQuery1.softwareName


Users, all computers users logged into and computers’ software

Select Top 1000000 SubQuery1.Username,
SubQuery1.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
SubQuery1.logontime,
SubQuery1.AssetID,
SubQuery1.AssetName,
SubQuery1.Domain,
SubQuery1.IPAddress,
SubQuery1.Manufacturer,
SubQuery1.Model,
SubQuery1.[Authorization],
SubQuery1.softwareName,
SubQuery1.softwareVersion,
SubQuery1.SoftwarePublisher
From tblADusers
Inner Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'approved'
When 2 Then 'not approved' End As [Authorization],
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Where (tblAssets.AssetName Like '%wks%' Or tblAssets.AssetName Like '%lap%') And
(tblAssetCustom.Manufacturer Like '%lenovo%' Or tblAssetCustom.Manufacturer Like
'%dell%')) SubQuery1 On SubQuery1.Username = tblADusers.Username
And SubQuery1.Userdomain = tblADusers.Userdomain
Order By SubQuery1.Userdomain,
SubQuery1.Username,
SubQuery1.logontime Desc,
SubQuery1.[Authorization],
SubQuery1.softwareName

[Hemoco]

Please try the following report. Are you also sure that you have assets that have a user relation?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tsysAssetRelationTypes.Name,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
Case tblSoftwareUni.Approved When 0 Then 'not specified'
When 1 Then 'authorized' Else 'not authorized' End As 'approved',
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
left Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID

[WildPh]

Thanks for the seccond version of the query. I still get the same message (This report has no results!). I also tried without the where and order by clause:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tsysAssetRelationTypes.Name,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
Case tblSoftwareUni.Approved When 0 Then 'not specified'
When 1 Then 'authorized' Else 'not authorized' End As 'approved',
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID

[Hemoco]

Can you please try with the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tsysAssetRelationTypes.Name,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
Case tblSoftwareUni.Approved When 0 Then 'not specified'
When 1 Then 'authorized' Else 'not authorised' End As 'approved',
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblAssets.AssetName Like '%wks%' Or tblAssets.AssetName Like '%lap%') And
(tblAssetCustom.Manufacturer Like '%dell%' Or tblAssetCustom.Manufacturer Like
'%Lenovo%')
Order By tblAssets.AssetName,
tblSoftwareUni.Approved

[WildPh]

Thanks very much for your prompt reply.
when i run the Report I get the message: This report has no results!.

[Hemoco]

Please use the following query:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tsysAssetRelationTypes.Name,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Displayname,
tblADusers.Department,
tblADusers.Company,
tblADusers.email,
Case tblSoftwareUni.Approved When 0 Then 'not specified'
When 1 Then 'authorized' Else 'not authorised' End As 'approved',
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblAssets.AssetName Like '%wks%' And tblAssetCustom.Manufacturer Like
'%dell%') Or
(tblAssets.AssetName Like '%lap%' And tblAssetCustom.Manufacturer Like
'%Lenovo%')
Order By tblAssets.AssetName,
tblSoftwareUni.Approved