cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Query Based on DHCP Client Name

I've created a custom query to report back any devices plugged into a specific vlan that does not meet our standard naming convention.  Is there a better way to create this report?  I have about 40 VLANs that I need to report on so, it's not a huge issue to copy and paste each line and edit.  Just curious.

SELECT        IPAM_DhcpLeases.ClientIpAddress, IPAM_DhcpLeases.ClientName, IPAM_DhcpLeases.ClientMAC, IPAM_DhcpLeases.ScopeId,

CASE
WHEN IPAM_DhcpLeases.ScopeId = 47 THEN 'User VLAN'

WHEN IPAM_DhcpLeases.ScopeId = 30 THEN 'User VLAN-2'
END AS [Server]


FROM           IPAM_DhcpLeases

WHERE       
                      (IPAM_DhcpLeases.ScopeId = 47
                     AND (IPAM_DhcpLeases.ClientName NOT LIKE 'EXAMPLE%'  AND IPAM_DhcpLeases.ClientName NOT LIKE 'EXAMPLE-1%'))
OR

                      (IPAM_DhcpLeases.ScopeId = 39
                     AND IPAM_DhcpLeases.ClientName NOT LIKE 'EXAMPLE-2%')

ORDER BY IPAM_DhcpLeases.ScopeId

0 Kudos
1 Reply
Level 10

You are trying to match some text with IPAM_DhcpLeases.ClientName.If all scopes is having same text filter then you may just remove "IPAM_DhcpLeases.ScopeId = 47" and "IPAM_DhcpLeases.ScopeId = 39" to cover entire scopes.

Could you  club one or more scopes under one region.That means (IPAM_DhcpLeases.ScopeId = 47 or IPAM_DhcpLeases.ScopeId = 48) which will have same Client name filter.

Every scopes are having different client name conventions means then  way of query is good and need to build this query further to accept all scopes.

WHERE       
                      (IPAM_DhcpLeases.ScopeId = 47

                                >>>>>>>Your Cilent name filter <<<<<<<<<<
OR

                      (IPAM_DhcpLeases.ScopeId = 39

               >>>>>>>Your Cilent name filter <<<<<<<<<<

    

OR

        (IPAM_DhcpLeases.ScopeId = 40

          >>>>>>>Your Cilent name filter <<<<<<<<<<

0 Kudos