Updating custom fields via odbc

[MustQ]

Hello,

So here is our situation:

We are using MS SQL 2012 for the server for lansweeper. I have the ability to use EITHER MS Access 2010 or 2013. adp files are no longer supported for SQL 2012.

Objective: Combine tbl_assets + tbl_assetcustom onto the same 'sheet' and modify custom fields there.

I would like to have an 'excel like' experience when I am updating the 'custom fields' of each of our assets. I know that Lansweeper doesn't support customizations and we do not plan to customize the lansweeper DB. It is just easier to update data on each asset from an 'excel like' view than it is to browse for each asset and enter it via the web console.

Whenever I try to use access 2013 to 'link to the data source by creating a linked table.", I receive the below message:

the operation failed. there are too many indexes on table

I'm a windows sys admin, not strong in SQL or working with databases. I've tried installing LibreOffice base and working with that but it's not "obvious" how to do things there either. I've successfully run 'queries" inside sql directly through sql management studio, but queries don't allow you to modify data.

No amount of googling has been able to help me to date because they all suggest deleting the 'excess indexes" because of MS access limitations, but as this is a proprietary product I do not know what is "excess", and lansweeper won't support it if I do.

I look forward to any help anyone can provide on this please :). I believe it's time the knowledgebase was updated for the newer versions of Access and SQL.

[Hemoco]

You need to modify each table separately. Note that we do not currently provide support for this. An import tool will be included in Lansweeper 5.1. We hope to release a 5.1 beta in either late September or October.

[MustQ]

I've hit a snag in my "MS access 2013 modifying the DB guide." I receive an error when I try to ADD new assets to the following web report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetType,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Description,
tblAssetCustom.Comments,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Custom1 As [Invoice Date],
tblAssetCustom.Location,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom2 As Vendor,
tblAssetCustom.Custom3 As [P/O #],
tblAssetCustom.Custom4 As [Invoice #],
tblAssetCustom.Custom5 As [Payment Type],
tblAssetCustom.Custom6 As [Invoice Total],
tblAssetCustom.Custom7 As [Price Per Unit],
tblAssetCustom.Custom8 As [Original Assigned User],
tblAssetCustom.Custom9 As [Associated PC],
tblAssetCustom.State
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetType = 208

The error is: ODBC -- inserton a linked table XXXXXX failed
[Microsfot]ODBC SQL Driver SQL Server] View of function 'REPORTNAME' is not updateable because the modification affects multiple base tables. (#4405)

Any clue per chance what I can do to get around this?

[Hemoco]

- For VMware, you require tblAssets, tblAssetCustom and the "tblVmware" database tables.
- For Linux, you require tblAssets, tblAssetCustom and the "tblLinux" database tables. A sample Linux report can be seen below.
- For other network devices, you require tblAssets and tblAssetCustom.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblLinuxSystem.OSRelease,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Processor,
tblAssets.NrProcessors,
tblState.Statename As State,
tsysIPLocations.IPLocation,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblLinuxHardDisks.Filesystem,
tblLinuxHardDisks.Size,
tblLinuxHardDisks.Available,
tblLinuxHardDisks.Used,
tblLinuxHardDisks.Percentage,
tblLinuxHardDisks.MountedOn,
tblLinuxNetworkDetection.Name,
tblLinuxNetworkDetection.Mac,
tblLinuxNetworkDetection.Ipv4
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblLinuxHardDisks On tblAssets.AssetID = tblLinuxHardDisks.AssetID
Inner Join tblLinuxNetworkDetection On tblAssets.AssetID =
tblLinuxNetworkDetection.AssetID
Where tsysAssetTypes.AssetTypename = 'linux' And tblAssetCustom.State = 1 And
tblLinuxNetworkDetection.Broadcast Is Not Null
Order By tblAssets.IPNumeric,
tblLinuxHardDisks.Filesystem

[MustQ]

Okay that is something I suppose I can work with, AS LONG AS the fields remain in the same order/structure as The built in report for "computer: system configuration info". This way I can run two/many reports, export to excel, and merge them into one sheet.

The reports I need (in the same structure) need the following asset types:

VMware hosts
Linux hosts
Monitors/screens
Telephones (IP)
Firewalls / Network equipment

what database tables would I need to pull from for these types of assets?

[MustQ]

Cool, only one thing really remaining on this:

The built in report for "computer: system configuration info" restricts the scope of assets it finds to windows computers (other assets like our vmware servers, linux, firewalls, switches/network etc). Basically any/all assets that are found by lansweeper are needed to be displayed in this report for us. How do I broaden this selection?

[Hemoco]

MustQ wrote:
Cool, only one thing really remaining on this:

The built in report for "computer: system configuration info" restricts the scope of assets it finds to windows computers (other assets like our vmware servers, linux, firewalls, switches/network etc). Basically any/all assets that are found by lansweeper are needed to be displayed in this report for us. How do I broaden this selection?

You would have to create separate reports for VMware, Linux, Mac... as these asset types' data is stored in different database tables.

[Hemoco]

In regards to your questions:
1) Yes, but an ID should automatically be assigned.
2) Run the report below for a list of asset types and asset type IDs.

