→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cpauli
Engaged Sweeper
Hi all,

I am new to lansweeper and need some support to create a custom report.

There are a default report "Software: All installed software". Furthermore i have created a asset group with 130 windows machines for our next rollout.

Now i need a report of all installed software on this machines in the custom asset group. Is this possible?

Some helpful feedback is much appreciated.

Best regards
Christian
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
You can use the following report. Please change the red word with the name of the asset group that you want to filter on:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetGroups.AssetGroup = 'name of asset group'
Order By tblAssets.AssetName

To use the report above, do the following:
• Open the report builder under Reports/Create New Report.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.

View solution in original post

8 REPLIES 8
cpauli
Engaged Sweeper
Hi again,

simply great, perfect. Now the report suits 100% our requirements.

Thank you very much for your support.

Best regards
Christian
Hemoco
Lansweeper Alumni
We have made some changes to the report:

Select Top 1000000 Count(tblAssets.AssetID) As total,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unapproved' End As approve,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetGroups.AssetGroup = 'Rollout'
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unapproved' End,
tblAssetGroups.AssetGroup
Having Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unapproved' End In ('unrated', 'unapproved')
Order By total Desc,
tblSoftwareUni.softwareName
cpauli
Engaged Sweeper
Hi again,

thank you for your report but i got the following error message when i try to save the report:

The column name cannot be resolved to a table. Specify the table to which the column belongs. [ Name of ambiguous column = approved ]

Furthermore I am missing the asset group depancy for my "Rollout" Asset group?

Where tblAssetGroups.AssetGroup = 'Rollout'

I am so sorry that i have so much requests regarding this special report 😞

Thank you in advance
Christian
Hemoco
Lansweeper Alumni
Please use the following report:

Select Top 1000000 Count(tblAssets.AssetID) As total,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssetGroups.AssetGroup,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unaproved' End As approved
From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.Approved = 0) Or
(tblSoftwareUni.Approved = 2)
Group By tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssetGroups.AssetGroup,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unaproved' End,
tblSoftwareUni.Approved
Order By total Desc,
tblSoftwareUni.softwareName
cpauli
Engaged Sweeper
wow such a quick support is great 🙂

Ok The Report works but we don't want a direct connection with the asset. So I have changed the sql statement a little bit.

Select Top 1000000 tblAssets.AssetID,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssetGroups.AssetGroup,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unaproved' End As approved,
From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetGroups.AssetGroup = 'Rollout' And (tblSoftwareUni.Approved = 0 Or
tblSoftwareUni.Approved = 2)
Order By tblSoftwareUni.softwareName

So this works fine. But i would to count the AssetID like in the default software report.

As i can see the following line is responsible for counting:

Count(tblSoftware.AssetID) As Total

but this statement isn't possible in your posted report, when i try to insert the line in your report i got the following message:

In aggregate and grouping expressions, the SELECT clause can contain only aggregates and grouping expressions. [ Select clause = tblAssets,AssetID

Have you an idea how i can manage to count the installations like in the default report for this report?

Best regards
Christian


Hemoco
Lansweeper Alumni
Please try the following report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssetGroups.AssetGroup,
Case tblSoftwareUni.Approved When 0 Then 'unrated' When 1 Then 'Approved'
When 2 Then 'unaproved' End As approved
From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetGroups.AssetGroup = 'name of asset group' And
(tblSoftwareUni.Approved = 0 Or tblSoftwareUni.Approved = 2)
Order By tblAssets.AssetName
cpauli
Engaged Sweeper
Thank you very much for your fast answer. The report works pretty well.
I have found the authorization function for software and I love it 😉

So how can i edit the report to get only installed software which is unrated or not allowed for the asset group?

Best regards
Christian
Hemoco
Lansweeper Alumni
You can use the following report. Please change the red word with the name of the asset group that you want to filter on:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetGroups.AssetGroup = 'name of asset group'
Order By tblAssets.AssetName

To use the report above, do the following:
• Open the report builder under Reports/Create New Report.
• Paste the SQL code we provided at the bottom of the page.
• Left-click somewhere in the upper section of the page so the code applies.
• Give the report a Title and hit the Save & Run button to save it. Export options are listed on the left.