Issue Splitting DNSServerSearchOrder results and sending to SQL DB

Home Forums Scripting Windows Script Host Issue Splitting DNSServerSearchOrder results and sending to SQL DB

This topic contains 6 replies, has 4 voices, and was last updated by Avatar evosti 8 years, 9 months ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • Avatar
    XIIxOveR
    Member
    #152155

    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.

    Function ReportDnsWins(DeviceName)
    Dim objWMIService, colCards, objCard, strDNSServerSearchOrder, strDNSServers
    Set objWMIService = GetObject(“winmgmts:” & “{impersonationLevel=impersonate}!\” & DeviceName & “rootcimv2”)
    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
    [/CODE]

    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[CODE]
    Function ReportDnsWins(DeviceName)
    Dim objWMIService, colCards, objCard, strDNSServerSearchOrder, strDNSServers
    Set objWMIService = GetObject(“winmgmts:” & “{impersonationLevel=impersonate}!\” & DeviceName & “rootcimv2”)
    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
    [/CODE]

    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

    Avatar
    Ossian
    Moderator
    #183240

    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

    Avatar
    evosti
    Member
    #379337

    Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

    Ossian;224189 wrote:
    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

    Avatar
    Ossian
    Moderator
    #183242

    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

    Avatar
    evosti
    Member
    #379338

    Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

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

    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[/CODE]

    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[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[/CODE]

    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

    Rems
    Rems
    Moderator
    #227897

    Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

    Ossian;224209 wrote:
    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 “[I][COLOR=”Blue”]computer-name[/COLOR][/I]”

    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 & “rootcimv2”)

    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

    Avatar
    evosti
    Member
    #379339

    Re: Issue Splitting DNSServerSearchOrder results and sending to SQL DB

    Rems;224233 wrote:
    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:

    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[/CODE]

    …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[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[/CODE]

    …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

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.

Register for this Petri Webinar!

Want to Make Your Backup Storage Unlimited & Ready for the Cloud? – Free Thurrott Premium Account with Webinar Registration!

Tuesday, August 27, 2019 @ 1:00 pm EDT

A Scale-Out Backup storage infrastructure is a must-have technology for your backups. In this webinar, join expert Rick Vanover for a look on what real-world problems are solved by the Scale-Out Backup Repository.

Register Now

Sponsored By