Notification

Icon
Error

Creating a count - Creating a count of assets

Posted: Thursday, May 13, 2021 8:10:10 PM(UTC)
Brianne

Brianne

Member Original PosterPosts: 3
0
Like
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.
Andy.S
#1Andy.S Member Posts: 93  
posted: 5/14/2021 10:33:39 AM(UTC)
Hi Brianne,

Is this what your after all software counts under 6:

Code:
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


Brianne
#2Brianne Member Original PosterPosts: 3  
posted: 5/14/2021 6:12:49 PM(UTC)
It is! Now I have to figure out how to display the assetname and location for each one.
Andy.S
#3Andy.S Member Posts: 93  
posted: 5/17/2021 9:32:46 AM(UTC)
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 ...

Code:
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


Brianne
#4Brianne Member Original PosterPosts: 3  
posted: 5/17/2021 7:20:17 PM(UTC)
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.S
#5Andy.S Member Posts: 93  
posted: 5/18/2021 10:38:18 AM(UTC)
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 d'oh!

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

Cheers
A



Active Discussions

Lansweeper Enterprise Options in Menu Bar/Configuration
by  mk@allan   Go to last post Go to first unread
Last post: 6/18/2021 7:38:43 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  Kenneth Lindsay  
Go to last post Go to first unread
Last post: 6/18/2021 3:31:06 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno  
Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)
Lansweeper Exclude Search
by  pryan67  
Go to last post Go to first unread
Last post: 6/16/2021 4:01:43 PM(UTC)
Lansweeper Report: All Apple Mac devices with Memory RAM asset
by  gabrielo   Go to last post Go to first unread
Last post: 6/16/2021 3:17:24 PM(UTC)
Lansweeper Does technical support for LS really respond?
by  tosch  
Go to last post Go to first unread
Last post: 6/16/2021 12:48:50 PM(UTC)