VBA script für Netzwerkverwaltung

IP-Symcon Skripte, PHP, SQL (Anleitungen)
1

Ich war es langsam satt, bei jedem neuen Gerät DNS, DHCP, etc. einzustellen. Also habe ich eine Access-Datenbank erstellt.
Ein VBA-Skript produziert folgende Dateien:

[ul]
[li].cfg files für Nagios [/li][li].cfg files für Munin, [/li][li]eine webpage mit allen anklickbaren Geräten[/li][li]DNS-Tables für Windows Server (forward und reverse). [/li][li]DHCP-Tables für Windows Server. [/li][/ul]

Ich lege den Skript hier bei. Falls jemand ans *.accdb Dokument interessiert ist, bitte PM.


Option Compare Database

Sub configLan()

Dim testSQL As String
Dim qd As DAO.QueryDef
Dim db As Database

Dim rstDynaset As Recordset
Dim rstSnapshot As Recordset
Dim rstForwardOnly As Recordset
Dim rstHostGroups As Recordset
Dim rstTableNetAssets As Recordset
Dim rstServicesPerHostgroup As Recordset
Dim rstServices As Recordset
Dim rstTextBlocks As Recordset
Dim rstLoop As Recordset
Dim prpLoop As Property

Set dbNetAssets = CurrentDb

' check if the environment exists; if not then change path
FileLocation = DropboxPath() & "Bellaria\BellariaNetwork\"

With dbNetAssets
' export a CSV file containing the hostgroup names
    Set rstTableNetAssets = .OpenRecordset("qryNetAssets", dbOpenSnapshot)
    Set rstHostGroups = .OpenRecordset("qryNagiosHostGroups", dbOpenSnapshot)
    Set rstServicesPerHostgroup = .OpenRecordset("qryServicesPerHostgroup", dbOpenSnapshot)
    Set rstServices = .OpenRecordset("tblNagiosServiceDefinitions", dbOpenSnapshot)
    Set rstTextBlocks = .OpenRecordset("tblNagiosServiceDefinitions", dbOpenSnapshot)
    Set webPageBlock = .OpenRecordset("SELECT tblTxtBlocks.txtContent FROM tblTxtBlocks WHERE (((tblTxtBlocks.txtDescription)='webPageNetAssets'));")


End With

'write cfg files
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim hostgroupsFile As Object
Dim hostsFile As Object
Dim servicesFile As Object
Dim dnsFile As Object
Dim dhcpFile As Object

Set hostgroupsFile = fso.CreateTextFile(FileLocation & "bellaria_hostgroups.cfg")
Set hostsFile = fso.CreateTextFile(FileLocation & "bellaria_hosts.cfg")
Set servicesFile = fso.CreateTextFile(FileLocation & "bellaria_services.cfg")
Set dnsFile = fso.CreateTextFile(FileLocation & "dns_table_lan.cfg")
Set dhcpFile = fso.CreateTextFile(FileLocation & "dhcp_table_lan.cfg")
Set netAssetsWebPage = fso.CreateTextFile(FileLocation & "netAssetsWebPage.html")


'write nagios configuration files
hostgroupsFile.Writeline createHostgroupCfg(rstHostGroups)
hostgroupsFile.Writeline createTemplatesCfg(rstHostGroups)

hostsFile.Writeline createHostsCfg(rstTableNetAssets)
hostsFile.Writeline generic_host()

servicesFile.Writeline createServicesCfg(rstServices)
servicesFile.Writeline generic_service()
servicesFile.Writeline generic_contact()

netAssetsWebPage.Writeline webPageBlock!txtContent & MakeNetAssetsWebPage(rstTableNetAssets)

'write dns and dhcp configuration files

'dhcpFile.Writeline createDhcpTable(rstTableNetAssets)
dnsFile.Writeline createDnsTable(rstTableNetAssets)
dhcpFile.Writeline createDhcpTable(rstTableNetAssets)

hostgroupsFile.Close
Set fso = Nothing
Set hostgroupsFile = Nothing
Set hostsFile = Nothing
Set servicesFile = Nothing

' run powershell command to assign dhcp and dns entries
 finish = runPowershell()


End Sub
Function DropboxPath()

