Notification

Icon
Error

Report to show uptime IF a specific software is installed

Posted: Friday, May 19, 2017 11:06:12 PM(UTC)
trdmc

trdmc

Member Posts: 1
0
Like
I found a report to show uptime of all assets, however I want to limit that report to show uptimes of assets that have a specific software installed.

The uptime report I'm using is below. What do I need to add to limit by software?

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As Uptime
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 And tblAssets.Uptime Is Not Null
Order By tblAssets.Uptime Desc
David.G
#1David.G Member Administration  
posted: 5/29/2017 12:33:03 PM(UTC)
To be able to filter on a specific software that is installed on a Windows computer, you will have to add the database tables tblSoftware and tblSoftwareUni. Afterwards, you select the database field tblSoftwareUni.SoftwareName and create a filter for the specific software name. I have modified the report you have provided and have pasted it below. You will only have to change the highlighted section in between the %-marks to the specific software name you want to filter your report on.

Code:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' minutes' As Uptime
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblAssetCustom.State = 1 And tblAssets.Uptime Is Not Null And
  tblSoftwareUni.softwareName Like '%[h]YourSoftware[/h]%'
Order By tblAssets.Uptime Desc

Active Discussions

Action Internet history of an user (IE and Firefox)
by  jgranese   Go to last post Go to first unread
Last post: 12/7/2017 6:11:22 PM(UTC)
Action Lansweeper's FREE Remote Control tool
by  galtran  
Go to last post Go to first unread
Last post: 11/29/2017 7:26:48 AM(UTC)
Action Find lost space the easy way (spacesniffer.exe)
by  MoZilla   Go to last post Go to first unread
Last post: 11/10/2017 5:34:43 PM(UTC)
Action Using PsExec and slmgr.vbs to Activate Windows
by  kevinjt4  
Go to last post Go to first unread
Last post: 11/2/2017 7:34:19 PM(UTC)
Action Delete old user profiles
by  warlock1663   Go to last post Go to first unread
Last post: 9/7/2017 7:14:30 PM(UTC)
Action Remote BlueScreenView
by  Tomas  
Go to last post Go to first unread
Last post: 9/5/2017 12:27:47 PM(UTC)
Action Disable Credential Manager
by  wilkinsonian   Go to last post Go to first unread
Last post: 5/23/2017 8:38:07 PM(UTC)
Action "Unable to contact to host:host.domain.com access denied."
by  kltr  
Go to last post Go to first unread
Last post: 4/7/2017 12:16:38 PM(UTC)