Integrating Lansweeper with Global platform

[Sonia]

Hi Team,

I am working on a project where I have to integrate all the lansweeper instances from our global infrastructure to the central platform which is Elastic search.

So, I know we can export data out of lansweeper SQL compact DB in the form of reports in CSV type and then using any pipeline or API we can feed it to elastic search cluster.

I wanted to export all the data regarding all the assets, whom they are assigned to , what software are installed on them, version of all the software, last login or update date etc. Basically, I want full transparency of all the assets, software present across all the locations so that if I find any vulnerability in any version of any software, I can query this data to find out how many assets have this vulnerable version of software, where are they installed and whom to contact. I hope this makes sense.

Could you please help me with the SQL query or if there is any other solution like API so that I can easily query lansweeper directly or any thing else?

I apologize for any technical mistake if I have made in the description above as I am not from the database or programming background.

Looking forward for your response.

Thanks 🙂

[Sonia]

Thanks a lot Esben for your response, really appreciate 🙂

Because of organizational structure, I don't handle or manage lansweeper instances so I have to go with whatever respective teams have already in place.

Because of the complexity of lansweeper DB, I am not able to understand the internal dependencies of tables in order to construct a full SQL query to export the data out of it.

Could you please help me in framing a sql query to export all the data regarding all the assets, whom they are assigned to , what software are installed on them, version of all the software, last login or update date

The query which I am running is:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By tblAssets.IPAddress Desc

I am not sure what else I am missing. I you can help me optimize it, that will be really helpful.
Thanks again

[Esben_D]

The best thing you can do is migrate over to an SQL server installation if you want to do more complex things like custom integrations. SQL server will allow you to do much more and it's a lot easier to start making direct connections to the Lansweeper database if needed.

You can find a KB article on how to migrate here: https://www.lansweeper.com/knowledgebase/moving-your-database-from-sql-compact-to-sql-server/

An API for assets is not available at the moment, but it has been required a few times so we are aware that there is a need for one.