How to get assets lists based on each DHCP server USED by dhcp clients?

[lunesolitaire]

Hello,

I need to identify the illegitimate DHCP server by finding out the DHCP addresses that were actually being used.

I've managed to create a report listing all the DHCP servers used by Windows DHCP clients (plz look at the screenshot), as well as the number of assets associated with each DHCP IP address. However, I'm having trouble creating a "linkable" DHCP IP address to display all assets using the same DHCP server. 

How can I make each DHCP server address "linkable" in my report so that when I click on each DHCP server IP, it displays a list of assets using that IP address?

Do you have any ideas?

Thanks in advance!

Select Top 1000000 Coalesce(tblNetwork.DHCPserver, N'') As DHCPServerOnClient,
Count(tblNetwork.AssetID) As TotalNbr
From tblassets
Right Join tblNetwork On tblassets.AssetID = tblNetwork.AssetID
Where tblNetwork.DHCPenabled = 'True'
Group By Coalesce(tblNetwork.DHCPserver, N'')
Order By TotalNbr Desc

[lunesolitaire]

Unfortunately.....

Thank you anyway

[Mister_Nobody · Accepted solution]

I have generated report for you via AI code generator.
Run report and use filter to show needed DHCPs and assets from TOTAL part.
Empty DHCPs are ignored.

With AssetDhcpDetails As (Select tblAssets.AssetID,
      tblAssets.AssetName,
      tblNetwork.DHCPserver As DHCPServerOnClient,
      tblNetwork.IPAddress,
      tblNetwork.MACaddress
    From tblAssets
      Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
    Where tblNetwork.DHCPserver Is Not Null And tblNetwork.DHCPserver != N'' And
      tblNetwork.DHCPenabled = N'True'),
  DhcpSummary As (Select AssetDhcpDetails.DHCPServerOnClient,
      COUNT(*) As TotalNbr
    From AssetDhcpDetails
    Group By AssetDhcpDetails.DHCPServerOnClient)
Select Top 1000000 CombinedResults.AssetID,
  CombinedResults.AssetName,
  CombinedResults.DHCP_Network_Client_IP,
  CombinedResults.MAC_Address,
  CombinedResults.[Row Type],
  CombinedResults.hyperlink_name_DHCPServer,
  CombinedResults.hyperlink_DHCPServer,
  CombinedResults.TotalNbr,
  CombinedResults.backgroundcolor
From (Select '' As AssetID,
      '' As AssetName,
      '' As DHCP_Network_Client_IP,
      '' As MAC_Address,
      N'📊 TOTAL' As [Row Type],
      s.DHCPServerOnClient As hyperlink_name_DHCPServer,
      N'/quicksearch.aspx?q=' + s.DHCPServerOnClient As hyperlink_DHCPServer,
      s.TotalNbr,
      N'LightYellow' As backgroundcolor
    From DhcpSummary s
    Union All
    Select d.AssetID,
      d.AssetName,
      d.IPAddress As DHCP_Network_Client_IP,
      d.MACaddress As MAC_Address,
      N'🖥️ DETAIL' As [Row Type],
      d.DHCPServerOnClient As hyperlink_name_DHCPServer,
      N'/quicksearch.aspx?q=' + d.DHCPServerOnClient As hyperlink_DHCPServer,
      s.TotalNbr,
      N'LightGreen' As backgroundcolor
    From AssetDhcpDetails d
      Inner Join DhcpSummary s On d.DHCPServerOnClient = s.DHCPServerOnClient)
  As CombinedResults
Order By CombinedResults.[Row Type] Desc,
  CombinedResults.TotalNbr Desc,
  CombinedResults.AssetName

[lunesolitaire]

Hi Mister_Nodbody,

Great! Thanks for your help. I just ran into a problem when I "Save & Run" the code; it said "Error: Incorrect syntax near ')'."

So, when I viewed the code again, I saw the following message:

But the first time I pasted this code into the editor, there were no errors.

I'm checking it now... we'll see... do you have any idea what the error might be?

Thanks!

[Mister_Nobody]

What is your sql server and lansweeper versions?

I use full ms sql server 2019 as lansweeper database and lansweeper 12.6.

[lunesolitaire]

Hi Mister_Nobody,

Your code works with Lansweeper 12.6. Thank you so much!

There's just one small problem: the code provides a summary list of DHCP addresses and then a complete list of each asset, which is perfect. However, when I click on a DHCP address, for example, dhcp_ip_1, it doesn't display a precise list of the assets using that IP.

But since it then provides a complete list of assets with DHCP sorted, it's not a major issue.

dhcp_ip_1    5000

dhcp_ip_2    3800

dhcp_ip_3    1000

dhcp_ip_4     55

.....

[Mister_Nobody]

Don't click!

You must copy and paste into filter field!

[lunesolitaire]

Lansweeper: 12.1.1.0

MS SQL Server 2022 64 bits

So, it's my Lansweeper that's too old; I'll schedule an update and test it with this code.

Thanks a lot!

[DavidPK]

Lansweeper’s reporting is designed to query and present the discovered asset data but the only built‑in clickable links in reports are things like:

• Links to individual assets 
• Links to user pages 

There is no native way today to make an arbitrary field value (like “DHCP server IP”) act as a dynamic link that opens another report pre‑filtered to that value.