Notification

Icon
Error

Integrating Lansweeper with Global platform - How to export data from all the lansweeper instances directly to Elastic search

Posted: Tuesday, May 7, 2019 3:41:57 PM(UTC)
Sonia

Sonia

Member Original PosterPosts: 2
0
Like
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 :)
Esben.D
#1Esben.D Member Administration Posts: 1,933  
posted: 5/8/2019 1:29:56 PM(UTC)
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.c...l-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.
Sonia
#2Sonia Member Original PosterPosts: 2  
posted: 5/8/2019 3:45:26 PM(UTC)
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:

Code:
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

Active Discussions

Lansweeper Physical Disk Size
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 5:18:38 PM(UTC)
Lansweeper AD User report
by  GMcCartney  
Go to last post Go to first unread
Last post: Yesterday at 12:07:22 PM(UTC)
Lansweeper PC Build Report Missing Software
by  Andy.S   Go to last post Go to first unread
Last post: Yesterday at 10:28:40 AM(UTC)
Lansweeper Creat report ALL office (ms officce,libreoffice,openoffice)
by  RC62N  
Go to last post Go to first unread
Last post: 11/15/2019 9:58:33 PM(UTC)
Lansweeper How to Report on Dynamic Asset Groups
by  Rob-CD   Go to last post Go to first unread
Last post: 11/15/2019 11:49:22 AM(UTC)
Lansweeper Custom All Assets Report Help
by  Matt Fuhrman  
Go to last post Go to first unread
Last post: 11/14/2019 3:55:25 PM(UTC)
Report Center Calculate your network's computing power
by  GBInnovation   Go to last post Go to first unread
Last post: 11/13/2019 12:11:18 PM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  JacobH  
Go to last post Go to first unread
Last post: 11/12/2019 5:28:37 PM(UTC)