Export Printer Information to CSV in Powershell

Here is a quick one liner to export basic printer information to CSV. I have found it handy to provide my service desk with this information when they are replacing printers and need to use the same IP information.

Get-Printer | select Name, DriverName, PortName | Export-Csv Printers.csv -NoTypeInformation

For more information about the Get-Printer command check out Technet at https://technet.microsoft.com/en-us/%5Clibrary/hh918358(v=wps.630).aspx

Read More

Create DPM Backup List using Powershell

Our auditors were curious about what exactly our Microsoft Data Protection Manager server was backing up. So I did a bit of digging around in the DPM powershell commandlet and came up with this little command.

Command

get-dpmdatasource | where {$_.Protected -eq "True"} | select productionservername, name, objecttype, protectiongroupname | Sort-Object productionservername, name | export-csv dpmserver-backup-list.csv

The CSV is rather basic but provides a nice easy list with enough detail to know what exactly is being backed up on each of the servers. Good luck keeping the auditors happy.

CSV output

ProductionServerName			Name				ObjectType					ProtectionGroupName
alpha-sqlserver.waynezim.com	master				SQL Server 2008 database	SQL Data
alpha-sqlserver.waynezim.com	model				SQL Server 2008 database	SQL Data
alpha-sqlserver.waynezim.com	msdb				SQL Server 2008 database	SQL Data
alpha-sqlserver.waynezim.com	PaperData			SQL Server 2008 database	SQL Data
child-dc1.waynezim.com			C:\					Volume						Domain Controllers
child-dc1.waynezim.com			System Protection	System Protection			Domain Controllers
child-dc2.waynezim.com			C:\					Volume						Domain Controllers
child-dc2.waynezim.com			System Protection	System Protection			Domain Controllers
exchange.cluster.waynezim.com	Administration		Storage group				Exchange Databases 
exchange.cluster.waynezim.com	Developers			Storage group				Exchange Databases 
exchange.cluster.waynezim.com	Helpdesk			Storage group				Exchange Databases 
exchange.cluster.waynezim.com	Other				Storage group				Exchange Databases 
exchange.cluster.waynezim.com	Test				Storage group				Exchange Databases 
sql.cluster.waynezim.com		HAData				SQL Server 2012 database	SQL Cluster Data
sql.cluster.waynezim.com		Test				SQL Server 2012 database	SQL Cluster Data

Read More

Audit Windows Network Settings with Powershell

We have been consolidating IT groups at the university for several years now. During this time we have to convert both clients and servers to use our central division Active Directory and other centralized services. However gathering all of the networking information can be quite the task and prone to error. One of the easiest ways I have found to gather all of this information is by running a script to generate CSV file of these settings.

I used this existing script I found over on techibee.com and took it a few steps further.

I added that ability to query Active Directory and find all the computers within an OU and the ability to adjust the filter. This can be handy if you want to find just windows servers or windows clients or just plain windows. I also fixed the output for multiple DNS servers or gateways to a CSV file as well as added some standard header and footers to make the screen output a bit cleaner. This script does require you to have the Active Directory Powershell Module Installed. It will save the CSV to the local directory from which the script is executed and report the path at the end of the screen output. This script should be run with a highly privileged user as it will need to query multiple remote computer using WMI and access Active Directory.

Script

Import-Module ActiveDirectory

$localpath = Get-Location;
$CSVFileName = $localpath.Path + "\Department-Network-Information.csv";

$ComputerName = Get-ADComputer -SearchBase "OU=Department,OU=Division,DC=domain,DC=local" -Filter {OperatingSystem -Like "Windows*"} -Property "Name";

$Results = @()

foreach ($Computer in $ComputerName)
{
    if(Test-Connection -ComputerName $Computer.Name -Count 1 -ea 0) 
    {
        $Networks = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $Computer.Name | ? {$_.IPEnabled};
        foreach ($Network in $Networks) 
        {
            $IPAddress  = $Network.IpAddress[0];
            $SubnetMask  = $Network.IPSubnet[0];
            $DefaultGateway = $Network.DefaultIPGateway;
            $DNSServers  = $Network.DNSServerSearchOrder;
            $IsDHCPEnabled = $false;
            If($network.DHCPEnabled) 
            {
                $IsDHCPEnabled = $true;
            }
            $MACAddress  = $Network.MACAddress;

            if ($DNSServers) 
            {
                $StringDNSServers = [string]::join("; ",$DNSServers);
            }
            else
            {
                $StringDNSServers = " ";
            }

            if($DefaultGateway)
            {
                $StringDefaultGateway = [string]::join("; ",$DefaultGateway);
            }
            else
            {
                $StringDefaultGateway = " ";
            }

            $ReturnedObj = New-Object -Type PSObject;
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name ComputerName -Value $Computer.Name.ToUpper();
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name IPAddress -Value $IPAddress;
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name SubnetMask -Value $SubnetMask;
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name Gateway -Value $StringDefaultGateway;
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name IsDHCPEnabled -Value $IsDHCPEnabled;
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name DNSServers -Value $StringDNSServers;
            $ReturnedObj | Add-Member -MemberType NoteProperty -Name MACAddress -Value $MACAddress;
            $ReturnedObj;
            $Results += $ReturnedObj;
        }
    }  
}

