Notification

Icon
Error

Need to pull a report from 5.22 - I need a report for all software installed including OS by asset

Posted: Monday, March 9, 2015 3:43:04 PM(UTC)
Sdonovan415

Sdonovan415

Member Original PosterPosts: 5
0
Like
This issue has been solved! Click here to view the solution
Hi All,

I am still learning SQL and was wondering if there is a way to create a query to be able to pull a report to list all software installed on each asset. I originally had this query but it does not list the OS.

Select
tblAssets.AssetName,
tblAssets.Lastseen,
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblSoftwareUni.softwareName
Daniel.B
#1Daniel.B Member Posts: 1,150  
posted: 3/10/2015 1:33:32 PM(UTC)
In order to list information about the OS, either add tsysOS.OSname to your report or add another table, tblOperatingsystem, and include tblOperatingsystem.Caption in your report. The following example uses tblOperatingsystem.Caption:
Code:

Select Top 1000000 tblAssets.AssetName,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.Lastchanged,
  tblOperatingsystem.Caption As OS
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblOperatingsystem
    On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblSoftwareUni.softwareName

Active Discussions

Lansweeper Sync information
by  prighi61   Go to last post Go to first unread
Last post: Yesterday at 2:34:06 PM(UTC)
Lansweeper Deleting, removing or hiding default ticket states
by  prighi61  
Go to last post Go to first unread
Last post: Yesterday at 1:16:52 PM(UTC)
Lansweeper Switch Dell S4128 Scan error
by  matteor   Go to last post Go to first unread
Last post: Yesterday at 1:04:03 PM(UTC)
Lansweeper Patch Tuesday report showing wrong results
by  NoZart  
Go to last post Go to first unread
Last post: Yesterday at 11:44:58 AM(UTC)
Lansweeper How do I create a new ticket using the API?
by  prighi61   Go to last post Go to first unread
Last post: Yesterday at 9:36:39 AM(UTC)
Lansweeper IP Location "undefined"
by  Kboyer  
Go to last post Go to first unread
Last post: 9/23/2021 9:25:46 PM(UTC)
Lansweeper Modify root cause list
by  Arno Butter   Go to last post Go to first unread
Last post: 9/23/2021 8:38:25 PM(UTC)
Lansweeper scanning network devices
by  mattscratt  
Go to last post Go to first unread
Last post: 9/23/2021 8:04:43 PM(UTC)