Scripts/Add-WOLComputerToDatabase.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132

Function Add-WOLComputerToDatabase {

    <#
 
     .SYNOPSIS
     Add a computer to the Wake On LAN database
 
     .DESCRIPTION
     Add a computer to the Wake On LAN database
 
     .PARAMETER ComputerName
     The name of the computer to add to the database
 
     .PARAMETER MACAddress
     The computer's physical address
 
     .EXAMPLE
     Add-WOLComputerToDatabase -ComputerName LabPC2064 -MACAddress AA:11:22:33:44:FF
 
     .NOTES
     The computer names and MAC addresses must be unique
 
     .LINK
     N/A
 
    #>


    [CmdletBinding ()]

    Param (

        [Parameter (Mandatory = $True,
                    ValueFromPipeline = $True,
                    ValueFromPipelineByPropertyName = $True,
                    HelpMessage = 'Enter computer name'
                   )
        ]

        [String[]]$ComputerName,

        [Parameter (Mandatory = $True,
                    ValueFromPipeline = $True,
                    ValueFromPipelineByPropertyName = $True,
                    HelpMessage = 'Enter MAC address'
                   )
        ]

        [String[]]$MACAddress

    )

    BEGIN {

        $MACIndex = 0

    }

    PROCESS {

        ForEach ($Computer In $ComputerName) {

            $Computer = $Computer.toUpper()
            $MACEntry = $MACAddress[$MACIndex].ToUpper()

            If ($MACEntry -notmatch '^([0-9A-F]{2}[:-]){5}([0-9A-F]{2})$') {

                [PsCustomObject]@{

                    'ComputerName' = $Computer
                    'MACAddress' = $MACEntry
                    'Status' = 'MAC address not formatted properly'

                }

            }

            Else {

                $Path = $MyInvocation.MyCommand.Module.ModuleBase + '\System.Data.SQLite.dll'

                Add-Type -Path $Path

                $DBConnect = New-Object -TypeName System.Data.SQLite.SQLiteConnection
                $DBConnect.ConnectionString = "Data Source = $env:ALLUSERSPROFILE\PSWakeOnLAN\WOLDatabase.db3"
                $DBConnect.Open()

                $SQL = $DBConnect.CreateCommand()
                $SQL.CommandText = "SELECT * FROM Computers WHERE Computer = ""$Computer"" OR MAC = ""$MACEntry"""
                $Adapter = New-Object -TypeName System.Data.SQLite.SQLiteDataAdapter $SQL
                $Data = New-Object System.Data.DataSet
                $Result = $Adapter.Fill($Data)

                If ($Result -gt 0) {

                    $Status = 'Computer or MAC already in database'

                }

                Else {

                    $SQL.CommandText = 'INSERT INTO Computers (Computer, MAC) VALUES (@Computer, @MAC)'
                    $SQL.Parameters.AddWithValue('@Computer', $Computer) | Out-Null
                    $SQL.Parameters.AddWithValue('@MAC', $MACEntry) | Out-Null
                    $SQL.ExecuteNonQuery() | Out-Null

                    $Status = 'Added to database'

                }

                $SQL.Dispose()
                $DBConnect.Close()

                [PsCustomObject]@{

                    'ComputerName' = $Computer
                    'MACAddress' = $MACEntry
                    'Status' = $Status

                }

            }

            $MACIndex++

        }

    }

    END {}

}