Installed Software for a custom Asset Group

[cpauli]

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

[cpauli]

Hi again,

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

Thank you very much for your support.

Best regards
Christian

[Hemoco]

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]

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]

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]

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]

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]

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 · Accepted solution]

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.