Update Lansweeper fields from a JSON file - using Curl and SQL

One thing that Lansweeper has (but needs a lot more of), are custom fields for an asset. These custom fields are great for integrating thingies to populate.

Messing around with API's, you'll most likely be encountering the JSON data format - so, how can we take JSON data, and update Lansweeper, using something so easy, that even I can do it?

In this example, I'll use PRTG's API, as it's pretty simple and straightforward - to update an asset with a 'Yes' in the first custom field - if it is a PRTG device (i.e. monitored).

Ingredients:

Lansweeper with MS SQL Server 2016 or greater as the database backend

• PRTG

• Curl

First, let's make a PRTG API key. In PRTG, go to Setup > API Keys

(Sorry, I don't have my usual image editing software, so I'm using snippet and the highlighter.)

Dang. I am having a hard time editing this thing - copy the Key before you save it.

In Lansweeper, title the first custom field: (Configuration > Asset Pages)

Next, use CURL to pull the data from PRTG using the API key you generated - CURL comes with windows 2019 already, but you can install it if you have an earlier Windows Server edition - I prefer using Chocolatey: (Reference https://dzone.com/articles/how-to-install-curl-on-windows). You can have error handling etc, but this is just a quick snippet -

curl "https://YOURPRTGSERVER/api/table.json?content=sensors&columns=sensor&apitoken=4CIGZTPMG3WJUWJ4PD5RRSFOCTYTL5F76LRZOTGSIQ======" -o c:\test\prtg_json_data.json

(I haven't set up HTTPS which you should have configured - just using an example in my lab)

Before:

Now, in SQL, connected to LANSWEEPERDB - you can use the JSON commands 'ISJSON' and 'OpenJson' which were introduced in SQL 2016, and pass them to an UPDATE command to update the custom field:

DECLARE @PRTGJSONFILE VARCHAR(MAX)




SELECT @PRTGJSONFILE =
BULKColumn
FROM OPENROWSET(BULK'C:\test\prtg_json_data.json', SINGLE_BLOB) JSON




 IF (ISJSON(@PRTGJSONFILE) = 1)
BEGIN




-- Example of reading structured (json) data
Select
*
,jObjId = JSON_VALUE(value,'$.objid')
,jProbe = JSON_VALUE(value,'$.probe')
,jGroup = JSON_VALUE(value,'$.group')
,jDevice = JSON_VALUE(value,'$.device')
,jHost = JSON_VALUE(value,'$.host')
From OpenJson(@PRTGJSONFILE, '$.devices')


-- Reset all Custom1 fields to NULL
Update lansweeperdb.dbo.tblassetcustom
Set custom1 = 'No'


-- Update Custom1 field for devices in PRTG JSON file
Update lansweeperdb.dbo.tblassetcustom
Set custom1 = 'Yes'
From tblAssets
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Inner Join (
Select
*
,jObjId = JSON_VALUE(value,'$.objid')
,jProbe = JSON_VALUE(value,'$.probe')
,jGroup = JSON_VALUE(value,'$.group')
,jDevice = JSON_VALUE(value,'$.device')
,jHost = JSON_VALUE(value,'$.host')
From OpenJson(@PRTGJSONFILE, '$.devices')
where JSON_VALUE(value,'$.host') <> '') tblJson On tblJson.jHost in (fqdn, IPAddress, AssetName)
END

The result: You can now see in Lansweeper if the asset is monitored in PRTG or not:

You can then report on what's monitored and what's not. Easiest way is to just save 'Assets: All columns from the Assets menu' report as a new report, and filter on custom1 field in the WHERE clause (Where Custom1 = 'Yes')

Select Top (1000000) tblAssets.AssetID
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.Domain,
  Case
    When tsysOS.osname Is Not Null Then tsysOS.osname
    When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
    When tblMacOSInfo.SystemVersion Is Not Null Then tblMacOSInfo.SystemVersion
    When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
      tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
      Coalesce(tblSccmAsset.OsCaption,
      tblSccmAsset.OperatingSystemNameandVersion)
    Else ''
  End As OS,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Mac As MACAddress,
  tblADComputers.OU,
  tblState.Statename As State,
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblAssets.Description,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  tblAssets.FQDN,
  tblAssetCustom.DNSName,
  tblAssetCustom.LastPatched,
  tblAssetCustom.LastFullbackup,
  tblAssetCustom.LastFullimage,
  tblAssetCustom.Location,
  tblAssetCustom.Building,
  tblAssetCustom.Department,
  tblAssetCustom.Branchoffice,
  tblAssetCustom.BarCode,
  tblAssetCustom.Contact,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.OrderNumber,
  tblAssetCustom.Custom1,
  tblAssetCustom.Custom2,
  tblAssetCustom.Custom3,
  tblAssetCustom.Custom4,
  tblAssetCustom.Custom5,
  tblAssetCustom.Custom6,
  tblAssetCustom.Custom7,
  tblAssetCustom.Custom8,
  tblAssetCustom.Custom9,
  tblAssetCustom.Custom10,
  tblAssetCustom.Custom11,
  tblAssetCustom.Custom12,
  tblAssetCustom.Custom13,
  tblAssetCustom.Custom14,
  tblAssetCustom.Custom15,
  tblAssetCustom.Custom16,
  tblAssetCustom.Custom17,
  tblAssetCustom.Custom18,
  tblAssetCustom.Custom19,
  tblAssetCustom.Custom20,
  Case tblAssetCustom.PreventCleanup
    When 0 Then 'No'
    When 1 Then 'Yes'
  End As PreventCleanup,
  tblAssets.Scanserver
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
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Where tblAssets.Assettype <> 66 and tblassetcustom.custom1 = 'Yes'
Order By tblAssets.AssetName

You can also make a chart report for a dashboard:

select


Status = 'Monitored by PRTG',
count(*)  as [Count]from tblassets TA
inner join tblAssetCustom TC
on TA.AssetID = TC.AssetID
where TC.custom1 = 'Yes'


UNION




select 
Status = 'Not Monitored by PRTG',
count(*) as [Count] from tblassets TA
inner join tblAssetCustom TC
on TA.AssetID = TC.AssetID
where TC.custom1 <> 'Yes' 

So, just set a scheduled task to run the CURL command, and then make a SQL job to run the SQL, and you're all set!

-Jacob