Database reporting

[mikkeland]

Hi,
We are presently evaluating lansweeper at a client site, and have run in to something I can't figure out how to do.
It's possible to add lots of relevant info to all discovered assets. However, this particular client wants to be able to add similar data to all discovered databases and thereby link them to various application servers.

I've edited a report to get a nice overview of all MSSQL databases, but cannot edit the individual assets to add the relevant notes.

Will database reporting be improved in the coming version 6 or am I doing something wrong?

Sincerely
Mikkel

[Daniel_B · Accepted solution]

Ok, thanks for clarification. Database information is scanned from WMI and stored in table tblSQLDatabases. You can't enter custom details directly to the database, but you could enter custom data into custom fields of the computer the database is running on. Then you could create a report based on your databases which would list the content of the custom fields as well. Please find an example report below.

Note: Titles and data types of asset custom fields need to be defined under Configuration\Asset pages, section Asset custom fields.

Select Top 1000000 tblSqlDatabases.name As [DB name],
  tblSqlServers.displayVersion As [SQL version],
  tblSqlServers.skuName As [SQL edition],
  tblAssets.AssetName,
  tblAssetCustom.AssetID,
  tblAssetCustom.Custom1 As [custom field 1],
  tblAssetCustom.Custom2 As [custom field 2],
  tblAssetCustom.Contact,
  tblADusers.Displayname As [Host AD manager]
From tblSqlDatabases
  Inner Join tblSqlServers On tblSqlServers.sqlServerId =
    tblSqlDatabases.sqlServerId
  Inner Join tblAssets On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADusers On tblADComputers.ManagerADObjectId =
    tblADusers.ADObjectID
Where tblAssetCustom.State = 1
Order By [DB name],
  tblAssets.AssetName

[mikkeland]

Daniel.B wrote:
Ok, thanks for clarification. Database information is scanned from WMI and stored in table tblSQLDatabases. You can't enter custom details directly to the database, but you could enter custom data into custom fields of the computer the database is running on. Then you could create a report based on your databases which would list the content of the custom fields as well. Please find an example report below.

Note: Titles and data types of asset custom fields need to be defined under Configuration\Asset pages, section Asset custom fields.

Select Top 1000000 tblSqlDatabases.name As [DB name],
  tblSqlServers.displayVersion As [SQL version],
  tblSqlServers.skuName As [SQL edition],
  tblAssets.AssetName,
  tblAssetCustom.AssetID,
  tblAssetCustom.Custom1 As [custom field 1],
  tblAssetCustom.Custom2 As [custom field 2],
  tblAssetCustom.Contact,
  tblADusers.Displayname As [Host AD manager]
From tblSqlDatabases
  Inner Join tblSqlServers On tblSqlServers.sqlServerId =
    tblSqlDatabases.sqlServerId
  Inner Join tblAssets On tblAssets.AssetID = tblSqlServers.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADusers On tblADComputers.ManagerADObjectId =
    tblADusers.ADObjectID
Where tblAssetCustom.State = 1
Order By [DB name],
  tblAssets.AssetName

Cool - I've been away for a few days, but will try it out next week!

/Mikkel

[mikkeland]

Hi,
Unfortunately that is not what I need to accomplish - I need to report on the individual databases found on variuous SQL instances.
As in
Name: database1
System: financial reporting
System owner: CFO
Contact: +123 456 7890
3rd party: Name of supplier

Name: database2
System: HR reporting
System owner: HR manager
Contact: +123 456 7890
3rd party: Name of supplier of HR product

That way I can make a report containing all assets belonging to (or contributing to) the various systems/applications.

So the issue basically is that a database is not treated as an asset, hence no custom attributes can be attached to it.

/mikkel

[Daniel_B]

The web console allows applying changes to multiple assets on the Assets lists. Filter for assets which should receive the same update and hit Mass change.

However, you won't be able to filter on SQL Server information. This can only be done through a database scripts. We don't offer support for creation of database scripts, but if you have somebody who knows SQL, it should be relatively simple. Create a report which lists relevant computers, then update a field on assets which are listed by that report. Do create a backup of your database before running a script on it.