Announcement

Collapse
No announcement yet.

Issue Splitting DNSServerSearchOrder results and sending to SQL DB

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Issue Splitting DNSServerSearchOrder results and sending to SQL DB

    Hi there,

    Hopefully I can explain this well enough so that it makes sense. I'm relatively new to VBScript which will probably be quite evident in this, my first post here.

    I am trying to capture the value of DNSServerSearchOrder from servers listed in an input file. I then want to write the primary, secondary, and tertiary values out to three seperate columns in a SQL database. Where I'm having trouble is sometimes a server may correctly have all three values whereby sometimes it will only have one or two values.

    Code:
    Function ReportDnsWins(DeviceName)
     Dim objWMIService, colCards, objCard, strDNSServerSearchOrder, strDNSServers
     Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & DeviceName & "\root\cimv2")
     Set colCards = objWMIService.ExecQuery("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
     For Each objCard in colCards
      strDNSServerSearchOrder = Join(objCard.DNSServerSearchOrder, ",")
      strDNSServers = split(strDNSServerSearchOrder, ",")
      Call Write2DB(DeviceName, strDNSServers(0), strDNSServers(1), strDNSServers(2), objCard.WINSPrimaryServer, objCard.WINSSecondaryServer)
     Next
    End Function
    So this works great if the strDNSServers array ends up populated with three values from the split, however this will give an "out of range", of course, for servers with only one or two values. If it's anything over three I don't care, I only care about the first three.

    This also works great if I want to keep it simple and output the entire value of DNSServerSearchOrder to a single column in SQL thus tracking the entire list of DNS servers in the column and sorting the results of the query in Excel or something rather than using the transact SQL query itself. But this seems like it should be simple enough so I can't let it go!

    Definitely open to any insight, it would take me a bit to explain everything I've tried so far but if it helps I'll try. Hopefully someone can help me think the logic through here, I'm probably missing something simple.

    Thanks!

    X

  • #2
    Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

    Can you post up your Write2DB function -- that needs to have the third parameter optional
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

      Originally posted by Ossian View Post
      Can you post up your Write2DB function -- that needs to have the third parameter optional
      Thanks for the reply Ossian...

      Well the issue is the line calling the Write2DB function where I am sending the explicit strDNSServers(2) when (2) doesn't always exist, not the function itself as the function doesn't scrutanize the data you're sending it, it simply writes the values to the database. You could send the Write2DB function anything you want as long as the number of values matches the number it is expecting.

      For the sake of simplicity, let's replace the Write2DB call in my sample code with a Wscript.echo with the exact same values. This gives me the same result.

      X

      Comment


      • #4
        Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

        OK, how about you:

        1) get the number of splits in the string
        2) use Select...Case to call Write2DB with the correct number of parameters:
        Select numOfSplits
        Case 1 Write2Db(DeviceName, strDNSServers(0))
        Case 2 Write2Db(DeviceName, strDNSServers(0), strDNSServers(1))
        etc......

        This is pseudocode, not exact syntax
        Tom Jones
        MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
        PhD, MSc, FIAP, MIITT
        IT Trainer / Consultant
        Ossian Ltd
        Scotland

        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

          Ah, interesting idea. So perhaps something along the lines of:

          Code:
          if ubound(strDNSServers) = 1 then
             Write2DB strDNSServers(0)
          elseif ubound(strDNSServers) = 2 then
             Write2DB strDNSServers(0), strDNSServers(1)
          elseif ubound(strDNSServers) >= 3 then
             Write2DB strDNSServers(0), strDNSServers(1), strDNSServers(2) 'don't care about anything higher than tertiary
          End if
          That certainly could do the trick since the values being written to the DB will simply be "ignored" if they are empty. I was hoping for something more "efficient" but that works better than me scrapping the seperate columns for each DNS server and having to split in Excel after running the tsql query!

          Exactly the kind of creativity I was looking for, thanks much for the idea!

          X

          Comment


          • #6
            Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

            Originally posted by Ossian View Post
            OK, how about you:

            1) get the number of splits in the string
            2) use Select...Case to call Write2DB with the correct number of parameters:
            Select numOfSplits
            Case 1 Write2Db(DeviceName, strDNSServers(0))
            Case 2 Write2Db(DeviceName, strDNSServers(0), strDNSServers(1))
            etc......

            This is pseudocode, not exact syntax
            That does not work. In VBS, when calling a function (or subroutine) the number of arguments provided must match the number of parameters of this function.

            You can try this:
            Code:
            Option explicit
            
            ReportDnsWins "computer-name"
            
            
            Sub Write2DB(val1,val2,val3,val4,val5,val6)
               wscript.echo val1, val2, val3, val4, val5, val6
            End Sub
            
            Function ReportDnsWins(DeviceName)
               Dim objWMIService, colCards, objCard
               Dim arrDNSServerSearchOrder, strDNS1, strDNS2, strDNS3
               Dim strWINSPri, strWINSSec, iCnt
            
               Set objWMIService = GetObject("winmgmts:{impersonationLevel" _
                 & "=impersonate}!\\" & DeviceName & "\root\cimv2")
             
               Set colCards = objWMIService.ExecQuery("Select * From " _
                 & "Win32_NetworkAdapterConfiguration Where IPEnabled = True",,48)
            
               For Each objCard in colCards
                 strWINSPri = objCard.WINSPrimaryServer
                 If isNull(strWINSPri) Then strWINSPri = Empty
            
                 strWINSSec = objCard.WINSSecondaryServer
                 If isNull(strWINSSec) Then strWINSSec = Empty
             
                 strDNS1 = Empty : strDNS2 = Empty : strDNS3 = Empty
                 arrDNSServerSearchOrder = objCard.DNSServerSearchOrder
                 If IsArray(arrDNSServerSearchOrder) Then
                 ' # Get the first three addresses from DNSServerSearchOrder array
                   ReDim Preserve arrDNSServerSearchOrder _
                     (Ubound(arrDNSServerSearchOrder))
                   For iCnt = 0 to Ubound(arrDNSServerSearchOrder)
                     If iCnt = 0 Then strDNS1 = arrDNSServerSearchOrder(iCnt)
                     If iCnt = 1 Then strDNS2 = arrDNSServerSearchOrder(iCnt)
                     If iCnt = 2 Then strDNS3 = arrDNSServerSearchOrder(iCnt)
                   Next
                 ElseIf Not isNull(arrDNSServerSearchOrder) Then
                   strDNS1 = arrDNSServerSearchOrder
                 End If
            
                 Call Write2DB(DeviceName, strDNS1, strDNS2, strDNS3, strWINSPri, strWINSSec)
               Next
            End Function
            \Rems

            This posting is provided "AS IS" with no warranties, and confers no rights.

            __________________

            ** Remember to give credit where credit's due **
            and leave Reputation Points for meaningful posts

            Comment


            • #7
              Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

              Originally posted by Rems View Post
              That does not work. In VBS, when calling a function (or subroutine) the number of arguments provided must match the number of parameters of this function.
              This actually does work in my case (and is working great right now) because what I'm really doing is setting the additional DNS servers to "Nothing" and passing all the parameters anyhow, basically what you are doing in your example code. HOWEVER, it is certainly not as elegant as what you posted!

              This logic here:
              Code:
              ReDim Preserve arrDNSServerSearchOrder _
              (Ubound(arrDNSServerSearchOrder))
              For iCnt = 0 to Ubound(arrDNSServerSearchOrder)
              If iCnt = 0 Then strDNS1 = arrDNSServerSearchOrder(iCnt)
              If iCnt = 1 Then strDNS2 = arrDNSServerSearchOrder(iCnt)
              If iCnt = 2 Then strDNS3 = arrDNSServerSearchOrder(iCnt)
              Next
              ...is what I was struggling with. For some reason I could not think through the assignment of each variable within the for loop.

              Man this has all been great help, I wish I joined the community sooner!

              Thanks guys.

              X

              Comment

              Working...
              X