cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mikkeland
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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

View solution in original post

4 REPLIES 4
Daniel_B
Lansweeper Alumni
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
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
Engaged Sweeper
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
Lansweeper Alumni
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.