Creating a count

[briannebew]

I am struggling with editing a report and getting it to create a count of how many assets have each software in my report. I then want to take this and display on my report the software that has 5 or less assets assigned to it and the details of which ones. Has anyone been successful with this? I am familiar with Crystal and the logic in Lansweeper is just a tad different.

[briannebew]

This is great! Thank you! Now I have to figure out to add install date since they just requested that and we should be good to go.

[Andy_Sismey]

Hi Brianne,

So I dont think that would be possible, the results page when you click the link calls a default software information page based on the software information from each URL, not sure how you could do this ...with my limited knowledge

Best way would be to create a report with all this information and export to Excel ?

Cheers
A

[briannebew]

It is! Now I have to figure out how to display the assetname and location for each one.

[Andy_Sismey]

Hi,

I'm no expert but the way I get round links within reports, is to add a URL based on the actual Lansweeper queries, its a bit of a "workaround" but works :-), just change the "SERVERNAME" to yours and this should work ...

Select Top 1000000 Count(tblAssets.AssetName) As Count,
  'http://SERVERNAME:81/Report/report.aspx?det=Web50FindSoftwareFullVersion&@soft=' + Query1.softwareName + '&@v=' + Query1.softwareVersion + '&title=' + Query1.softwareName As hyperlink_hyp,
  Query1.softwareName As hyperlink_name_hyp,
  Query1.softwareVersion
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Right Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID,
        tblSoftware.softwareVersion
      From tblSoftwareUni
        Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID)
  Query1 On tblAssets.AssetID = Query1.AssetID
Where tblAssetCustom.State = 1
Group By Query1.softwareName,
  Query1.softwareVersion
Having Count(tblAssets.AssetName) < 6
Order By Count Desc

[Andy_Sismey]

Hi Brianne,

Is this what your after all software counts under 6:

Select Top 1000000 Count(tblAssets.AssetName) As Count,
  Query1.softwareName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Right Join (Select tblSoftwareUni.softwareName,
        tblSoftware.AssetID,
        tblSoftware.softwareVersion
      From tblSoftwareUni
        Inner Join tblSoftware On tblSoftwareUni.SoftID = tblSoftware.softID)
  Query1 On tblAssets.AssetID = Query1.AssetID
Where tblAssetCustom.State = 1
Group By Query1.softwareName,
  Query1.softwareVersion
Having Count(tblAssets.AssetName) < 6
Order By Count Desc