'set file location to Dropbox
'first find the info.json file
dropboxEnv1 = Environ("AppData") & "\Dropbox\info.json"
dropboxEnv2 = Environ("LocalAppData") & "\Dropbox\info.json"
If Dir(dropboxEnv1) <> "" Then
    Debug.Print "5" & Dir(dropboxEnv1)
    dropboxJson = readLine(dropboxEnv1)
Else
    Debug.Print "6: " & Dir(dropboxEnv2)
    If Dir(dropboxEnv2) <> "" Then
            Debug.Print "7" & dropboxEnv2
           dropboxJson = readLine(dropboxEnv2)
    Else
        Debug.Print ("no dropbox")
        Exit Function
    End If
End If

' then extract the path
pathPosition = InStr(dropboxJson, Chr(34) & "path" & Chr(34) & ":")
dBoxPathTemp = Mid(dropboxJson, pathPosition + 9)
dBoxPath = Left(dBoxPathTemp, InStr(dBoxPathTemp, ", ") - 2)
dBoxPath = Replace(dBoxPath, "\\", "\") & "\"
Debug.Print "dBoxPath*" & dBoxPath & "*"
DropboxPath = dBoxPath


End Function

Function readLine(fileInputName)
Dim FileNum As Integer
Dim DataLine As String
Debug.Print fileInputName

FileNum = FreeFile()
Open fileInputName For Input As #FileNum

While Not EOF(FileNum)
    Line Input #FileNum, DataLine ' read in data 1 line at a time
    readLine = readLine & DataLine
    ' decide what to do with dataline,
    ' depending on what processing you need to do for each case
Wend

End Function
Function createHostgroupCfg(rstHostGroups As Recordset)

' prepare hostgroup and templates table


'Check to see if the recordset actually contains rows
If Not (rstHostGroups.EOF And rstHostGroups.BOF) Then
    rstHostGroups.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rstHostGroups.EOF = True
       
        vHostgroupDef = "define hostgroup {" & vbCrLf & _
                    vbTab & "hostgroup_name " & rstHostGroups!NagiosHostGroups & vbCrLf & _
                    vbTab & "# alias " & vbCrLf & _
                    vbTab & "}" & vbCrLf & vbCrLf
        
        vHostgroupDefs = vHostgroupDefs & vHostgroupDef

        'Move to the next record. Don't ever forget to do this.
        rstHostGroups.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

'MsgBox "Finished looping through records."
' Debug.Print vHostgroupDefs
createHostgroupCfg = vHostgroupDefs
End Function
Function createTemplatesCfg(rstHostGroups As Recordset)

' templates are for each hostgroup (same)


'Check to see if the recordset actually contains rows
If Not (rstHostGroups.EOF And rstHostGroups.BOF) Then
    rstHostGroups.MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until rstHostGroups.EOF = True
       
        vHostgroupDef = "define host { ; THIS IS A TEMPLATE" & vbCrLf & _
                    vbTab & "name " & rstHostGroups!NagiosHostGroups & "_template" & vbCrLf & _
                    vbTab & "use generic-host" & vbCrLf & _
                    vbTab & "register 0 ; DO NOT REGISTER BECAUSE THIS IS JUST A TEMPLATE" & vbCrLf & _
                    vbTab & "}" & vbCrLf & vbCrLf
        
        vHostgroupDefs = vHostgroupDefs & vHostgroupDef

        'Move to the next record. Don't ever forget to do this.
        rstHostGroups.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

'MsgBox "Finished looping through records."
' Debug.Print vHostgroupDefs
createTemplatesCfg = vHostgroupDefs
End Function

Function createHostsCfg(rstTableNetAssets As Recordset)
' prepare host table

vHostsCounter = 0
'Check to see if the recordset actually contains rows
With rstTableNetAssets
If Not (.EOF And .BOF) Then
    .MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until .EOF = True
        If (Trim(!HostName) = "" Or IsNull(!HostName)) Then
            Debug.Print "hostname is null!"
            Debug.Print vHostDefs
            Exit Do
        End If
        vHostsCounter = vHostsCounter + 1
        
        vHostDef = "# record " & vHostsCounter & vbCrLf & _
                    "define host {" & vbCrLf & _
                    vbTab & "host_name " & !HostName & vbCrLf & _
                    vbTab & "address " & !ip_address & vbCrLf & _
                    vbTab & "use " & !NagiosHostGroups & "_template" & vbCrLf & _
                    vbTab & "hostgroups " & !NagiosHostGroups & vbCrLf & _
                    vbTab & "notifications_enabled " & IIf(!nagios_alert, 1, 0) & vbCrLf & _
                    vbTab & "check_command check_ping!100.0,20%!500.0,60%" & vbCrLf & _
                    vbTab & "}" & vbCrLf & vbCrLf
        vHostDefs = vHostDefs & vHostDef
        'Move to the next record. Don't ever forget to do this.
        rstTableNetAssets.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If

'MsgBox "Finished looping through records."

createHostsCfg = vHostDefs

' write text file with all hosts

End With
'Set rstTableNetAssets = Nothing 'Clean up
      'rstTableNetAssets.Close
      'rstTableNetAssets.Close


End Function

Function createServicesCfg(rstServices As Recordset)
 
rstSql1 = "SELECT tblServicesPerHostgroup.HostGroupName, tblServicesPerHostgroup.IdService " & _
"FROM qryNagiosHostgroups INNER JOIN (tblServicesPerHostgroup LEFT JOIN tblNagiosServiceDefinitions " & _
"ON tblServicesPerHostgroup.IdService = tblNagiosServiceDefinitions.ID) " & _
"ON qryNagiosHostgroups.NagiosHostGroups = tblServicesPerHostgroup.HostGroupName " & _
"WHERE (((tblServicesPerHostgroup.IdService)= "

rstSql2 = " ));"

vServicesCounter = 0
With rstServices
'Check to see if the recordset actually contains rows
If Not (.EOF And .BOF) Then
    .MoveFirst 'Unnecessary in this case, but still a good habit
    Do Until .EOF = True
        vServicesCounter = vServicesCounter + 1
        
        ' find all applicable hosts for this service
        rstSql = rstSql1 & !ID & rstSql2
        'Debug.Print rstSql
        Set rstHostgroupsPerService = CurrentDb.OpenRecordset(rstSql, dbOpenSnapshot)
            'Check to see if the recordset actually contains rows
            With rstHostgroupsPerService
            If Not (.EOF And .BOF) Then
                .MoveFirst 'Unnecessary in this case, but still a good habit
                hostgroupsCollection = ""
                Do Until .EOF = True
                    hostgroupsCollection = hostgroupsCollection & ", " & !HostGroupName
                    .MoveNext
                Loop
            hostgroupsCollection = Mid(hostgroupsCollection, 2)
            End If
            End With
                    
            Debug.Print vServicesCounter, hostgroupsCollection
            
            'if the service is not in use by any hostgroup, put together service definition
                If (Len(hostgroupsCollection) > 3) Then

                vServicesDef = "# record " & vServicesCounter & vbCrLf & _
                    "# " & !nagios_description & vbCrLf & _
                    "define service {" & vbCrLf & _
                    vbTab & "hostgroup_name " & hostgroupsCollection & vbCrLf & _
                    vbTab & "check_command " & !check_command & vbCrLf & _
                    vbTab & "service_description   " & !service_description & vbCrLf & _
                    vbTab & "use " & !nagios_use & vbCrLf & _
                    vbTab & "notification_options " & !notification_options & vbCrLf & _
                    vbTab & "notification_interval " & !notification_interval & vbCrLf & _
                    vbTab & "}" & vbCrLf & vbCrLf
                End If

' vbTab & "host_name localhost" & vbCrLf &
        
        vServicesDefs = vServicesDefs & vServicesDef
        'Move to the next record. Don't ever forget to do this.
        .MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If


'MsgBox "Finished looping through records."
createServicesCfg = vServicesDefs

.Close 'Close the recordset
End With
Set rstServicesPerHostgroup = Nothing 'Clean up
  
End Function
'************************************************

Function ExistsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
    On Error Resume Next
    ExistsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function

Function generic_host()

generic_host = vbCrLf & _
"# Generic host definition template - This is NOT a real host, just a template! " & vbCrLf & _
"define host{     " & vbCrLf & _
        "name                            generic-host    ; The name of this host template  " & vbCrLf & _
        "notifications_enabled           1           ; Host notifications are enabled   " & vbCrLf & _
        "event_handler_enabled           1           ; Host event handler is enabled " & vbCrLf & _
        "flap_detection_enabled          1           ; Flap detection is enabled " & vbCrLf & _
        "process_perf_data               1           ; Process performance data " & vbCrLf & _
        "retain_status_information       1           ; Retain status information across program restarts   " & vbCrLf & _
        "retain_nonstatus_information    1           ; Retain non-status information across program restarts  " & vbCrLf & _
        "check_command                   check-host-alive        ; Default command to check if routers are alive   " & vbCrLf & _
        "max_check_attempts              10          ;" & vbCrLf & _
        "check_period                    24x7        ; By default, switches are monitored round the clock  " & vbCrLf & _
        "check_interval                  5       ; Switches are checked every 5 minutes  " & vbCrLf & _
        "retry_interval                  1       ; Schedule host check retries at 1 minute intervals  " & vbCrLf & _
        "notification_interval 1440   " & vbCrLf & _
        "notification_period             24x7        ; Send host notifications at any time   " & vbCrLf & _
        "register                        0           ; DONT REGISTER THIS DEFINITION - ITS NOT A REAL HOST, JUST A TEMPLATE!   " & vbCrLf & _
        "contact_groups      admins      ; Notifications get sent to the admins by default   " & vbCrLf & _
        "} " & vbCrLf & vbCrLf
        
End Function
Function generic_service()

generic_service = vbCrLf & _
"# Generic service definition template - This is NOT a real service, just a template!" & vbCrLf & _
"define service{" & vbCrLf & _
"        name                            generic-service     ; The 'name' of this service template    " & vbCrLf & _
"        active_checks_enabled           1               ; Active service checks are enabled    " & vbCrLf & _
"        passive_checks_enabled          1               ; Passive service checks are enabled/accepted    " & vbCrLf & _
"        parallelize_check               1               ; Active service checks should be parallelized (disabling this can lead to major performance problems)    " & vbCrLf & _
"        obsess_over_service             1               ; We should obsess over this service (if necessary)    " & vbCrLf & _
"        check_freshness                 0               ; Default is to NOT check service 'freshness'    " & vbCrLf & _
"        notifications_enabled           1               ; Service notifications are enabled    " & vbCrLf & _
"        event_handler_enabled           1               ; Service event handler is enabled    " & vbCrLf & _
"        flap_detection_enabled          1               ; Flap detection is enabled    " & vbCrLf & _
"        process_perf_data               1               ; Process performance data    " & vbCrLf & _
"        retain_status_information       1               ; Retain status information across program restarts    " & vbCrLf & _
"        retain_nonstatus_information    1               ; Retain non-status information across program restarts    " & vbCrLf & _
"        is_volatile                     0               ; The service is not volatile    " & vbCrLf & _
"        check_period                    24x7            ; The service can be checked at any time of the day    " & vbCrLf & _
"        max_check_attempts              3           ; Re-check the service up to 3 times in order to determine its final (hard) state    " & vbCrLf & _
"        normal_check_interval           10          ; Check the service every 10 minutes under normal conditions    " & vbCrLf & _
"        retry_check_interval            2           ; Re-check the service every two minutes until a hard state can be determined    " & vbCrLf & _
"        contact_groups                  admins          ; Notifications get sent out to everyone in the 'admins' group    " & vbCrLf & _
"        notification_options        u,c         ; Send notifications about warning, unknown, critical, and recovery events    " & vbCrLf & _
"        notification_interval   1440            ; Re-notify about service problems every hour    " & vbCrLf & _
"        notification_period             24x7            ; Notifications can be sent out at any time    " & vbCrLf & _
"        register                        0              ; DONT REGISTER THIS DEFINITION - ITS NOT A REAL SERVICE, JUST A TEMPLATE!    " & vbCrLf & "} " & vbCrLf & vbCrLf
        
End Function

Function generic_contact()
generic_contact = "define contact{    " & vbCrLf & _
"        name                            generic-contact     ; The name of this contact template    " & vbCrLf & _
"        service_notification_period     24x7            ; service notifications can be sent anytime    " & vbCrLf & _
"        host_notification_period        24x7            ; host notifications can be sent anytime    " & vbCrLf & _
"        service_notification_options    w,u,c,r,f,s     ; send notifications for all service states, flapping events, and scheduled downtime events    " & vbCrLf & _
"        host_notification_options       d,u     ; send notifications for all host states, flapping events, and scheduled downtime events    " & vbCrLf & _
"        service_notification_commands   notify-service-by-email ; send service notifications via email    " & vbCrLf & _
"        host_notification_commands      notify-host-by-email    ; send host notifications via email    " & vbCrLf & _
"        register                        0               ; DONT REGISTER THIS DEFINITION - ITS NOT A REAL CONTACT, JUST A TEMPLATE!    " & vbCrLf & _
"        }"
        


End Function
Function createDnsTable(rstTableNetAssets As Recordset)
' prepare host table
vHostsCounter = 0
'Check to see if the recordset actually contains rows
With rstTableNetAssets
If Not (.EOF And .BOF) Then
    .MoveFirst 'Unnecessary in this case, but still a good habit
    vHostDefs = "record_no," & String(4, " ") & "operation, host_name," & String(21, " ") & "ip_address " & vbCrLf
            
    Do Until .EOF = True
        If (Trim(!HostName) = "" Or IsNull(!HostName)) Then
            Debug.Print "hostname is null!"
            Debug.Print vHostDefs
            Exit Do
        End If
        
        vHostsCounter = vHostsCounter + 1
        vHostDef = "# record " & vHostsCounter & "," & String(3 - Len(vHostsCounter), " ") & _
                    " add_dns,   " & _
                    !HostName & "," & String(30 - Len(!HostName), " ") & _
                    !ip_address & _
                    vbCrLf
        vHostDefs = vHostDefs & vHostDef
        
        'check if additional names are present for this host, and add respective dns entries
            If Len(!DnsNames) > 1 Then
            splitDns = Split(!DnsNames, ",")
            For lngPosition = LBound(splitDns) To UBound(splitDns)
                addDnsEntry = Trim(splitDns(lngPosition))
                vAddDns = "# record " & vHostsCounter & "." & lngPosition & "," & _
                    " add_dns,   " & _
                     addDnsEntry & "," & String(30 - Len(addDnsEntry), " ") & _
                    !ip_address & _
                    vbCrLf
                vHostDefs = vHostDefs & vAddDns
            Next lngPosition
            End If
        'Move to the next record. Don't ever forget to do this.
        rstTableNetAssets.MoveNext
    Loop
End If
createDnsTable = vHostDefs

End With
End Function
Function createDhcpTable(rstTableNetAssets As Recordset)
' prepare dhcp table
vHostsCounter = 0
'Check to see if the recordset actually contains rows
With rstTableNetAssets
If Not (.EOF And .BOF) Then
    .MoveFirst 'Unnecessary in this case, but still a good habit
    vHostDefs = "record_no," & String(4, " ") & "operation," & String(13, " ") & "host_name," & String(21, " ") _
    & "ip_address," & String(8, " ") & "mac_address" & vbCrLf
            
    Do Until .EOF = True
        If (Trim(!HostName) = "" Or IsNull(!HostName)) Then
            Debug.Print "hostname is null!"
            Debug.Print vHostDefs
            Exit Do
        End If
        
        vHostsCounter = vHostsCounter + 1
        vHostDef = "# record " & vHostsCounter & "," & String(3 - Len(vHostsCounter), " ") & _
                    " add_dhcp_reservation,  " & _
                    !HostName & "," & String(30 - Len(!HostName), " ") & _
                    !ip_address & "," & String(15 - Len(!ip_address), " ") & _
                    !mac_address & _
                    vbCrLf
        vHostDefs = vHostDefs & vHostDef
        
        
        rstTableNetAssets.MoveNext
    Loop
End If
createDhcpTable = vHostDefs
Debug.Print createDhcpTable

End With
End Function

Function runPowershell()

Dim retval As Variant

powershellCommand = "\\xyz\\C_xy\Users\aag\Dropbox\Bellaria\BellariaNetwork\refresh_lan.ps1"

storeCredentials = "  " & _
"$Username = 'xy'" & _
"$Password = 'xy' " & _
"$pass = ConvertTo-SecureString -AsPlainText $Password -Force " & _
"$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass" & _
"Invoke-Command -ComputerName wskeller -ScriptBlock { invoke-expression -Command \\WSKELLER\\C_wskeller\Users\aag\Dropbox\Bellaria\BellariaNetworkxxx.ps1 } -credential $Cred"

retval = Shell("PowerShell ""C:\MyScript.ps1""", vbNormalFocus)

End Function

Function MakeNetAssetsWebPage(rstTableNetAssets)
' prepare web page table
vHostsCounter = 0
'Check to see if the recordset actually contains rows
With rstTableNetAssets
If Not (.EOF And .BOF) Then
    .MoveFirst 'Unnecessary in this case, but still a good habit
    vWebPage = "record_no," & String(4, " ") & "operation," & String(13, " ") & "host_name," & String(21, " ") _
    & "ip_address," & String(8, " ") & "mac_address" & vbCrLf
            
    Do Until .EOF = True
        If (Trim(!HostName) = "" Or IsNull(!HostName)) Then
            Debug.Print "hostname is null!"
            Debug.Print vHostDefs
            Exit Do
        End If
        
        vHostsCounter = vHostsCounter + 1
        vWebPageDef = "<tr><td width='25'> <a href='http://" & !HostName & ".lan' >" & !HostName & " </a></td>" & vbCrLf & _
        "<td width='71'><a href='http://ip_address' title='host'>" & !ip_address & " </td>" & vbCrLf & _
        "<td width='94'>" & !mac_address & " </td>" & vbCrLf & _
        "<td width='254'>" & !Description & " </td>" & vbCrLf
     
        vWebPage = vWebPage & vWebPageDef
        
        
        rstTableNetAssets.MoveNext
    Loop
End If
MakeNetAssetsWebPage = vWebPage


End With
End Function
2

dazu kommt noch ein Powershell-Script, der die CSV-Dateien abholt und die entsprechenden DHCP und DNS-Einträge macht.


Import-module "c:\admin\Microsoft.DHCP.PowerShell.Admin.psm1"
#delete pre-existing DNS records

		$ReverseLookupZone="10.10.in-addr.arpa"
		$ForwardLookupZone="lan"
		$DNSServer="WSKELLER"

		#remove PTR records
		$records=Get-DnsServerResourceRecord -ZoneName $ReverseLookupZone -ComputerName $DNSServer | Where-Object {$_.RecordType -eq "PTR" }
		Foreach ($record in $records)
		{Remove-DnsServerResourceRecord -ZoneName $ReverseLookupZone -ComputerName $DNSServer -Force -RRType "Ptr" -Name $record.HostName}
		#remove A records
		$records=Get-DnsServerResourceRecord -ZoneName $ForwardLookupZone -ComputerName $DNSServer | Where-Object {$_.RecordType -eq "A" }
		Foreach ($record in $records)
		{Remove-DnsServerResourceRecord -ZoneName $ForwardLookupZone -ComputerName $DNSServer -Force -RRType "A" -Name $record.HostName}


#delete pre-existing DHCP records

Get-DHCPServerV4Scope | ForEach {
    #Get-DHCPServerv4Lease -ScopeID $_.ScopeID | where {$_.AddressState -like '*Reservation'}
    Get-DHCPServerv4Lease -ScopeID "10.10.0.0" -AllLeases
} | Select-Object ScopeId,IPAddress,HostName,ClientID,AddressState | Export-Csv ".\$($env:COMPUTERNAME)-Reservations.csv" -NoTypeInformation



$DnsDefinitionsFile = "C:\admin\dns_table_lan.cfg"
$DhcpDefinitionsFile = "C:\admin\dhcp_table_lan.cfg"
$ScopeName = 'lan'
$dnsServer = "WSKELLER" # Your DNS Server Name
$dhcpServer = "WSKELLER" # Your DHCP Server Name
$ReverseZone = "10.10.in-addr.arpa" # Your ReverseLookup Zone 

 
$DhcpScope = Get-DhcpServerv4Scope | where-object{ $_.name.tolower() -eq $ScopeName.ToLower() } 


function AddDhcpReservation ($row)
	{$m = ($row.'mac_address').replace( ":", "-")
	Add-DhcpServerv4Reservation -ScopeId $DhcpScope.ScopeId -Description "no description" -IPAddress $row.'ip_address' -Name $row.'host_name' -ClientId $m -Type Dhcp 
	}

function AddDnsEntries ($row)	
	{
	$IpArray = $row.'ip_address'.Split("{.}")
	Write-Host $IpArray
	$IpReverseSubaddress = $IpArray[3] + "." + $IpArray[2]
	dnscmd $dnsServer /RecordAdd lan $row.'host_name' A $row.'ip_address'
	dnscmd $dnsServer /RecordAdd $ReverseZone $IpReverseSubaddress PTR $row.'host_name'
	}






$ValidMAC_address = "^([0-9A-Fa-f]{2}[:-]){5}([0-9A-Fa-f]{2})$"
$ValidIpAddress = "^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$"


#############################################################################################################
# process the file for DNS entries
#############################################################################################################

$dnsHeader = "record_no", "operation", "host_name", "ip_address"
Import-Csv $DnsDefinitionsFile -Header $dnsHeader  | Format-Table
$resListCsv = Import-Csv $DnsDefinitionsFile -Header $dnsHeader


$counter = 0 
foreach( $row in $resListCsv ) 
	{ 
 	$counter = $counter + 1
	$row.'operation' = $row.'operation'.Trim()
	$row.'host_name'= $row.'host_name'.Trim()
	$row.'ip_address' = $row.'ip_address'.trim() 
	Write-Host " "

	# loop if begin of line is not "add_dns". All instructions must start with "add"
	if ( $row.'operation' -ne "add_dns") {
		Write-Host $row.'operation' " = Not add_dns"
		continue } 
	
	#Write-Host "line: " $counter  " operation *" $row.'operation' -foregroundcolor green 
	#if "add group" specify group descriptor, then loop

	#if no IP address is found, loop and do not waste any more time
	
	if ( $row.'ip_address' -notmatch $ValidIpAddress ) 
		{Write-Host "IP_address misformed at line " $counter $counter -foregroundcolor blue -backgroundcolor green
		 Write-Host $row.'ip_address' -foregroundcolor blue -backgroundcolor green
		 Write-Host  $row -foregroundcolor blue -backgroundcolor green
		 continue}

	AddDnsEntries $row
	}

	
#############################################################################################################
# process the file for DHCP entries
#############################################################################################################
    
$dhcpHeader = "record_no","operation","host_name","ip_address","mac_address"
Import-Csv $DhcpDefinitionsFile -Header $dhcpHeader  | Format-Table
$resListCsv = Import-Csv $DhcpDefinitionsFile -Header $dhcpHeader

$counter = 0 
foreach( $row in $resListCsv ) 
	{ 
 	$counter = $counter + 1
	$row.'operation' = $row.'operation'.Trim()

	# loop if begin of line is not "add_dhcp_reservation". All instructions 	must start with "add"
	if ( $row.'operation' -ne "add_dhcp_reservation") {
		Write-Host $row.'operation' " = Not add_dhcp_reservation"
		continue } 
	$row.'host_name'= $row.'host_name'.Trim()
	$row.'mac_address'= $row.'mac_address'.Trim()
	$row.'ip_address' = $row.'ip_address'.trim() 
	Write-Host " "
	
	#Write-Host "line: " $counter  " operation *" $row.'operation' -foregroundcolor green 
	#if "add group" specify group descriptor, then loop

	#if no IP address is found, loop and do not waste any more time
	
	if ( $row.'ip_address' -notmatch $ValidIpAddress ) 
		{Write-Host "IP_address misformed at line " $counter $counter -foregroundcolor blue -backgroundcolor green
		 Write-Host $row.'ip_address' -foregroundcolor blue -backgroundcolor green
		 Write-Host  $row -foregroundcolor blue -backgroundcolor green
		 continue}
	
	if ($row.'mac_address' -notmatch $ValidMAC_address)
		{Write-Host "MAC_address misformed at line " $counter -foregroundcolor yellow -backgroundcolor green
		 Write-Host  $row -foregroundcolor yellow -backgroundcolor green
		 continue}
		 Write-Host "!!!"
		 Write-Host  "line " $counter " *$($row.'mac_address')*" " *$($row.'ip_address')*" " *$($row.'host_name')*" 	
		 
		 
	AddDhcpReservation $row
	}

restart-service dns
restart-service dhcpserver