Select * from tsysassettypes

3) Fields that contain scanned data are not locked unless they have a corresponding "lock" field. Anything added to tblOperatingsystem will be overwritten for instance, but tblAssetCustom.WarrantyDate will not be overwritten if tblAssetCustom.WarrantyDateLock is set to 1.

[MustQ]

I have had a 'breakthrough' on this issue, and I think it would be a good KB article.

Keep in mind that the goal of the report I'm trying to generate is a financial overview for our Finance dept (non IT/Technical people), who wants EVERYTHING (including monitor info) on one excel page in the final outcome. This means that there's a ton of 'custom fields' that need to be seen in this report, and many asset types will be needed (Monitor, Laptop, Desktop, Misc Computer kit, servers, network, etc).

It is ENTIRELY possible to use MS SQL 2012STD and Access 2013 to modify the data live in the DB, without the need for an ADP file. Please follow these instructions (click spoiler):

1) In the lansweeper console, run the report called "Computer: System Configuration Overview".

2) Edit this report

3) In the report editor, Add the Custom Fields you desire from tblAssetCustom, and sort them out as you desire.

4) Step 4 depends on if you have monitors entered as their own assets, or if you will rely upon the scanned info from the DB. I'm still working on this, and will require further play so I will finalize this guide later

5) Because you are adding multiple types of assets to this report and it needs to be clear for non-IT people, I recommend adding the field "Assettype" from tblAssets to the first column (A in excel). This is because monitors from the scanned data will show up on rows, causing 'duplicate' lines of "Asset ID" and "assetunique" (so your PC's SMB name will appear on multiple rows because of monitors/video cards). It becomes important to identify your asset type first when looking at this data.

6) SAVE AS (so you don't erase the original/default report) any name you like. Run the report

7) Despite the fact you entered a name for the report, this name is not what is written to the lansweeperdb. To find the 'real name' of this report, look at the web address of the report: http://LANSWEEPERWEBSITE/report.aspx?det=web50repTHENAMEOFTHEREPORTISHERE

😎 Open MS Access 2013, create a blank desktop database.

9) Click on External Data and choose ODBC Database.

10) Link to the data source by creating a linked table

11) Go to Machine Data, and (if necessary) create a new ODBC connection to your lansweeper db

12) When prompted to pick the tables, you now only need to pick one table. It will be called dbo.web50rep%%%%%%%%%%%%%%% (Where % is the name of the report you found in step 7). Be careful to only select this table and not more than one!

13) You'll now be presented with the data in the exact same layout as you set it up in the report, including custom fields and any additional tables you added to the report (with some 'hidden fields' now visible that I advise against editing). You can use the 'tables' at the top to sort the data in any order you like to make it easier to navigate (I sort by AssetUnique)

14) be very careful because no 'save' is necessary to modify this data. Simply going to a field and writing something is writing that live to the DB. The save button in MS access 2013 just saves your local file settings. Point: You're writing directly to a db without a "I'm sure/satisfied button". Use with caution, it's powerful!

Questions for lansweeper team:

1) Now that I have this data in MS access displayed before me, if I want to add a new asset via modifying the db directly I am concerned about the AssetID. Can I choose any unused asset id myself (pick a random number even so long as it's unused) safely?

2) A windows OS computer (laptop/desktop/etc) shows up as "assettype -1" in the db. What are the other asset type codes, and how do I add more asset types to this report (report currently seems limited to windows assets only)?

3) Data I enter manually: Does this become 'locked' data or can a lansweeper scan overwrite my data?

[MustQ]

Yes, but when using 2010 and connecting to a SQL 2012 server via an adp file, you cannot 'save' your queries/linked tables because sql server 2012 doesn't support adp.

I receive errors when doing the above.