show report results in a custom field

[luigirusso]

Hi everyone,

I hope someone can help me.

I use lansweeper v. 12.2.0.4 on prem.

I created a query that shows in a report the Supremo ID of each computer scanned.

Now I need to show the Supremo ID in a custom field of the computer dashboard, I understood that the custom field, for example custom 1, is in "TableassetCustom.Custom1".

this is the query

Select Top 1000000 tblassets.AssetName,
Registry.RegKey,
Registry.ValueName,
Case
When Len(Registry.Value) = 9 Then Cast(Registry.Value As varchar(9))
When Len(Registry.Value) = 8 Then '0' + Cast(Registry.Value As varchar(8))
When Len(Registry.Value) = 7 Then '00' + Cast(Registry.Value As varchar(7))
When Len(Registry.Value) = 6 Then '000' + Cast(Registry.Value As varchar(6))
Else Registry.Value
End As ClientID,
tblassets.Username
From tblassets
Left Join tblRegistry As Registry On tblassets.AssetID = Registry.AssetID
Inner Join tblAssetCustom On tblassets.AssetID = tblAssetCustom.AssetID
Where Registry.RegKey Like '%Supremo%' And Registry.ValueName Like '%ClientID%'

I think, but I'm not sure, I could do the update of the "TblAssetCustom.custom1" in my query...

Thank you.

[FrankSc]

Hello luigirusso,
If you create a new report, the default query that is shown gives you the correct join to tblassetcustom. 
You can use the same join and then select Custom1 in the field list as shown in the screenshot.

 

[luigirusso]

Hello FrankSc,

thank you for the answer!So, do I need to make a new query or can I update TblAssetCustom with a query like this?

UPDATE tblAssetCustom
SET custom1 = 
    CASE
        WHEN LEN(Registry.Value) = 9 THEN CAST(Registry.Value AS VARCHAR(9))
        WHEN LEN(Registry.Value) = 8 THEN '0' + CAST(Registry.Value AS VARCHAR(8))
        WHEN LEN(Registry.Value) = 7 THEN '00' + CAST(Registry.Value AS VARCHAR(7))
        WHEN LEN(Registry.Value) = 6 THEN '000' + CAST(Registry.Value AS VARCHAR(6))
        ELSE Registry.Value
    END
FROM tblAssetCustom
INNER JOIN tblAssets ON tblAssets.AssetID = tblAssetCustom.AssetID
LEFT JOIN tblRegistry AS Registry ON tblAssets.AssetID = Registry.AssetID
WHERE Registry.RegKey LIKE '%Supremo%' 
  AND Registry.ValueName LIKE '%ClientID%'

[Katgroup]

We do not support running custom database queries like this since it could easily corrupt the database in non-obvious ways that may not be visible until a future upgrade.

If you choose to proceed, we strongly recommend you have a backup, and are aware that it is "at your own risk".