$Results | export-csv $CSVFileName -notype;

Write-Host
Write-Host "File Saved to: $CSVFileName";
Write-Host
Write-Host "Press any key to close ..."
$host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

Screen Output Example

ComputerName  : Computer1
IPAddress     : 194.90.128.89
SubnetMask    : 255.255.255.0
Gateway       : 194.90.128.254
IsDHCPEnabled : True
DNSServers    : 8.8.8.8; 8.8.4.4
MACAddress    : 18:13:73:2A:98:FA

ComputerName  : Computer2
IPAddress     : 194.90.128.92
SubnetMask    : 255.255.255.0
Gateway       : 194.90.128.254
IsDHCPEnabled : True
DNSServers    : 8.8.8.8; 8.8.4.4
MACAddress    : F8:B1:56:AC:FB:2E


File Saved to: D:\Powershell-Scripts\Department-Network-Information.csv

Press any key to close ...

CSV Output

"Computer1","194.90.128.89","255.255.255.0","194.90.128.254","True","8.8.8.8; 8.8.4.4","18:13:73:2A:98:FA"
"Computer2","194.90.128.92","255.255.255.0","194.90.128.254","True","8.8.8.8; 8.8.4.4","F8:B1:56:AC:FB:2E"

Read More

Report Workstation Uptime in a CSV using Active Directory and VBS

Have you ever been left wondering which computers on your domain have been neglected by their user and not restarted in forever? This is a question that come up in my office every once and a while. One of the easiest ways to solve this problem is to ask WMI for when the computer was last restarted and subtract it from the current time. Also, while asking WMI questions you might as well ask which user is currently logged on the PC that way you know who to blame. This is exactly what the following script does for your domain. It grabs the list of workstations from the domain then queries WMI for the last time the computer is restarted and does some conversion and math and makes you an nice CSV that you can play with.

Script Configuration
Before running this script there is some minor configuration that must be done so it can communicate with your Active Directory setup.

  1. Find objConnection.Open “Active Directory Server” change Active Directory Server to the name of your Domain Controller
  2. Find objCommand.CommandText = _
    “Select Name, Location from ‘LDAP://OU=Workstations,DC=west,DC=domain,DC=edu’ ” _
    & “Where objectClass=’computer'”
    change subdomain, domain, and suffix to the name of your domain i.e. west domain edu (respectively)
  3. Find GetUptime objRecordSet.Fields(“Name”).Value, “C:\uptime.csv” and change C:\uptime.csv to the location where you want the file saved. Be sure to save it with the extension CSV
Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Server" 

Set objCOmmand.ActiveConnection = objConnection
objCommand.CommandText = _
    "Select Name, Location from 'OU=Workstations,DC=west,DC=domain,DC=edu' " _
        & "Where objectClass='computer'"  
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

Do Until objRecordSet.EOF
	GetUptime objRecordSet.Fields("Name").Value, "C:\uptime.csv"
    objRecordSet.MoveNext
Loop

Sub GetUptime(strComputer, strFilename)
	On Error Resume Next
	Set StdOut = WScript.StdOut
	 
	Set objFSO = CreateObject("scripting.filesystemobject")
	Set logStream = objFSO.opentextfile(strFilename, 8, True)
	 
	Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
	If Err.Number Then
	      logStream.writeline(strComputer & ",Offline")
	      Err.Clear
	Else
		Set objWMIService = GetObject _
			("winmgmts:\\" & strComputer & "\root\cimv2")
		Set colOperatingSystems = objWMIService.ExecQuery _
			("Select * from Win32_OperatingSystem")
		For Each objOS in colOperatingSystems
			dtmBootup = objOS.LastBootUpTime
			dtmLastBootupTime = WMIDateStringToDate(dtmBootup)
			dtmSystemUptime = DateDiff("h", dtmLastBootUpTime, Now()) 
		Next
		Set objWMIService = GetObject _
			("winmgmts:\\" & strComputer & "\root\cimv2")
		Set colComputerSys = objWMIService.ExecQuery _
			("Select UserName from Win32_ComputerSystem")
		For Each objCS in colComputerSys
			username = objCS.UserName
			logStream.writeline(strComputer & ",Online," & dtmSystemUptime & "," & dtmLastBootupTime & "," & username) 
		Next
				
	End If
	logStream.Close
