1 Reply Latest reply on Jul 13, 2015 8:16 AM by muralikvp

    Custom Query Based on DHCP Client Name

    lori.mcgarity@styrolution.com

      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

        • Re: Custom Query Based on DHCP Client Name
          muralikvp

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