Scripting

We recently setup the Azure Active Directory Sync Tool and noticed that it does not automatically assign licenses to new users. This can be a real pain for a big company so I decided to create a script that will automatically do this for us.
Read more

For this example, we are going update our User Logon Script that you can find here and have it insert data into a SQL database.

First we will need to get our information that we will be inserting into the SQL Database:

$username = $env:USERNAME
$computername = $env:COMPUTERNAME
$ipv4 = Test-Connection -ComputerName (hostname) -Count 1 | foreach { $_.ipv4address } 
$ipv6 = Test-Connection -ComputerName (hostname) -Count 1 | foreach { $_.ipv6address } 
$computermodel = get-wmiobject win32_computersystem | foreach { $_.model } 
$serial = get-wmiobject win32_bios | foreach { $_.serialnumber } 
$action = 'Logon'
$timeformat='MM-dd-yyyy hh:mm:ss tt'
$time = (Get-Date).ToString($timeformat)

Now we will need to start connecting to our SQL Database. In the example we will be using the following SQL information:
SQL Server = SQL-Server-01
Database Name = Comp_Info
Table Name = LogonInfo
Columns = Date and Time,Username,Comptuter Name,IPv4 Address,IPv6 Address,Model,Serial,Action

Now we have to start the SQL database connection:

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Data Source=SQL-Server-01;Initial Catalog=Comp_Info;Integrated Security=SSPI;"
$connection.Open()

Now we will prepare to insert the data:

$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $connection
$cmd.CommandText = "INSERT INTO LogonInfo ([Date and Time],Username,[Comptuter Name],[IPv4 Address],[IPv6 Address],Model,Serial,Action)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')" -f
$time,$username,$computername,$ipv4,$ipv6,$computermodel,$serial,$action
$cmd.ExecuteNonQuery()

And finally we will execute the query and close the database connection:

$cmd.ExecuteNonQuery()
$connection.Close()

The entire script should look similar to this:

#Gets computer information
$username = $env:USERNAME
$computername = $env:COMPUTERNAME
$ipv4 = Test-Connection -ComputerName (hostname) -Count 1 | foreach { $_.ipv4address } 
$ipv6 = Test-Connection -ComputerName (hostname) -Count 1 | foreach { $_.ipv6address } 
$computermodel = get-wmiobject win32_computersystem | foreach { $_.model } 
$serial = get-wmiobject win32_bios | foreach { $_.serialnumber } 
$action = 'Logon'
$timeformat='MM-dd-yyyy hh:mm:ss tt'
$time = (Get-Date).ToString($timeformat)

#Connects to Database
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Data Source=SQL-Server-01;Initial Catalog=Comp_Info;Integrated Security=SSPI;"
$connection.Open()

#Inserts information to the DB
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $connection
$cmd.CommandText = "INSERT INTO LogonInfo ([Date and Time],Username,[Comptuter Name],[IPv4 Address],[IPv6 Address],Model,Serial,Action)
VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')" -f
$time,$username,$computername,$ipv4,$ipv6,$computermodel,$serial,$action
$cmd.ExecuteNonQuery()

#Closes Connection
$connection.Close()