End Sub
Function WMIDateStringToDate(dtmBootup)
    WMIDateStringToDate = CDate(Mid(dtmBootup, 5, 2) & "/" & _
         Mid(dtmBootup, 7, 2) & "/" & Left(dtmBootup, 4) _
         & " " & Mid (dtmBootup, 9, 2) & ":" & _
         Mid(dtmBootup, 11, 2) & ":" & Mid(dtmBootup, _
         13, 2))
End Function

Read More

List All Active Directory User Accounts in a CSV

We all know maintaining hundreds of user accounts can be frustrating especially when it comes to audit time and you need a good list of information to pass on to an auditor. Well today I am your savory, this simple script will produce you a list of users with some detailed information that can make audits or documentation much easier. The script creates a Comma Separated Values file or CSV that you can edit in Microsoft Excel or any standard spreadsheet application so you can customize the information before adding it to your report or audit. Below are the specific fields that this script will provide detail on for your Active Directory Users.

User Details

  • Name
  • Description
  • Profile Path
  • Home Drive
  • Account Disabled
  • Password Required
  • User Changable Password
  • Password Expires
  • SmartCard Required
  • Login Count
  • Last Login (date)
  • Last Password Change (date)
  • Created (date)
  • Modified (date)

Script Configuration
Before running this script there is some minor configuration that must be done so it can communicate with your Active Directory setup.

  1. Find objConnection.Open “Active Directory Server” change Active Directory Server to the name of your Domain Controller
  2. Find objCommand.CommandText = _
    “SELECT Name, description, profilePath, homeDrive, distinguishedName,userAccountControl FROM ‘LDAP://dc=subdomain,dc=domain,dc=suffix’ WHERE objectCategory=’user'”
    change subdomain, domain, and suffix to the name of your domain i.e. west consco com (respectively)
  3. Find Set logStream = objFSO.opentextfile(“C:\domainusers.csv”, 8, True) and change C:\domainusers.csv to the location where you want the file saved. Be sure to save it with the extension CSV
On Error Resume Next
Const ADS_SCOPE_SUBTREE = 2

Const ADS_UF_ACCOUNTDISABLE = &H0002 
Const ADS_UF_PASSWD_NOTREQD = &H0020 
Const ADS_UF_PASSWD_CANT_CHANGE = &H0040 
Const ADS_UF_DONT_EXPIRE_PASSWD = &H10000 
Const ADS_UF_SMARTCARD_REQUIRED = &H40000 
 
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Server"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

objCommand.CommandText = _
    "SELECT Name, description, profilePath, homeDrive, distinguishedName,userAccountControl FROM 'LDAP://dc=subdomain,dc=domain,dc=suffix' WHERE objectCategory='user'"  
Set objRecordSet = objCommand.Execute

objRecordSet.MoveFirst
Set objFSO = CreateObject("scripting.filesystemobject")
Set logStream = objFSO.opentextfile("C:\domainusers.csv", 8, True)
logStream.writeline("Name,Description,Profile Path,Home Drive,Account Disabled,Password Required,User Changable Password,Password Expires,SmartCard Required,Login Count,Last Login,Last Password Change,Created,Modified")
Do Until objRecordSet.EOF

	strDN = objRecordset.Fields("distinguishedName").Value 
	Set objUser = GetObject ("LDAP://" & strDN)
	 
	If objRecordset.Fields("userAccountControl").Value AND ADS_UF_ACCOUNTDISABLE Then
		Text = "Yes"
	Else
		Text = "No"
	End If
	If objRecordset.Fields("userAccountControl").Value AND ADS_UF_PASSWD_NOTREQD Then
		Text = Text & ",No"
	Else
		Text = Text & ",Yes"
	End If
	 
	If objRecordset.Fields("userAccountControl").Value AND ADS_PASSWORD_CANT_CHANGE Then
		Text = Text & ",No"
	Else
		Text = Text & ",Yes"
	End If	 
	If objRecordset.Fields("userAccountControl").Value AND ADS_UF_DONT_EXPIRE_PASSWD Then
		Text = Text & ",No"
	Else
		Text = Text & ",Yes"
	End If
	If objRecordset.Fields("userAccountControl").Value AND ADS_UF_SMARTCARD_REQUIRED Then
		Text = Text & ",Yes"
	Else
		Text = Text & ",No"
	End If
	
	logStream.writeline(objRecordset.Fields("Name").Value & ","_
		& objRecordset.Fields("description").Value & ","_
		& objRecordset.Fields("profilePath").Value & ","_
		& objRecordset.Fields("homeDrive").Value & ","_
		& text & ","_
		& objUser.logonCount & ","_
		& objUser.LastLogin & ","_
		& objUser.PasswordLastChanged & ","_
		& objUser.whenCreated & ","_
		& objUser.whenChanged & ","_
		)
		
    objRecordSet.MoveNext
Loop
logStream.Close

Read More