script

PRTG sensor to monitor a directory for a specific file type and minimum size and age

The following script will monitor a specific directory for a file of a defined type and minimum size, it will report back to you the newest file of the specified type and over the minimum size and provide you the file age and size to PRTG.

The name of the file will also be reported back as a text value for the sensor in PRTG.

If the script encounters any errors, the total files value will be 0 for no files found or -1 for a script error. Therefor please set minimum channel error values accordingly to get alerted.

The goal here is to get the age of the file back that was found, to make sure the file is not older than expected. This is needed for some automated exports or data transfer files, this was you can be sure that your export routines work as expected.

param(
    &#91;string] $FolderPath = "C:\",
    &#91;string] $Extension = "*.undefined",
    &#91;int] $FileMinimumSizeByte = 1
)

Function WriteXmlToScreen (&#91;xml]$xml)
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

Try {
    $Files = Get-ChildItem -Path $FolderPath -Filter $Extension | Where {$_.Lengthength -gt $FileMinimumSizeByte} | Sort-Object CreationTime -Descending
    If ($Files.Count -gt 0) { 
        Foreach ($File in $Files)
        {
            $TotalFiles = $Files.Count
            $Date = Get-Date
            $FileAgeSeconds = &#91;math]::Round(($Date - $File.CreationTime).TotalSeconds)
            $FileSize = $File.Length
            $FileName = $File.Name

            $XML =  "&lt;prtg>"
            $XML += "&lt;result>&lt;channel>Total Files&lt;/channel>&lt;value>$TotalFiles&lt;/value>&lt;unit>Count&lt;/unit>&lt;/result>"
            $XML += "&lt;result>&lt;channel>Newest File - Age in Seconds&lt;/channel>&lt;value>$FileAgeHours&lt;/value>&lt;unit>TimeSeconds&lt;/unit>&lt;/result>"
            $XML += "&lt;result>&lt;channel>Newest File - Size in Bytes&lt;/channel>&lt;value>$FileSize&lt;/value>&lt;unit>Byte&lt;/unit>&lt;/result>"
            $XML += "&lt;text>$FileName&lt;/text>"
            $XML += "&lt;/prtg>"

            WriteXmlToScreen $XML
        
            Break;
        }
    } else {
        $XML =  "&lt;prtg>"
        $XML += "&lt;result>&lt;channel>Total Files&lt;/channel>&lt;value>0&lt;/value>&lt;unit>Count&lt;/unit>&lt;/result>"
        $XML += "&lt;result>&lt;channel>Newest File - Age in Seconds&lt;/channel>&lt;value>0&lt;/value>&lt;unit>Count&lt;/unit>&lt;/result>"
        $XML += "&lt;result>&lt;channel>Newest File - Size in Bytes&lt;/channel>&lt;value>0&lt;/value>&lt;unit>Byte&lt;/unit>&lt;/result>"
        $XML += "&lt;text>No Files found&lt;/text>"
        $XML += "&lt;/prtg>"

        WriteXmlToScreen $XML
    }
} Catch {

    $XML =  "&lt;prtg>"
    $XML += "&lt;result>&lt;channel>Total Files&lt;/channel>&lt;value>-1&lt;/value>&lt;unit>Count&lt;/unit>&lt;/result>"
    $XML += "&lt;result>&lt;channel>Newest File - Age in Seconds&lt;/channel>&lt;value>-1&lt;/value>&lt;unit>Count&lt;/unit>&lt;/result>"
    $XML += "&lt;result>&lt;channel>Newest File - Size in Bytes&lt;/channel>&lt;value>-1&lt;/value>&lt;unit>Byte&lt;/unit>&lt;/result>"
    $XML += "&lt;text>Error&lt;/text>"
    $XML += "&lt;/prtg>"

    WriteXmlToScreen $XML

}

param(

[string] $FolderPath = "C:\",

[string] $Extension = "*.undefined",

[int] $FileMinimumSizeByte = 1

)

Function WriteXmlToScreen ([xml]$xml)

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

Try {

$Files = Get-ChildItem -Path $FolderPath -Filter $Extension | Where {$_.Lengthength -gt $FileMinimumSizeByte} | Sort-Object CreationTime -Descending

If ($Files.Count -gt 0) {

Foreach ($File in $Files)

{

$TotalFiles = $Files.Count

$Date = Get-Date

$FileAgeSeconds = [math]::Round(($Date - $File.CreationTime).TotalSeconds)

$FileSize = $File.Length

$FileName = $File.Name

$XML = "<prtg>"

$XML += "<result><channel>Total Files</channel><value>$TotalFiles</value><unit>Count</unit></result>"

$XML += "<result><channel>Newest File - Age in Seconds</channel><value>$FileAgeHours</value><unit>TimeSeconds</unit></result>"

$XML += "<result><channel>Newest File - Size in Bytes</channel><value>$FileSize</value><unit>Byte</unit></result>"

$XML += "<text>$FileName</text>"

$XML += "</prtg>"

WriteXmlToScreen $XML

Break;

}

} else {

$XML = "<prtg>"

$XML += "<result><channel>Total Files</channel><value>0</value><unit>Count</unit></result>"

$XML += "<result><channel>Newest File - Age in Seconds</channel><value>0</value><unit>Count</unit></result>"

$XML += "<result><channel>Newest File - Size in Bytes</channel><value>0</value><unit>Byte</unit></result>"

$XML += "<text>No Files found</text>"

$XML += "</prtg>"

WriteXmlToScreen $XML

}

} Catch {

$XML = "<prtg>"

$XML += "<result><channel>Total Files</channel><value>-1</value><unit>Count</unit></result>"

$XML += "<result><channel>Newest File - Age in Seconds</channel><value>-1</value><unit>Count</unit></result>"

$XML += "<result><channel>Newest File - Size in Bytes</channel><value>-1</value><unit>Byte</unit></result>"

$XML += "<text>Error</text>"

$XML += "</prtg>"

WriteXmlToScreen $XML

}

ActiveDirectory/LDAP result limits – MaxPageSize

ns a website from a systems administrator for systems administrators Home IT-Admins CMDB IT-Admins tool IT Search EOL Solutions Blog Contact Links ActiveDirectory/LDAP result limits – MaxPageSize

ActiveDirectory, respective LDAP, has a result limit setting, MaxPageSize. Those are set by default to 1000 rows per query.

This is primarily important if you use some kind of programming language to get results from LDAP, this code must compensate those limits and engage paging.

Your LDAP query does not need to provide the limit, only the code needs to do the paging as you always just get the max. amount of results set in the current settings.

In order to check your settings do the following commands in a command prompt / cmd window:

ntdsutil
ldap policies 
connections
connect to domain YOURDOMAIN.LOCAL
quit
show values

ntdsutil

ldap policies

connections

connect to domain YOURDOMAIN.LOCAL

quit

show values

In theory you could set different values now as well, assuming you have the permission level to do so. But this is not recommended and you should engage paging instead, as you otherwise risk to overload your DCs – even if your commands won’t cause it, a possibly DoS attack could happen – malicious or not, so leave the limits, but be aware of them.

April 7, 2021 by Florian Rossmark configuration server solutions

Monitor group memberships in Active Directory with PRTG

There is at least one group you want to monitor for any membership changes in Active Directory / LDAP – the Domain Admins group. This is so important, as any changes to this group could cause great harm to your whole system. Of course there are other ways in but you for sure want to monitor at least the basic information of the amount of users in this group.

In order to do so, I wrote a PowerShell script that provides you the amount of members of any given group in Active Directory, as well as a text response (under the probe name and in some alerts if activated) of the sAMAccountName of each member in the group. This way you can hopefully right away determine the changed object, assuming you know what should be in there and what not.

If you have nested groups, you might wanna monitor them as well till you reach a user only level.

Create the script as always on your PRTG probing server in C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML and add a new EXE/Advanced XML sensor in PRTG. Select the script and provide at a bare minimum the parameter MonitoredGroup.

Parameter samples:

-MonitoredGroup “Domain Admins”
-MonitoredGroup “Domain Admins” -Server “MyDC.domain.local”

If you do not provide a server name, the system will try determine it on it’s own – default Domain-Membership etc..

Once the first run was successful you should review the results and set the upper and lower error limit on the PRTG sensor to the current amount of members. Any change then will cause the sensor to go in to error status and inform you therefor about the change.

param(
	[string]$Server = "",
	[string]$MonitoredGroup = ""
)
Import-Module ActiveDirectory

If ($Server.Length -gt 0) {
    $LDAPGroup = Get-ADGroupMember $MonitoredGroup -Server $Server
} Else {
    $LDAPGroup = Get-ADGroupMember $MonitoredGroup 
}

[string]$LDAPGroupMembers = ""
Foreach ($Member in $LDAPGroup){
    If ($LDAPGroupMembers.Length -gt 0) {$LDAPGroupMembers += ", "}
    $LDAPGroupMembers += $Member.SamAccountName
}

$XML = "<prtg>
            <result>
                <channel>Amound of Users in Group</channel>
                <value>"+ $LDAPGroup.count +"</value>
            </result>			
            <text>"+ $MonitoredGroup +" Members: " + $LDAPGroupMembers + "</text>
        </prtg>"
 
Function WriteXmlToScreen ([xml]$XML) #just to make it clean XML code...
{
	$StringWriter = New-Object System.IO.StringWriter;
	$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
	$XmlWriter.Formatting = "indented";
	$xml.WriteTo($XmlWriter);
	$XmlWriter.Flush();
	$StringWriter.Flush();
	Write-Output $StringWriter.ToString();
}
 
WriteXmlToScreen $XML;

param(

[string]$Server = "",

[string]$MonitoredGroup = ""

)

Import-Module ActiveDirectory

If ($Server.Length -gt 0) {

$LDAPGroup = Get-ADGroupMember $MonitoredGroup -Server $Server

} Else {

$LDAPGroup = Get-ADGroupMember $MonitoredGroup

}

[string]$LDAPGroupMembers = ""

Foreach ($Member in $LDAPGroup){

If ($LDAPGroupMembers.Length -gt 0) {$LDAPGroupMembers += ", "}

$LDAPGroupMembers += $Member.SamAccountName

}

$XML = "<prtg>

<channel>Amound of Users in Group</channel>

<value>"+ $LDAPGroup.count +"</value>

</result>

<text>"+ $MonitoredGroup +" Members: " + $LDAPGroupMembers + "</text>

</prtg>"

Function WriteXmlToScreen ([xml]$XML) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen $XML;

October 13, 2020 by Florian Rossmark monitoring prtg scripts security server

Monitoring relative printer page counts with PRTG

PRTG has many standard sensors, but one I was always missing is a daily page count compare. The standard printer sensor gives you a total page count – but this to some extend will always be a graph that only will go up. You can only estimate the total page counts in those graphs.

If you ever looked in to the IT Assets database project, you will see that in the Printers area there is a possibility to enable detailed graphs for relative page counts.

Why is this important you might wonder. The answer is simple, as an IT Manager you need to know if a certain kind of a printer makes sense at a certain location. If you have a low end printer for only casual print-outs but you have a total over e.g. 10,000 pages printed every month, you might need to reconsider the printer model. The reasons would likely be:

higher cost per page
- constant toner exchange of a compared more expensive toner cartridge
maintenance cost
- you might need to constantly maintenance the printer
- the cost for the maintenance kit are relatively high
downtime issues
- due to toner empty
- printer needs maintenance again
- less pages in paper tray

On the other hand, a printer might also be overkill for a certain area and not be cost efficient. Those conditions also might change over time of course. Further is there often the question – is a single area printer (copier) better or multiple smaller printers. This of course can go pretty far and you want to consider Lean processes, Six Sigma guidelines and others along with this data.

How ever, I started a first draft of a script that provides me at least the total page count relative to each day in PRTG. This sure is not as efficient yet as I do this in the IT Assets database printer module, where I collect data e.g. every 30 minutes in a huge table and then later calculate all the data in a daily range respective monthly range while collecting total page counts and possibly counts per copy vs. print outs and additionally color vs. black and white print. But at least it is a start.

Below you find the first draft of this script.

One thing to know – you will need to run the following command in order to install the PowerShell SNMP module on your PRTG probing server:

Install-Module -Name SNMP

1	Install-Module -Name SNMP

The current version of the PRTG script:

#For PRTG - default parameter list: 
#-deviceID %deviceid -targetHost '%host' -OID '1.3.6.1.2.1.43.10.2.1.4.1.1' 
#find a list with OID examples at the following link as they differ by vendor and partly model
#https://www.it-admins.com/it-assets-database/online-manual/printers/
param(
    [Parameter(
        Mandatory = $true
    )][int]$deviceID,

    [Parameter(
        Mandatory = $true
    )][string]$targetHost,

    [Parameter(
        Mandatory = $true
    )][string]$OID = "",

    [string]$Community = "public"
)

Import-Module -Name SNMP

[string]$ErrorText = "" #will hold error information to be returned to PRTG text field / sensor message
[int]$ErrorValue = 0 #default no errors initiated with 0 (0 = OK | 1 = Warning | 2 = Error)

[string]$LogFile = "$PSScriptRoot\PrinterLog_$deviceID.log"

[int]$result = -2 #standard response would be negative 2 as initialized integer value

try{
    $result = (Get-SNMPdata -IP $targetHost -OID $OID -Community $Community).Data
    #if no error occured, we have the current page count as a result
} catch {
    $result = -1 #if any error happened, we now have the result set to negative 1
    $ErrorText += "error: can't read SNMP OID value from device "
    $ErrorValue = 2
}

[int]$returnValue = 0 #default return value, integer, initialized with 0
[string]$LogFileContent = -1 #default logfilecontent is integer, intialized with negative 1
[string]$LogFileDate 
[int]$LogFileCount = -1

if ($result -gt 0) {
    #we seem to have a page count from SNMP

    if (Test-Path $LogFile -PathType Leaf){
        #we do have an exisiting LogFile
        try{
            $LogFileContent = Get-Content $LogFile -Raw
            if ($LogFileContent.Length -gt 0){
                $LogFileDate = $LogFileContent.Split("|")[0] 
                $LogFileCount = $LogFileContent.Split("|")[1]
            } else {
                #LogFile empty
                $LogFileCount = 0
                $ErrorText += "warning: initial run or no logfile yet "
                $ErrorValue = 1
            }
        } catch {
            $LogFileCount = -2 #negative 2 as error catch
        } finally {
            if ($LogFileCount -gt 0){
                #no error occured as for the LogFile reading
                if (($result - $LogFileCount) -gt -1){
                    #it should be either the same value or higher then the last logfile value
                    $returnValue = $result - $LogFileCount
                } else {
                    #LogFile value is higher then current count - this is weird
                    #still returning this - but it will show a negative count and should raise a flag
                    $returnValue = $result - $LogFileCount
                    $ErrorText += "error: logfile value higher then current printer read "
                    $ErrorValue = 2
                }
            } else {
                #there was some kind of error in the LogFile read - not an integer value or new Logfile started and result was 0
                $ErrorText += "error: can't read logfile for data compare "
                $ErrorValue = 2
            }
        }

    } else {
        #no LogFile yet - will need one but return value as difference initially will stay 0
        #we set the LogFileContent to 0 as this is the current status
        $LogFileCount = 0
        $ErrorText += "warning: initial run or no logfile yet or wrong data format "
        $ErrorValue = 1
    }

    #we need to write the positive SNMP OID result to the LogFile 
    #but only if the date differs more then 1x day

    [bool]$WriteLog = $false
    [string]$CurrentDate = (Get-Date -Format yyyy-MM-dd)

    if ($LogFileDate.Length -gt 0){
        try{
            if ((New-TimeSpan -Start $LogFileDate -End $CurrentDate).Days -gt 1){ 
                #date difference more then 1 day 
                $WriteLog = $true
            } else {
                #date difference not sufficient - same day? Future?
                if ((New-TimeSpan -Start $LogFileDate -End $CurrentDate).Days -lt 0){ 
                    #LogFile newer then current date? That's just wrong
                    $WriteLog = $true
                    $ErrorText += "warning: Logfile date above current date $LogFileDate - rewriting log"
                    $ErrorValue = 1
                } else {
                    #not greater then 1 day (0 and 1) or less must be equal as a result...
                    #nothing to do as WriteLog is initialized false
                }
            }
        } catch {
            #couldn't compare the date - let's write the Log to be save
            $WriteLog = $true
        }
    } else {
        #LogFileDate length 0 means we need to write a logfile as it might not exist
        $WriteLog = $true
    }

    if ($WriteLog){
        try {
            Set-Content -Path $LogFile -Value "$CurrentDate|$result"
            $LogFileDate = $CurrentDate
        } catch {
            #there was an issue writing to the LogFile
            $ErrorText += "error: can't write to logfile "
            $ErrorValue = 2
        }
    }

} else {
    #no pagecount available due to errors or issues
    #uncomment the next line for debugging only
    #$returnValue = $result
    #the returnValue will now show the current result value and point therefor to the error section
    $ErrorText += "error: no pagecount available "
    $ErrorValue = 2
}

#Assuming no errors - the text result will be the last LogFile date
if ($ErrorText.Length -eq 0){
    $ErrorText = "Last LogFile write date: $LogFileDate"
}

$XML = "<prtg>
            <result>
                <channel>last relative Page Count</channel>
                <value>$returnValue</value>
            </result>
            <result>
                <channel>last total from Printer</channel>
                <value>$result</value>
            </result>
            <result>
                <channel>last total from LogFile</channel>
                <value>$LogFileCount</value>
            </result>
            <result>
                <channel>Script Error Status</channel>
                <value>$ErrorValue</value>
                <LimitMode>1</LimitMode>
                <LimitMaxWarning>0</LimitMaxWarning>
                <LimitMaxError>1</LimitMaxError>
            </result>
            <text>$ErrorText</text>
        </prtg>"
 
Function WriteXmlToScreen ([xml]$XML) #just to make it clean XML code...
{
	$StringWriter = New-Object System.IO.StringWriter;
	$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
	$XmlWriter.Formatting = "indented";
	$xml.WriteTo($XmlWriter);
	$XmlWriter.Flush();
	$StringWriter.Flush();
	Write-Output $StringWriter.ToString();
}
 
WriteXmlToScreen $XML;

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

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

#For PRTG - default parameter list:

#-deviceID %deviceid -targetHost '%host' -OID '1.3.6.1.2.1.43.10.2.1.4.1.1'

#find a list with OID examples at the following link as they differ by vendor and partly model

#https://www.it-admins.com/it-assets-database/online-manual/printers/

param(

[Parameter(

Mandatory = $true

)][int]$deviceID,

[Parameter(

Mandatory = $true

)][string]$targetHost,

[Parameter(

Mandatory = $true

)][string]$OID = "",

[string]$Community = "public"

)

Import-Module -Name SNMP

[string]$ErrorText = "" #will hold error information to be returned to PRTG text field / sensor message

[int]$ErrorValue = 0 #default no errors initiated with 0 (0 = OK | 1 = Warning | 2 = Error)

[string]$LogFile = "$PSScriptRoot\PrinterLog_$deviceID.log"

[int]$result = -2 #standard response would be negative 2 as initialized integer value

try{

$result = (Get-SNMPdata -IP $targetHost -OID $OID -Community $Community).Data

#if no error occured, we have the current page count as a result

} catch {

$result = -1 #if any error happened, we now have the result set to negative 1

$ErrorText += "error: can't read SNMP OID value from device "

$ErrorValue = 2

}

[int]$returnValue = 0 #default return value, integer, initialized with 0

[string]$LogFileContent = -1 #default logfilecontent is integer, intialized with negative 1

[string]$LogFileDate

[int]$LogFileCount = -1

if ($result -gt 0) {

#we seem to have a page count from SNMP

if (Test-Path $LogFile -PathType Leaf){

#we do have an exisiting LogFile

try{

$LogFileContent = Get-Content $LogFile -Raw

if ($LogFileContent.Length -gt 0){

$LogFileDate = $LogFileContent.Split("|")[0]

$LogFileCount = $LogFileContent.Split("|")[1]

} else {

#LogFile empty

$LogFileCount = 0

$ErrorText += "warning: initial run or no logfile yet "

$ErrorValue = 1

}

} catch {

$LogFileCount = -2 #negative 2 as error catch

} finally {

if ($LogFileCount -gt 0){

#no error occured as for the LogFile reading

if (($result - $LogFileCount) -gt -1){

#it should be either the same value or higher then the last logfile value

$returnValue = $result - $LogFileCount

} else {

#LogFile value is higher then current count - this is weird

#still returning this - but it will show a negative count and should raise a flag

$returnValue = $result - $LogFileCount

$ErrorText += "error: logfile value higher then current printer read "

$ErrorValue = 2

}

} else {

#there was some kind of error in the LogFile read - not an integer value or new Logfile started and result was 0

$ErrorText += "error: can't read logfile for data compare "

$ErrorValue = 2

}

} else {

#no LogFile yet - will need one but return value as difference initially will stay 0

#we set the LogFileContent to 0 as this is the current status

$LogFileCount = 0

$ErrorText += "warning: initial run or no logfile yet or wrong data format "

$ErrorValue = 1

}

#we need to write the positive SNMP OID result to the LogFile

#but only if the date differs more then 1x day

[bool]$WriteLog = $false

[string]$CurrentDate = (Get-Date -Format yyyy-MM-dd)

if ($LogFileDate.Length -gt 0){

try{

if ((New-TimeSpan -Start $LogFileDate -End $CurrentDate).Days -gt 1){

#date difference more then 1 day

$WriteLog = $true

} else {

#date difference not sufficient - same day? Future?

if ((New-TimeSpan -Start $LogFileDate -End $CurrentDate).Days -lt 0){

#LogFile newer then current date? That's just wrong

$WriteLog = $true

$ErrorText += "warning: Logfile date above current date $LogFileDate - rewriting log"

$ErrorValue = 1

} else {

#not greater then 1 day (0 and 1) or less must be equal as a result...

#nothing to do as WriteLog is initialized false

}

} catch {

#couldn't compare the date - let's write the Log to be save

$WriteLog = $true

}

} else {

#LogFileDate length 0 means we need to write a logfile as it might not exist

$WriteLog = $true

}

if ($WriteLog){

try {

Set-Content -Path $LogFile -Value "$CurrentDate|$result"

$LogFileDate = $CurrentDate

} catch {

#there was an issue writing to the LogFile

$ErrorText += "error: can't write to logfile "

$ErrorValue = 2

}

} else {

#no pagecount available due to errors or issues

#uncomment the next line for debugging only

#$returnValue = $result

#the returnValue will now show the current result value and point therefor to the error section

$ErrorText += "error: no pagecount available "

$ErrorValue = 2

}

#Assuming no errors - the text result will be the last LogFile date

if ($ErrorText.Length -eq 0){

$ErrorText = "Last LogFile write date: $LogFileDate"

}

$XML = "<prtg>

<channel>last relative Page Count</channel>

<value>$returnValue</value>

</result>

<channel>last total from Printer</channel>

<value>$result</value>

</result>

<channel>last total from LogFile</channel>

<value>$LogFileCount</value>

</result>

<channel>Script Error Status</channel>

<value>$ErrorValue</value>

</result>

<text>$ErrorText</text>

</prtg>"

Function WriteXmlToScreen ([xml]$XML) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen $XML;

June 17, 2020 by Florian Rossmark monitoring prtg recommendations scripts server

Office 365 licenses and activated features per user

Ever wondered which user has what license activated and e.g. which specific feature is activated? Recently I was challenged to see who has the Exchange mailbox feature enabled and who not out of the active user base. Due to the huge user-base this would have taken hours to review manually. Using PowerShell for this, connecting to Office 365, exporting the data eventually to a CSV file and filtering it in Microsoft Excel made this way easier.

The challenge here is that Microsoft uses SKU’s – or licenses – that again can have various features enabled or disabled. Let’s say you have a E5 Plan (license) assigned to your user, you still can disabled various features within this plan, e.g. Microsoft Exchange.

If you take a look at the following website, you find a whole list of GUIDs / IDs of all those various features.

https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference

In case of the Microsoft Exchange Mailbox feature – we are talking about this GUID: efb87545-963c-4e0d-99df-69c6916d9eb0

Once I had identified the GUID the next step was to grab users from a specific on premise Active Directory OU and query them against Microsoft Azure on the Office 365 environment as for their assigned licenses/features. The results then are collected in a PowerShell object and eventually saved in a defined file name in a CSV format that you easily can filter in Excel afterwards.

Please keep in mind that you will need RSAT tools (PowerShell) and Azure/Office 365 connectivity, rights etc. in order for this to work.

#script looks for all users in a base OU path
#it checks for the license defined per user
#it will write a CSV file in the path the script is in: O365UserMailboxList.csv 

#Base OU path - needs to be set
$BaseOU = "DC=domain,DC=local"


#Exchange mailbox license ID - see link for full list
#https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference
$ExchangeMailboxId = "efb87545-963c-4e0d-99df-69c6916d9eb0"


#Script starts below - no additional changes should be needed - besides the last line for the output file name
$tbl = New-Object System.Data.DataTable "Results"
$col1 = New-Object System.Data.DataColumn UserPrincipalName
$col2 = New-Object System.Data.DataColumn ADUserEnabled
$col3 = New-Object System.Data.DataColumn ExchangeMailboxEnabled
$col4 = New-Object System.Data.DataColumn ErrorMessage
$tbl.Columns.Add($col1)
$tbl.Columns.Add($col2)
$tbl.Columns.Add($col3)
$tbl.Columns.Add($col4)

$ADUsersFromOU = Get-ADUser -SearchBAse $BaseOU -filter * 

Connect-AzureAD

ForEach ($ADUser In $ADUsersFromOU) {
    $i += 1
    $p = [math]::Round(($i/$ADusersFromOU.Count*100),0)
    Write-Host "..processing $i of"$ADusersFromOU.Count"- $p% - "$ADUser.UserPrincipalName
    $MailboxEnabled = $false
    $ErrorMessage = ""
    try {
        $UserLicenses = Get-AzureADUser -objectid $ADUser.UserPrincipalName | Select -ExpandProperty AssignedPlans | Where {$_.CapabilityStatus -eq "Enabled"}
    } catch {
        $ErrorMessage = $Error[0]
        #Write-Host "....Error: "$Error[0]
    }
    ForEach ($ULicense In $UserLicenses) {
        If ($ULicense.ServicePlanId -eq $ExchangeMailboxId)
        {
            $MailboxEnabled = $true
            break;
        }
    }
    $row = $tbl.NewRow()
    $row.UserPrincipalName = $ADUser.UserPrincipalName
    $row.ADUserEnabled = $ADUser.Enabled
    $row.ExchangeMailboxEnabled = $MailboxEnabled
    $row.ErrorMessage = $ErrorMessage
    $tbl.Rows.Add($row)
}

$tbl |ft

#adjust this export-file name if you want to
$tbl | Export-Csv -Path .\O365UserMailboxList.csv

#script looks for all users in a base OU path

#it checks for the license defined per user

#it will write a CSV file in the path the script is in: O365UserMailboxList.csv

#Base OU path - needs to be set

$BaseOU = "DC=domain,DC=local"

#Exchange mailbox license ID - see link for full list

#https://docs.microsoft.com/en-us/azure/active-directory/users-groups-roles/licensing-service-plan-reference

$ExchangeMailboxId = "efb87545-963c-4e0d-99df-69c6916d9eb0"

#Script starts below - no additional changes should be needed - besides the last line for the output file name

$tbl = New-Object System.Data.DataTable "Results"

$col1 = New-Object System.Data.DataColumn UserPrincipalName

$col2 = New-Object System.Data.DataColumn ADUserEnabled

$col3 = New-Object System.Data.DataColumn ExchangeMailboxEnabled

$col4 = New-Object System.Data.DataColumn ErrorMessage

$tbl.Columns.Add($col1)

$tbl.Columns.Add($col2)

$tbl.Columns.Add($col3)

$tbl.Columns.Add($col4)

$ADUsersFromOU = Get-ADUser -SearchBAse $BaseOU -filter *

Connect-AzureAD

ForEach ($ADUser In $ADUsersFromOU) {

$i += 1

$p = [math]::Round(($i/$ADusersFromOU.Count*100),0)

Write-Host "..processing $i of"$ADusersFromOU.Count"- $p% - "$ADUser.UserPrincipalName

$MailboxEnabled = $false

$ErrorMessage = ""

try {

$UserLicenses = Get-AzureADUser -objectid $ADUser.UserPrincipalName | Select -ExpandProperty AssignedPlans | Where {$_.CapabilityStatus -eq "Enabled"}

} catch {

$ErrorMessage = $Error[0]

#Write-Host "....Error: "$Error[0]

}

ForEach ($ULicense In $UserLicenses) {

If ($ULicense.ServicePlanId -eq $ExchangeMailboxId)

{

$MailboxEnabled = $true

break;

}

$row = $tbl.NewRow()

$row.UserPrincipalName = $ADUser.UserPrincipalName

$row.ADUserEnabled = $ADUser.Enabled

$row.ExchangeMailboxEnabled = $MailboxEnabled

$row.ErrorMessage = $ErrorMessage

$tbl.Rows.Add($row)

}

$tbl |ft

#adjust this export-file name if you want to

$tbl | Export-Csv -Path .\O365UserMailboxList.csv

March 24, 2020 by Florian Rossmark o365 / m365 scripts

PRTG and VMware 6.7 vCenter host hardware status

The following script was created to bypass an issue in the SOAP API in relation with VMware, hardware vendor drivers and PRTG. In any case, you could use the same script for other monitoring systems or any other purpose – of course while adjusting it to your needs.

You can find more information about the issue here: https://kb.paessler.com/en/topic/82458-vmware-host-hardware-status-soap-sensor-returns-warnings-after-update-to-vmware-6-7

In order to make this work you need to install the VMware PowerCLI PowerShell extension on the PRTG probe server. Further will you need to inject username and password as well as the vCenter name and internal hostname in vCenter.

LDAP authentication activated targets:

$host %host “%windowsdomain\%windowsuser” “%windowspassword”
$host %host.domain.local “%windowsdomain\%windowsuser” “%windowspassword”

Otherwise – you might need to use this format:

$host %host root MyRootPW

Test it in PowerShell as the Probe-User first – you should see the results. Eventually the script create a sensor with multiple channels – sensors in GREEN status will be counted only – sensors in UNKNOWN status will be counted and returned as text, while as long as no YELLOW or RED status (warning or error) occurs, the sensor still stays green/okay. Warning or Error levels will automatically apply and have the problematic hardware systems in the sensor message text.

My first attempt was to show all channels on top of the summary – due to getting over 100 separate hardware statuses back and the limitation in PRTG of 50 channels per sensor, I dropped the idea – while the script still has all the code to handle it.

#pre-req
#make sure the VMware PowerCLI is installed for all users
#install-module -name vmware.powercli -AllowClobber 
#make sure you set the ignore certificate error - assuming you do not have a valid cert on the vCenter
#Set-PowerCLIConfiguration -InvalidCertificateAction Ignore

param(
    [string] $vCenter  = "",
    [string] $HostFQDN = "",
    [string] $Username = "",
    [string] $Password = ""
)
Import-Module VMware.PowerCLI

#avoid unecessary output
Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP:$false -confirm:$false
#avoid certificate issues
Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false

Connect-VIServer -Server $vCenter -User $Username -Password $Password 



$VHost = Get-VMHost -Name $HostFQDN
$HSS = Get-View -Id $VHost.ExtensionData.ConfigManager.HealthStatusSystem
$HSI = $HSS.Runtime.SystemHealthInfo.NumericSensorInfo | Select Name,@{N='Health';E={$_.HealthState.Label}},CurrentReading

$XMLDetail = ""
$cntGreen = 0
$cntWarning = 0
$cntError = 0
$cntUnknown = 0

$txtWarning = ""
$txtError = ""
$txtUnknown = ""

foreach ($SI in $HSI) {
    $XMLDetail += "<result><channel>" + $SI.Name + "</channel><value>" + $SI.CurrentReading + "</value></result>"
    if ($SI.Health.ToLower() -eq "Green".ToLower()) {
        $cntGreen += 1
    } elseif ($SI.Health.ToLower() -eq "Yellow".ToLower()) {
        $cntWarning += 1
        if ($txtWarning.Length -gt 0) { 
            $txtWarning += " - " 
        }
        $txtWarning += $SI.Name
    } elseif ($SI.Health.ToLower() -eq "Red".ToLower()) {
        $cntError += 1
        if ($txtError.Length -gt 0) { 
            $txtError += " - " 
        }
        $txtError += $SI.Name
    } else {
        $cntUnknown += 1
        if ($txtUnknown.Length -gt 0) { 
            $txtUnknown += " - " 
        }
        $txtUnknown += $SI.Name
    }
}

$OverallStatus = 0
if ($cntWarning -gt 0) {
    $OverallStatus = 1
}
if ($cntError -gt 0) {
    $OverallStatus = 2
}

$OverallText = ""
if ($cntError -gt 0) {
    $OverallText = "Error status: $txtError"
}
if ($cntError -gt 0) {
    if ($OverallText.Length -gt 0) {
        $OverallText += " --- "
    }
    $OverallText = "Warning status: $txtWarning"
}
if ($cntUnknown -gt 0) {
    if ($OverallText.Length -gt 0) {
        $OverallText += " --- "
    }
    $OverallText = "Unknown status: $txtUnknown"
}

$XML = "<PRTG>"
$XML += "<result><channel>Overall Status</channel><value>$OverallStatus</value><LimitMode>1</LimitMode><LimitMaxError>1</LimitMaxError><LimitMinError>-1</LimitMinError><LimitMaxWarning>0</LimitMaxWarning></result>"
$XML += "<result><channel>Total OK</channel><value>$cntGreen</value></result>"
$XML += "<result><channel>Total Warning</channel><value>$cntWarning</value></result>"
$XML += "<result><channel>Total Error</channel><value>$cntError</value></result>"
$XML += "<result><channel>Total Uknown</channel><value>$cntUnknown</value></result>"
#$XML += $XMLDetail
#can not add the detail to PRTG cause this would create above 100 channels (likely) and PRTG has a 50 channel limit statue per sensor
$XML += "<text>$OverallText</text>"
$XML += "</PRTG>"

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

WriteXmlToScreen "$XML"

100

101

102

103

104

105

106

107

108

109

110

#pre-req

#make sure the VMware PowerCLI is installed for all users

#install-module -name vmware.powercli -AllowClobber

#make sure you set the ignore certificate error - assuming you do not have a valid cert on the vCenter

#Set-PowerCLIConfiguration -InvalidCertificateAction Ignore

param(

[string] $vCenter = "",

[string] $HostFQDN = "",

[string] $Username = "",

[string] $Password = ""

)

Import-Module VMware.PowerCLI

#avoid unecessary output

Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP:$false -confirm:$false

#avoid certificate issues

Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false

Connect-VIServer -Server $vCenter -User $Username -Password $Password

$VHost = Get-VMHost -Name $HostFQDN

$HSS = Get-View -Id $VHost.ExtensionData.ConfigManager.HealthStatusSystem

$HSI = $HSS.Runtime.SystemHealthInfo.NumericSensorInfo | Select Name,@{N='Health';E={$_.HealthState.Label}},CurrentReading

$XMLDetail = ""

$cntGreen = 0

$cntWarning = 0

$cntError = 0

$cntUnknown = 0

$txtWarning = ""

$txtError = ""

$txtUnknown = ""

foreach ($SI in $HSI) {

$XMLDetail += "<result><channel>" + $SI.Name + "</channel><value>" + $SI.CurrentReading + "</value></result>"

if ($SI.Health.ToLower() -eq "Green".ToLower()) {

$cntGreen += 1

} elseif ($SI.Health.ToLower() -eq "Yellow".ToLower()) {

$cntWarning += 1

if ($txtWarning.Length -gt 0) {

$txtWarning += " - "

}

$txtWarning += $SI.Name

} elseif ($SI.Health.ToLower() -eq "Red".ToLower()) {

$cntError += 1

if ($txtError.Length -gt 0) {

$txtError += " - "

}

$txtError += $SI.Name

} else {

$cntUnknown += 1

if ($txtUnknown.Length -gt 0) {

$txtUnknown += " - "

}

$txtUnknown += $SI.Name

}

$OverallStatus = 0

if ($cntWarning -gt 0) {

$OverallStatus = 1

}

if ($cntError -gt 0) {

$OverallStatus = 2

}

$OverallText = ""

if ($cntError -gt 0) {

$OverallText = "Error status: $txtError"

}

if ($cntError -gt 0) {

if ($OverallText.Length -gt 0) {

$OverallText += " --- "

}

$OverallText = "Warning status: $txtWarning"

}

if ($cntUnknown -gt 0) {

if ($OverallText.Length -gt 0) {

$OverallText += " --- "

}

$OverallText = "Unknown status: $txtUnknown"

}

$XML = "<PRTG>"

$XML += "<result><channel>Overall Status</channel><value>$OverallStatus</value><LimitMode>1</LimitMode><LimitMaxError>1</LimitMaxError><LimitMinError>-1</LimitMinError><LimitMaxWarning>0</LimitMaxWarning></result>"

$XML += "<result><channel>Total OK</channel><value>$cntGreen</value></result>"

$XML += "<result><channel>Total Warning</channel><value>$cntWarning</value></result>"

$XML += "<result><channel>Total Error</channel><value>$cntError</value></result>"

$XML += "<result><channel>Total Uknown</channel><value>$cntUnknown</value></result>"

#$XML += $XMLDetail

#can not add the detail to PRTG cause this would create above 100 channels (likely) and PRTG has a 50 channel limit statue per sensor

$XML += "<text>$OverallText</text>"

$XML += "</PRTG>"

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen "$XML"

August 8, 2019 by Florian Rossmark monitoring prtg server solutions

Search the Windows Security Eventlog for a string / text

Lately I had to search a lot through logs – as you can tell by all my postings… I just had to create yet another script that allows you to search through the Windows Security Eventlog – while the script is easily adjustable to other log types like application log or system log.

It’s not the most pretty script – but it certainly works. Don’t be surprised if the script takes it sweet time – it might be it needs to read through a lot of eventlog entries.

param(
    [string] $RemoteComputer = "",
    [string] $SearchString = "",
	[int] 	 $MaxAgeHours = 24,
    [bool]   $FullDetails = $false
)

$Query = @"
<QueryList>
    <Query Id="0" Path="Security">
    <Select Path="Security">*</Select>
  </Query>
</QueryList>
"@

if ($RemoteComputer.Length -gt 0){
	if ($FullDetails) {
		Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) -ComputerName $RemoteComputer |fl
	} else {
		Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) -ComputerName $RemoteComputer |ft
	}
} else {
	if ($FullDetails) {
		Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) |fl
	} else {
		Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) |ft
	}
}

param(

[string] $RemoteComputer = "",

[string] $SearchString = "",

[int] $MaxAgeHours = 24,

[bool] $FullDetails = $false

)

$Query = @"

</Query>

</QueryList>

if ($RemoteComputer.Length -gt 0){

if ($FullDetails) {

Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) -ComputerName $RemoteComputer |fl

} else {

Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) -ComputerName $RemoteComputer |ft

}

} else {

if ($FullDetails) {

Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) |fl

} else {

Get-EventLog -LogName Security -Message "*$SearchString*" -After (Get-Date).AddHours(-$MaxAgeHours) |ft

}

August 7, 2019 by Florian Rossmark scripts security server

APC InRow A/C error monitoring with PRTG

It is rather hard to get valuable alarm monitoring from an APC InRow air conditioning unit. The APC A/C’s are a real pain when it comes to this, it might even be that this same principle applies to APC UPS units, but I did not have yet time to test this out.

What I really wanted is a way to monitor alerts that the unit reports. Doing so seemed to be fine with a simple SNMP sensor in PRTG but the real challenge was getting the alert text. Now, there are SNMP channels but they are only available when an alert is ongoing, meaning when there is no alert status the whole OID fails.

To compensate this, I ended up writing a simple PowerShell script that interprets the SNMP OID results, even ignores a certain failure cause I didn’t care about it, and reports back the results as a total error count (set the channel to ErrorLimit = 0 in PRTG) and if there are Errors it will write them to the text.

This is an Advanced EXE script that needs to reside in the following path:

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML

1	C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML

It expects the parameters for community and IP-Address

public %host

1	public %host

The results of the script will always hold the top 4 error messages, but it will exclude the phrase “No Backup Units Available Alarm” from the error count – cause in certain setups like hours there are multiple units but they are not necessarily clustered – this is not a full alarm rather then a warning in my case. Feel free to adjust this in the script if you want to raise the error. You could simply remove / remark the following line:

$TotalErrors.Data = $TotalErrors.Data - $SubstractError

1	$TotalErrors.Data = $TotalErrors.Data - $SubstractError

Here a picture of a real world alarm respective issue with the APC InRow A/C in PRTG generated by the script

Param(
    $Community,
    $IP
)

$TotalErrors = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.1.0
$Error1 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.1
$Error2 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.2
$Error3 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.3
$Error4 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.4

$SubstractError = 0

if ($Error1.Data.length -gt 0) {
    if ($Error1.Data -ne "NoSuchInstance") {
        if ($Error1.Data -eq "No Backup Units Available Alarm") {    
            $SubstractError += 1
        }
        $ErrorText = $Error1.Data
    }
}
if ($Error2.Data.length -gt 0) {
    if ($Error2.Data -ne "NoSuchInstance") {
        if ($Error2.Data -eq "No Backup Units Available Alarm") {    
            $SubstractError += 1
        }
        $ErrorText += " - " + $Error2.Data
    }
}
if ($Error3.Data.length -gt 0) {
    if ($Error3.Data -ne "NoSuchInstance") {
        if ($Error3.Data -eq "No Backup Units Available Alarm") {    
            $SubstractError += 1
        }
        $ErrorText += " - " + $Error3.Data
    }
}
if ($Error4.Data.length -gt 0) {
    if ($Error4.Data -ne "NoSuchInstance") {
        if ($Error4.Data -eq "No Backup Units Available Alarm") {    
            $SubstractError += 1
        }
        $ErrorText += " - " + $Error4.Data
    }
}

$TotalErrors.Data = $TotalErrors.Data - $SubstractError

$XML =  "<prtg>"
$XML += "<result><channel>Total Errors</channel><value>" + $TotalErrors.Data + "</value><unit>Count</unit></result>"
$XML += "<text>$ErrorText</text>"
$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

WriteXmlToScreen $XML

Param(

$Community,

$IP

)

$TotalErrors = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.1.0

$Error1 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.1

$Error2 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.2

$Error3 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.3

$Error4 = Get-SnmpData -IP $IP -Community $Community -OID 1.3.6.1.4.1.318.1.1.13.4.3.2.1.3.4

$SubstractError = 0

if ($Error1.Data.length -gt 0) {

if ($Error1.Data -ne "NoSuchInstance") {

if ($Error1.Data -eq "No Backup Units Available Alarm") {

$SubstractError += 1

}

$ErrorText = $Error1.Data

}

if ($Error2.Data.length -gt 0) {

if ($Error2.Data -ne "NoSuchInstance") {

if ($Error2.Data -eq "No Backup Units Available Alarm") {

$SubstractError += 1

}

$ErrorText += " - " + $Error2.Data

}

if ($Error3.Data.length -gt 0) {

if ($Error3.Data -ne "NoSuchInstance") {

if ($Error3.Data -eq "No Backup Units Available Alarm") {

$SubstractError += 1

}

$ErrorText += " - " + $Error3.Data

}

if ($Error4.Data.length -gt 0) {

if ($Error4.Data -ne "NoSuchInstance") {

if ($Error4.Data -eq "No Backup Units Available Alarm") {

$SubstractError += 1

}

$ErrorText += " - " + $Error4.Data

}

$TotalErrors.Data = $TotalErrors.Data - $SubstractError

$XML = "<prtg>"

$XML += "<result><channel>Total Errors</channel><value>" + $TotalErrors.Data + "</value><unit>Count</unit></result>"

$XML += "<text>$ErrorText</text>"

$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen $XML

July 19, 2019 by Florian Rossmark monitoring prtg scripts

Move user Documents and Desktop to OneDrive

The PowerShell script below was design to move Documents, Music, Videos, Pictures, Favorites and Desktop to a sub-folder in a connected OneDrive. In theory the script does not depend on OneDrive and could be adjusted to any other destination.

While it normally is wise to engage GPOs to adjust those paths to internal server resources, this is not possible easily while using OneDrive. The script therefor works better here.

What it does

is the current path per folder accessible
does the target path exist
1. YES: adjust the registry respective folder targets to the target path – FINISHED
2. NO: create the target folders – see 3.
is the source path on the same volume / partition – like C:
1. YES: see below – 4.
2. NO: check if there is enough free space for the amount of data needed to be moved
  1. YES: see below – 4.
  2. ALMOST: YELLOW warning – see below 4.
  3. NO: RED error – you could still proceed or simply close the script
move the data to the new target folder
remove the old folder – if not possible rename it

The script retains the special icons for the folders and engages the Windows API to adjust the folder paths.

What you need to do

Adjust the target-path in the top of the script
If desired, adjust the minimum free space value (2 GB by default) for the warning in regards to the free space – this only matters if the source and target volume / partition aren’t the same

To start the script, either right click and say run with PowerShell or run it directly in a PowerShell. This script will need to execute in the user-context and does NOT need administrative rights.

Clear
$NewRootPath = "$env:USERPROFILE\OneDrive - MYDOMAIN\User Profile"
$regUSF = "HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders"
$regSF = "HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders" 

$Folders = "Personal,My Pictures,My Music,My Video,Favorites,Desktop"
$Folders = $Folders.Split(",")
$FolderNames = "Documents,Pictures,Music,Videos,Favorites,Desktop"
$FolderNames = $FolderNames.Split(",")

$AddSize = 2000000000 #free space in Bytes to add if files are on a different volume (2000000000 = about 2 GB)

$ArrayPosition = -1

Function Move-KnownFolderPath {
    Param (
            [Parameter(Mandatory = $true)]
            [ValidateSet('Desktop', 'Documents', 'Downloads', 'Favorites', 'Music', 'Pictures', 'Videos')]
            [string]$KnownFolder,
            [Parameter(Mandatory = $true)]
            [string]$Path
    )

    # Known Folder GUIDs
    $KnownFolders = @{
        'Desktop' = @('B4BFCC3A-DB2C-424C-B029-7FE99A87C641');
        'Documents' = @('FDD39AD0-238F-46AF-ADB4-6C85480369C7','f42ee2d3-909f-4907-8871-4c22fc0bf756');
        'Downloads' = @('374DE290-123F-4565-9164-39C4925E467B','7d83ee9b-2244-4e70-b1f5-5393042af1e4'); #not in use - just in case you want to use it..
        'Favorites' = '1777F761-68AD-4D8A-87BD-30B759FA33DD';
        'Music' = @('4BD8D571-6D19-48D3-BE97-422220080E43','a0c69a99-21c8-4671-8703-7934162fcf1d');
        'Pictures' = @('33E28130-4E1E-4676-835A-98395C3BC3BB','0ddd015d-b06c-45d5-8c4c-f59713854639');
        'Videos' = @('18989B1D-99B5-455B-841C-AB7C74E4DDFC','35286a68-3c57-41a1-bbb1-0eae73d76c95');
    }

    $FolderGUID = ([System.Management.Automation.PSTypeName]'KnownFolders').Type
	#create the Type entry if it does not yet exist / relates to the Registry eventually
    If (-not $FolderGUID) {
        $KnownFolderGUID = @'
[DllImport("shell32.dll")]
public extern static int SHSetKnownFolderPath(ref Guid folderId, uint flags, IntPtr token, [MarshalAs(UnmanagedType.LPWStr)] string path);
'@
        $FolderGUID = Add-Type -MemberDefinition $KnownFolderGUID -Name 'KnownFolders' -Namespace 'SHSetKnownFolderPath' -PassThru
    }

    If(!(Test-Path $Path)){
		#in case folder does yet exist, we create it
		Try {
			New-Item -Path $Path -Type Directory -Force -ErrorAction SilentlyContinue
		} Catch {}
    }
	#set the new folder path
	ForEach ($TmpGUID in $KnownFolders[$KnownFolder]) {
		$tmp = $FolderGUID::SHSetKnownFolderPath([ref]$TmpGUID, 0, 0, $Path)	
	}	
	Attrib +r $Path	#needed to retain the icon 
}

ForEach ($Folder In $Folders)
{	
	$ArrayPosition += 1
    Write-Host "Working on folder"$FolderNames[$ArrayPosition] -BackgroundColor DarkGreen
    $SourcePath = (Get-ItemProperty -Path $regUSF -Name $Folder).$Folder
    If ($SourcePath.Length -gt 0)
    {
		Write-Host "..checking source path: $SourcePath"
		If ((Test-Path -Path $SourcePath)){
			Write-Host "....path is accessible"
			$CompSource = Get-Item -Path $SourcePath
			$CompareResult = $false
			If ((Test-Path -Path ($NewRootPath + "\" + $CompSource.Name))){
				$CompTarget = Get-Item -Path ($NewRootPath + "\" + $CompSource.Name)
				Write-Host "..Comparing Source Path: "$CompSource.FullName
				Write-Host "....with Target Path:    "$CompTarget.FullName
				If ($CompSource.FullName.ToLower() -eq $CompTarget.FullName.ToLower()){
					$CompareResult = $true
				} Else {
					$CompareResult = $false
				}
			} Else {
				Write-Host "..Target Path does not exist"
			}
			If ($CompareResult -eq $true){
				Write-Host "..Source and Target path are identical: $CompTarget" -ForegroundColor Yellow
				Write-Host "....Not applying any changes!" -ForegroundColor Yellow
				Pause
			} Else {
				$SourceFolder = Get-Item -Path $SourcePath
				Write-Host "..accessing source folder $SourceFolder" -ForegroundColor Green
				If (!(Test-Path -Path ($NewRootPath + '\' + $SourceFolder.Name))){
					Move-KnownFolderPath -KnownFolder ("" + $FolderNames[$ArrayPosition] + "") -Path ($NewRootPath + '\' + $FolderNames[$ArrayPosition])
					$TargetPath = Get-Item -Path ("$NewRootPath\" + $FolderNames[$ArrayPosition])
					Write-Host "..created new folder $TargetPath"
					
					#compare source size with target free space here
					Write-Host "..Comparing Source and Target drive letters"
					If ($NewRootPath.SubString(0,2).ToLower() -ne $SourcePath.SubString(0,2).ToLower())
					{
						#source drives are different.. we need to obey the SourceSize against the free space on the target
						Write-Host "....Source and Target drive letters are different"
						Write-Host "......Please wait while calculating the source size"
						$SourceSize = (Get-ChildItem -Path $SourcePath -Recurse | Measure-Object -Property Length -Sum -ErrorAction SilentlyContinue).Sum
						$TargetFree = Get-PSDrive $NewRootPath.SubString(0,1) | Select-Object Free
						If ($SourceSize -gt $TargetFree.Free) {
							Write-Host "......The Source size of $SourceSize Bytes is bigger then the free space on the Target of $TargetFree.Free Bytes" -ForegroundColor Red
						} ElseIf (($SourceSize + $AddSize) -gt $TargetFree.Free) {
							Write-Host "......The Source size of $SourceSize Bytes is close to the free space on the Target of $TargetFree.Free Bytes" -ForegroundColor Yellow
						} Else {
							Write-Host "......The Source size of $TargetFree.Free Bytes is sufficient to hold the data from the Source of $SourceSize Bytes" -ForegroundColor Green
						}
						Pause
					} Else {
						Write-Host "....Source and Targets are on the same drive, all good"
					}
					
					Write-Host "..Moving data from: $SourcePath"
					Write-Host "....to folder:      $TargetPath"
					Write-Host "....This can take several minutes..."
					Move-Item -Path ("" + $SourcePath + "\*") -Destination $TargetPath -Force -ErrorAction SilentlyContinue
					Write-Host "....Done moving data"
				} Else {
					Write-Host "..Warning - Target Path exists already: $CompTarget" -ForegroundColor Yellow
					Write-Host "....Files will not be moved, registry still will be adjusted to the target path!" -ForegroundColor Yellow
					Pause
					Move-KnownFolderPath -KnownFolder ("" + $FolderNames[$ArrayPosition] + "") -Path ($NewRootPath + '\' + $FolderNames[$ArrayPosition])
					Write-Host "....Finished adjusting registry"
				}
				Write-Host "..Trying to remove $SourcePath"
				Pause
				Try {
					Attrib -r -s $SourceFolder.FullName	
					Remove-Item -Path $SourceFolder.FullName -Force -Confirm:$false -Recurse -ErrorAction SilentlyContinue
				} Catch {}	
				Try {
					Attrib -r -s ($SourceFolder + "\desktop.ini")	
					Remove-Item -Path ($SourceFolder + "\desktop.ini") -Force -Confirm:$false -ErrorAction SilentlyContinue
				} Catch {}						
				Try {
					Rename-Item -Path $SourceFolder -NewName ($SourceFolder.Name + ".old") -Force -ErrorAction SilentlyContinue
				} Catch {}		
			}
		} Else {
			Write-Host "....SourceFolder Path invalid: $SourcePath" -ForegroundColor Red
		}
    } 
	Write-Host "..Done processing"$FolderNames[$ArrayPosition] -BackgroundColor Blue
	Pause
}
Write-Host "Script finished processing" -ForegroundColor Green
Pause

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

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

Clear

$NewRootPath = "$env:USERPROFILE\OneDrive - MYDOMAIN\User Profile"

$regUSF = "HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders"

$regSF = "HKCU:\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders"

$Folders = "Personal,My Pictures,My Music,My Video,Favorites,Desktop"

$Folders = $Folders.Split(",")

$FolderNames = "Documents,Pictures,Music,Videos,Favorites,Desktop"

$FolderNames = $FolderNames.Split(",")

$AddSize = 2000000000 #free space in Bytes to add if files are on a different volume (2000000000 = about 2 GB)

$ArrayPosition = -1

Function Move-KnownFolderPath {

Param (

[Parameter(Mandatory = $true)]

[ValidateSet('Desktop', 'Documents', 'Downloads', 'Favorites', 'Music', 'Pictures', 'Videos')]

[string]$KnownFolder,

[Parameter(Mandatory = $true)]

[string]$Path

)

# Known Folder GUIDs

$KnownFolders = @{

'Desktop' = @('B4BFCC3A-DB2C-424C-B029-7FE99A87C641');

'Documents' = @('FDD39AD0-238F-46AF-ADB4-6C85480369C7','f42ee2d3-909f-4907-8871-4c22fc0bf756');

'Downloads' = @('374DE290-123F-4565-9164-39C4925E467B','7d83ee9b-2244-4e70-b1f5-5393042af1e4'); #not in use - just in case you want to use it..

'Favorites' = '1777F761-68AD-4D8A-87BD-30B759FA33DD';

'Music' = @('4BD8D571-6D19-48D3-BE97-422220080E43','a0c69a99-21c8-4671-8703-7934162fcf1d');

'Pictures' = @('33E28130-4E1E-4676-835A-98395C3BC3BB','0ddd015d-b06c-45d5-8c4c-f59713854639');

'Videos' = @('18989B1D-99B5-455B-841C-AB7C74E4DDFC','35286a68-3c57-41a1-bbb1-0eae73d76c95');

}

$FolderGUID = ([System.Management.Automation.PSTypeName]'KnownFolders').Type

#create the Type entry if it does not yet exist / relates to the Registry eventually

If (-not $FolderGUID) {

$KnownFolderGUID = @'

[DllImport("shell32.dll")]

public extern static int SHSetKnownFolderPath(ref Guid folderId, uint flags, IntPtr token, [MarshalAs(UnmanagedType.LPWStr)] string path);

$FolderGUID = Add-Type -MemberDefinition $KnownFolderGUID -Name 'KnownFolders' -Namespace 'SHSetKnownFolderPath' -PassThru

}

If(!(Test-Path $Path)){

#in case folder does yet exist, we create it

Try {

New-Item -Path $Path -Type Directory -Force -ErrorAction SilentlyContinue

} Catch {}

}

#set the new folder path

ForEach ($TmpGUID in $KnownFolders[$KnownFolder]) {

$tmp = $FolderGUID::SHSetKnownFolderPath([ref]$TmpGUID, 0, 0, $Path)

}

Attrib +r $Path #needed to retain the icon

}

ForEach ($Folder In $Folders)

{

$ArrayPosition += 1

Write-Host "Working on folder"$FolderNames[$ArrayPosition] -BackgroundColor DarkGreen

$SourcePath = (Get-ItemProperty -Path $regUSF -Name $Folder).$Folder

If ($SourcePath.Length -gt 0)

{

Write-Host "..checking source path: $SourcePath"

If ((Test-Path -Path $SourcePath)){

Write-Host "....path is accessible"

$CompSource = Get-Item -Path $SourcePath

$CompareResult = $false

If ((Test-Path -Path ($NewRootPath + "\" + $CompSource.Name))){

$CompTarget = Get-Item -Path ($NewRootPath + "\" + $CompSource.Name)

Write-Host "..Comparing Source Path: "$CompSource.FullName

Write-Host "....with Target Path: "$CompTarget.FullName

If ($CompSource.FullName.ToLower() -eq $CompTarget.FullName.ToLower()){

$CompareResult = $true

} Else {

$CompareResult = $false

}

} Else {

Write-Host "..Target Path does not exist"

}

If ($CompareResult -eq $true){

Write-Host "..Source and Target path are identical: $CompTarget" -ForegroundColor Yellow

Write-Host "....Not applying any changes!" -ForegroundColor Yellow

Pause

} Else {

$SourceFolder = Get-Item -Path $SourcePath

Write-Host "..accessing source folder $SourceFolder" -ForegroundColor Green

If (!(Test-Path -Path ($NewRootPath + '\' + $SourceFolder.Name))){

Move-KnownFolderPath -KnownFolder ("" + $FolderNames[$ArrayPosition] + "") -Path ($NewRootPath + '\' + $FolderNames[$ArrayPosition])

$TargetPath = Get-Item -Path ("$NewRootPath\" + $FolderNames[$ArrayPosition])

Write-Host "..created new folder $TargetPath"

#compare source size with target free space here

Write-Host "..Comparing Source and Target drive letters"

If ($NewRootPath.SubString(0,2).ToLower() -ne $SourcePath.SubString(0,2).ToLower())

{

#source drives are different.. we need to obey the SourceSize against the free space on the target

Write-Host "....Source and Target drive letters are different"

Write-Host "......Please wait while calculating the source size"

$SourceSize = (Get-ChildItem -Path $SourcePath -Recurse | Measure-Object -Property Length -Sum -ErrorAction SilentlyContinue).Sum

$TargetFree = Get-PSDrive $NewRootPath.SubString(0,1) | Select-Object Free

If ($SourceSize -gt $TargetFree.Free) {

Write-Host "......The Source size of $SourceSize Bytes is bigger then the free space on the Target of $TargetFree.Free Bytes" -ForegroundColor Red

} ElseIf (($SourceSize + $AddSize) -gt $TargetFree.Free) {

Write-Host "......The Source size of $SourceSize Bytes is close to the free space on the Target of $TargetFree.Free Bytes" -ForegroundColor Yellow

} Else {

Write-Host "......The Source size of $TargetFree.Free Bytes is sufficient to hold the data from the Source of $SourceSize Bytes" -ForegroundColor Green

}

Pause

} Else {

Write-Host "....Source and Targets are on the same drive, all good"

}

Write-Host "..Moving data from: $SourcePath"

Write-Host "....to folder: $TargetPath"

Write-Host "....This can take several minutes..."

Move-Item -Path ("" + $SourcePath + "\*") -Destination $TargetPath -Force -ErrorAction SilentlyContinue

Write-Host "....Done moving data"

} Else {

Write-Host "..Warning - Target Path exists already: $CompTarget" -ForegroundColor Yellow

Write-Host "....Files will not be moved, registry still will be adjusted to the target path!" -ForegroundColor Yellow

Pause

Move-KnownFolderPath -KnownFolder ("" + $FolderNames[$ArrayPosition] + "") -Path ($NewRootPath + '\' + $FolderNames[$ArrayPosition])

Write-Host "....Finished adjusting registry"

}

Write-Host "..Trying to remove $SourcePath"

Pause

Try {

Attrib -r -s $SourceFolder.FullName

Remove-Item -Path $SourceFolder.FullName -Force -Confirm:$false -Recurse -ErrorAction SilentlyContinue

} Catch {}

Try {

Attrib -r -s ($SourceFolder + "\desktop.ini")

Remove-Item -Path ($SourceFolder + "\desktop.ini") -Force -Confirm:$false -ErrorAction SilentlyContinue

} Catch {}

Try {

Rename-Item -Path $SourceFolder -NewName ($SourceFolder.Name + ".old") -Force -ErrorAction SilentlyContinue

} Catch {}

}

} Else {

Write-Host "....SourceFolder Path invalid: $SourcePath" -ForegroundColor Red

}

Write-Host "..Done processing"$FolderNames[$ArrayPosition] -BackgroundColor Blue

Pause

}

Write-Host "Script finished processing" -ForegroundColor Green

Pause

Please be advised – the script will by default not try to move e.g. DOWNLOADS.

You can adjust this, while adding the folder to the two parameter, see sample below.

$Folders = "Personal,My Pictures,My Music,My Video,Favorites,Desktop<strong>,Downloads</strong>" 
$Folders = $Folders.Split(",") 
$FolderNames = "Documents,Pictures,Music,Videos,Favorites,Desktop<strong>,Downloads</strong>" 
$FolderNames = $FolderNames.Split(",")

$Folders = "Personal,My Pictures,My Music,My Video,Favorites,Desktop<strong>,Downloads</strong>"

$Folders = $Folders.Split(",")

$FolderNames = "Documents,Pictures,Music,Videos,Favorites,Desktop<strong>,Downloads</strong>"

$FolderNames = $FolderNames.Split(",")

If you want more folder, the script would need some special adjustments. It can be used as a base script, if you want.

April 18, 2019 by Florian Rossmark o365 / m365 recommendations scripts windows

Raspberry PI and Microsoft SQL databases

Raspberry PI can read and write on a Microsoft SQL server database.

In order to accomplish this you follow the instructions here: http://pymssql.org/en/stable/index.html

To summarize it in a nutshell, here is what you need to do:

apt-get install freetds-dev
pip install pymssql

Update: Above information is for a Raspberry 2 – Raspberry 3 needs the below information as far as I know:

sudo apt-get install freetds-dev
sudo pip3 install cython
sudo pip3 install pymssql

Personally I had issues getting this to work in Python 3.x so I tested it in Python 2.x and it was working fine. The issue was simply that the module “pymssql” could not be found and therefor the IMPORT line already failed in the Python script. It should be a rather easy fix – like copying the files to the Python 3 modules folder, but as of now I did not have the time to investigate this further – as I was fine using Python 2 in my specific situation.

Here is a sample script

import pymssql

server = "mySQLservername"
user = "myuser"
password = "mypassword"
DB = "mydatabase"

conn = pymssql.connect(server, user, password, DB)
cur = conn.cursor()
cur.execute('SELECT * FROM MyTable')
  for row in cur:
    #print(row['id'])
    print(row[0])
conn.close

import pymssql

server = "mySQLservername"

user = "myuser"

password = "mypassword"

DB = "mydatabase"

conn = pymssql.connect(server, user, password, DB)

cur = conn.cursor()

cur.execute('SELECT * FROM MyTable')

for row in cur:

#print(row['id'])

print(row[0])

conn.close

The example I tested used a SQL server user account. The documentation of PyMSSQL talks about the possibility to use Windows Authentication as well.

As for asking Google about this – there is a lot of confusion information out there – the top ranked posts aren’t really helpful, so I thought I just post it again hoping someone finds this helpful.

January 31, 2019 by Florian Rossmark configuration solutions

Monitor the total amount of sessions on your RDS farm

ins a website from a systems administrator for systems administrators Home IT-Admins CMDB IT-Admins tool IT Search EOL Solutions Blog Contact Links Monitor the total amount of sessions on your RDS farm

This script is designed for PRTG and will allow you to go through all your RDS hosts and result back the total amount of sessions and active sessions.

You have various options as server name source, see the parameter section on top of the script.

This was also posted here: https://kb.paessler.com/en/topic/83151-total-user-count-rds-windows-2016

Please note that I grabbed the original script and re-wrote it completely, adjust some issues I encountered and tried to make it as variable as possible.

param(
    $ServerNameSource, #Numeric script config parameter: 1 = Parameter ServerNames list / 2 = Parameter InputFile / 3 = LDAP query with name-prefix / 4 = LDAP query with filter on OU / 5 = LDAP query with name-prefix and OU filter
    $ServerNames, #depending on ServerNameSource (1) - a simple, coma separated list of server names e.g. @("server1", "server2", "server3")
    $ServerNamesInputFile, #depending on ServerNameSource (2) - path and name of a file that holds the server names, one servername per line - e.g.: "C:\servernames.txt"
    $ServerNamesLDAPnameFilter, #depending on ServerNameSource (3 and/or 5) - name search string - can hold wildcards (*) e.g.: "RDS*"
    $ServerNamesLDAPOUFilter, #depending on ServerNameSource (4 and/or 5) - e.g.: "OU=RDS hosts,DC=domain,DC=local"
    $SubstractSessionsPerHost = 2, #substract this amount of sessions per host - due to e.g. two RDS listeners (would make 2 sessions) or additional e.g. 1x ICA listener (would make 3 sessions) etc...
    $UseWMI = $false #use WMI - if not set it will use QWINSTA by default: enable with: $true
)

#function to convert a string to proper XML and write it as output/screen
Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

$ServerNameList;

If ($ServerNameSource -gt 0 -and $ServerNameSource -lt 6) {

    Switch ($ServerNameSource) {
        1 {
            $ServerNameList = $ServerNames;
        }
        2 {
            $ServerNameList = Get-Content $ServerNamesInputFile;
        }
        3 {
            $ServerNameList = (Get-ADComputer -LDAPFilter "(name=$ServerNamesLDAPnameFilter)" | Select Name).Name;
        }
        4 {
            $ServerNameList = (Get-ADComputer -Filter "*" -SearchBase $ServerNamesLDAPOUFilter | Select Name).Name;
        }
        5 {
            $ServerNameList = (Get-ADComputer -LDAPFilter "(name=$ServerNamesLDAPnameFilter)" -SearchBase $ServerNamesLDAPOUFilter | Select Name).Name;
        }
    }
} Else {
    Write-Output "Missing Parameter or wrong value for: ServerNameSource";
    Exit;
}
  
#if the array holds entries
If ($ServerNameList.Count -gt 0) {
 
    $SessionsTotal = 0;
    $SessionsActive = 0;
    $ServersNotReached = 0;

    #go through the list
    ForEach ($ServerName in $ServerNameList) {
        #try to connect - on error jump to catch
        Try {
            $FoundTotal = 0;
            $FoundActive = 0;
            
            
            If ($UseWMI -eq $false) {
                #less control in theory - due to reformatting and searching through the results
                #single execution of the query / command
                $QWinstaResults = qwinsta /server $ServerName | ForEach-Object {
                        $_.Trim() -replace "\s+",","
                    } | ConvertFrom-Csv -Header "SessionName","UserName","ID","State","Type","Device"; #we add manual headers here, since this otherwise can cause issues with the filter later on
                $FoundTotal = $QWinstaResults.Count-1;  #if we use QWINSTA we need to substract one more session from the results
                $FoundActive = ($QWinstaResults | ? { $_.State -eq "Active" }).Count;
            } else {            
                #more control - but might run slower
                #in theory you can invoke all server-names at once, but then you need to run through the table and you lose some control over what was reached and what wasn't - first run might always be slowest depending on amount of servers
                $FoundTotal = (Get-WmiObject -Query "SELECT TotalSessions FROM Win32_TerminalService" -ComputerName $ServerName | Select TotalSessions).TotalSessions;
                $FoundActive = (Get-WmiObject -Query "SELECT ActiveSessions FROM Win32_PerfFormattedData_LocalSessionManager_TerminalServices" -ComputerName $ServerName | Select ActiveSessions).ActiveSessions;
            }

            #SessionsTotal might need a substract depending on the parameter input
            $FoundTotal = $FoundTotal - $SubstractSessionsPerHost;
            #we need to avoid that the substractions is negative, to avoid false substraction
            If ($FoundTotal -le 0){
                $FoundTotal = 0;
            }
            
            #we now add the results to the current counters
            $SessionsTotal += $FoundTotal;
            $SessionsActive += $FoundActive;
        } Catch {
            $ServersNotReached += 1;
        } Finally {
            #nothing to do here
        }
    }

    #Lets put together the results
    $PRTGstring="<prtg>
        <result>
	    <channel>Total Servers checked</channel>
        <value>" +  $ServerNameList.Count + "</value>
	    </result>
        <result>
	    <channel>Total Servers responded</channel>
        <value>" + ($ServerNameList.Count - $ServersNotReached)  + "</value>
	    </result>
        <result>
	    <channel>Total Servers not reached</channel>
        <value>$ServersNotReached</value>
	    </result>
        <result>
	    <channel>Total Sessions found</channel>
        <value>$SessionsTotal</value>
	    </result>
        <result>
	    <channel>Total Sessions active</channel>
        <value>$SessionsActive</value>
	    </result>
        </prtg>"

    #we call the function WriteXmlToScreen to transform the $PRTGstring to XML and output it 
    WriteXmlToScreen "$PRTGstring"

} Else {
    Write-Output "Parameter Error or List did not contain any names / no server names found"
    Exit;
}

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

param(

$ServerNameSource, #Numeric script config parameter: 1 = Parameter ServerNames list / 2 = Parameter InputFile / 3 = LDAP query with name-prefix / 4 = LDAP query with filter on OU / 5 = LDAP query with name-prefix and OU filter

$ServerNames, #depending on ServerNameSource (1) - a simple, coma separated list of server names e.g. @("server1", "server2", "server3")

$ServerNamesInputFile, #depending on ServerNameSource (2) - path and name of a file that holds the server names, one servername per line - e.g.: "C:\servernames.txt"

$ServerNamesLDAPnameFilter, #depending on ServerNameSource (3 and/or 5) - name search string - can hold wildcards (*) e.g.: "RDS*"

$ServerNamesLDAPOUFilter, #depending on ServerNameSource (4 and/or 5) - e.g.: "OU=RDS hosts,DC=domain,DC=local"

$SubstractSessionsPerHost = 2, #substract this amount of sessions per host - due to e.g. two RDS listeners (would make 2 sessions) or additional e.g. 1x ICA listener (would make 3 sessions) etc...

$UseWMI = $false #use WMI - if not set it will use QWINSTA by default: enable with: $true

)

#function to convert a string to proper XML and write it as output/screen

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

$ServerNameList;

If ($ServerNameSource -gt 0 -and $ServerNameSource -lt 6) {

Switch ($ServerNameSource) {

1 {

$ServerNameList = $ServerNames;

}

2 {

$ServerNameList = Get-Content $ServerNamesInputFile;

}

3 {

$ServerNameList = (Get-ADComputer -LDAPFilter "(name=$ServerNamesLDAPnameFilter)" | Select Name).Name;

}

4 {

$ServerNameList = (Get-ADComputer -Filter "*" -SearchBase $ServerNamesLDAPOUFilter | Select Name).Name;

}

5 {

$ServerNameList = (Get-ADComputer -LDAPFilter "(name=$ServerNamesLDAPnameFilter)" -SearchBase $ServerNamesLDAPOUFilter | Select Name).Name;

}

} Else {

Write-Output "Missing Parameter or wrong value for: ServerNameSource";

Exit;

}

#if the array holds entries

If ($ServerNameList.Count -gt 0) {

$SessionsTotal = 0;

$SessionsActive = 0;

$ServersNotReached = 0;

#go through the list

ForEach ($ServerName in $ServerNameList) {

#try to connect - on error jump to catch

Try {

$FoundTotal = 0;

$FoundActive = 0;

If ($UseWMI -eq $false) {

#less control in theory - due to reformatting and searching through the results

#single execution of the query / command

$QWinstaResults = qwinsta /server $ServerName | ForEach-Object {

$_.Trim() -replace "\s+",","

} | ConvertFrom-Csv -Header "SessionName","UserName","ID","State","Type","Device"; #we add manual headers here, since this otherwise can cause issues with the filter later on

$FoundTotal = $QWinstaResults.Count-1; #if we use QWINSTA we need to substract one more session from the results

$FoundActive = ($QWinstaResults | ? { $_.State -eq "Active" }).Count;

} else {

#more control - but might run slower

#in theory you can invoke all server-names at once, but then you need to run through the table and you lose some control over what was reached and what wasn't - first run might always be slowest depending on amount of servers

$FoundTotal = (Get-WmiObject -Query "SELECT TotalSessions FROM Win32_TerminalService" -ComputerName $ServerName | Select TotalSessions).TotalSessions;

$FoundActive = (Get-WmiObject -Query "SELECT ActiveSessions FROM Win32_PerfFormattedData_LocalSessionManager_TerminalServices" -ComputerName $ServerName | Select ActiveSessions).ActiveSessions;

}

#SessionsTotal might need a substract depending on the parameter input

$FoundTotal = $FoundTotal - $SubstractSessionsPerHost;

#we need to avoid that the substractions is negative, to avoid false substraction

If ($FoundTotal -le 0){

$FoundTotal = 0;

}

#we now add the results to the current counters

$SessionsTotal += $FoundTotal;

$SessionsActive += $FoundActive;

} Catch {

$ServersNotReached += 1;

} Finally {

#nothing to do here

}

#Lets put together the results

$PRTGstring="<prtg>

<channel>Total Servers checked</channel>

<value>" + $ServerNameList.Count + "</value>

</result>

<channel>Total Servers responded</channel>

<value>" + ($ServerNameList.Count - $ServersNotReached) + "</value>

</result>

<channel>Total Servers not reached</channel>

<value>$ServersNotReached</value>

</result>

<channel>Total Sessions found</channel>

<value>$SessionsTotal</value>

</result>

<channel>Total Sessions active</channel>

<value>$SessionsActive</value>

</result>

</prtg>"

#we call the function WriteXmlToScreen to transform the $PRTGstring to XML and output it

WriteXmlToScreen "$PRTGstring"

} Else {

Write-Output "Parameter Error or List did not contain any names / no server names found"

Exit;

}

January 14, 2019 by Florian Rossmark monitoring prtg scripts server

VMware alert monitoring with PRTG and PowerShell

There is a way to read out and process ALL alerts of your VMware environment using PowerShell and reporting the results back to PRTG. The script further down in this article does this. What you get is similar to the graphic here.

This show you the following channels:

Overall status
- this will be green as long there aren’t any not acknowledged warnings or alerts in VMware
- if the warning or alert is acknowledged, the sensor / script will return to green cause it is nothing that is new
Total Alerts – amount of alerts acknowledged and not ackowledged
Total Alerts – Acknowledged
Total Alerts – NOT Acknowledged
Total Warnings
Total Warnings – Acknowledged
Total Warnings – NOT Acknowledged
Total Warnings and Alerts
Total Warnings and Alerts – Acknowledged
Total Warnings and Alerts – NOT Acknowledged

As you can see – you can get more granular on your PRTG statuses if you use the channels for Warnings/Alerts that are acknowledged. You could set upper warning or error limits of 0 to keep a warning / error level in PRTG if you want to see them still.

While I was writing the script, I decided to create a new lookup value in PRTG to make it more clear. If you adjust the script in regards to add additional statuses for the channel overall status – you will need to adjust this file as well.

Let’s start with the value lookup file, you need to copy the text from the first script block in to a file you store here: C:\Program Files (x86)\PRTG Network Monitor\lookups\custom

Name the file: vmware.alerts.search.ovl

<?xml version="1.0" encoding="utf-8"?>
  <ValueLookup id="vmware.alerts.search" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd" undefinedState="Warning">
    <Lookups>
      <SingleInt state="Ok" value="0">Overall Status - Green - OK</SingleInt>
      <SingleInt state="Warning" value="1">Overall Status - Yellow - Warning</SingleInt>
      <SingleInt state="Error" value="2">Overall Status - Red - Error</SingleInt>
    </Lookups>
  </ValueLookup>

<?xml version="1.0" encoding="utf-8"?>

<SingleInt state="Ok" value="0">Overall Status - Green - OK</SingleInt>

<SingleInt state="Warning" value="1">Overall Status - Yellow - Warning</SingleInt>

<SingleInt state="Error" value="2">Overall Status - Red - Error</SingleInt>

</Lookups>

</ValueLookup>

Now we need to create a custom EXE/XML sensor in this directory: C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML

Name the file: VMwareAlerts.ps1

#make sure the executing user and system has those two commands run
#Find-Module -Name VMware.PowerCLI
#Install-Module -Name VMware.PowerCLI
#useful command 
#Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP:$false -confirm:$false
#Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false
#
#alternative look here: https://blogs.vmware.com/PowerCLI/2017/04/powercli-install-process-powershell-gallery.html

param(
    [string] $VCenterServer = "",
	[string] $DomainAndUser = "",
	[string] $Password = ""
)

Import-Module VMware.PowerCLI

#avoid unecessary output
Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP:$false -confirm:$false
#avoid certificate issues
Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false
Connect-VIServer $VCenterServer -username "$DomainAndUser" -Password "$Password"
$colAlarms = (Get-DataCenter).ExtensionData.TriggeredAlarmState

$XML = "<PRTG>"

[int] $Warnings = 0
[int] $WarningsAck = 0
[int] $Alerts = 0
[int] $AlertsAck = 0
[int] $OverallStatus = 0 #default green

if ($colAlarms.Count -gt 0) {
    foreach ($entry in $colAlarms) {
        if ($entry.OverallStatus -eq "yellow") {
            $Warnings += 1
            if ($entry.Acknowledged) {
                $WarningsAck =+ 1
            } else { #we only increase the overall status if this alert is not acknowledged
                if ($OverallStatus -lt 1)
                {
                    $OverallStatus = 1
                }
            }
        } elseif ($entry.OverallStatus -eq "red") {
            $Alerts += 1
            if ($entry.Acknowledged) {
                $AlertsAck =+ 1
            } else { #we only increase the overall status if this alert is not acknowledged
                if ($OverallStatus -lt 2)
                {
                    $OverallStatus = 2
                }
            }
        }
    }
}

$XML += "<result><channel>Overwall Status</channel><value>" + $OverallStatus + "</value><ValueLookup>vmware.alerts.search</ValueLookup></result>"
$XML += "<result><channel>Total Warnings and Alerts</channel><value>" + $colAlarms.Count + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Warnings and Alerts - NOT Acknowledged</channel><value>" + ($colAlarms.Count - $WarningsAck + $AlertsAck) + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Warnings and Alerts - Acknowledged</channel><value>" + ($WarningsAck + $AlertsAck) + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Warnings</channel><value>" + $Warnings + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Warnings - NOT Acknowledged</channel><value>" + ($Warnings - $WarningsAck) + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Warnings - Acknowledged</channel><value>" + $WarningsAck + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Alerts</channel><value>" + $Alerts + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Alerts - NOT Acknowledged</channel><value>" + ($Alerts - $AlertsAck) + "</value><CustomUnit>#</CustomUnit></result>"
$XML += "<result><channel>Total Alerts - Acknowledged</channel><value>" + $AlertsAck + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "</PRTG>"


Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}
#Clear
WriteXmlToScreen "$XML"

#make sure the executing user and system has those two commands run

#Find-Module -Name VMware.PowerCLI

#Install-Module -Name VMware.PowerCLI

#useful command

#Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP:$false -confirm:$false

#Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false

#alternative look here: https://blogs.vmware.com/PowerCLI/2017/04/powercli-install-process-powershell-gallery.html

param(

[string] $VCenterServer = "",

[string] $DomainAndUser = "",

[string] $Password = ""

)

Import-Module VMware.PowerCLI

#avoid unecessary output

Set-PowerCLIConfiguration -Scope User -ParticipateInCEIP:$false -confirm:$false

#avoid certificate issues

Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false

Connect-VIServer $VCenterServer -username "$DomainAndUser" -Password "$Password"

$colAlarms = (Get-DataCenter).ExtensionData.TriggeredAlarmState

$XML = "<PRTG>"

[int] $Warnings = 0

[int] $WarningsAck = 0

[int] $Alerts = 0

[int] $AlertsAck = 0

[int] $OverallStatus = 0 #default green

if ($colAlarms.Count -gt 0) {

foreach ($entry in $colAlarms) {

if ($entry.OverallStatus -eq "yellow") {

$Warnings += 1

if ($entry.Acknowledged) {

$WarningsAck =+ 1

} else { #we only increase the overall status if this alert is not acknowledged

if ($OverallStatus -lt 1)

{

$OverallStatus = 1

}

} elseif ($entry.OverallStatus -eq "red") {

$Alerts += 1

if ($entry.Acknowledged) {

$AlertsAck =+ 1

} else { #we only increase the overall status if this alert is not acknowledged

if ($OverallStatus -lt 2)

{

$OverallStatus = 2

}

$XML += "<result><channel>Overwall Status</channel><value>" + $OverallStatus + "</value><ValueLookup>vmware.alerts.search</ValueLookup></result>"

$XML += "<result><channel>Total Warnings and Alerts</channel><value>" + $colAlarms.Count + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Warnings and Alerts - NOT Acknowledged</channel><value>" + ($colAlarms.Count - $WarningsAck + $AlertsAck) + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Warnings and Alerts - Acknowledged</channel><value>" + ($WarningsAck + $AlertsAck) + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Warnings</channel><value>" + $Warnings + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Warnings - NOT Acknowledged</channel><value>" + ($Warnings - $WarningsAck) + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Warnings - Acknowledged</channel><value>" + $WarningsAck + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Alerts</channel><value>" + $Alerts + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Alerts - NOT Acknowledged</channel><value>" + ($Alerts - $AlertsAck) + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "<result><channel>Total Alerts - Acknowledged</channel><value>" + $AlertsAck + "</value><CustomUnit>#</CustomUnit></result>"

$XML += "</PRTG>"

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

#Clear

WriteXmlToScreen "$XML"

Once you have both files created, go to PRTG and add a new sensor called EXE/Script Advanced and select the new created script file. ~~As Parameter you either type the host-name of your vSphere server or if you created it underneath the device in PRTG just use %host.~~

UPDATE: I changed the script cause I found it to be better to go with the following expected parameters and always making sure you have control over username and password used to connect to VMware. Please use the follow parameter moving forward:

<strong>%host "%windowsdomain\%windowsuser" "%windowspassword"</strong>

1	<strong>%host "%windowsdomain\%windowsuser" "%windowspassword"</strong>

There are still a few challenges you might need to overcome on top of this:

install the VMware PowerShell extensions on your PRTG probe server
- in a PowerShell execute
  - Find-Module -Name VMware.PowerCLI
  - Install-Module -Name VMware.PowerCLI
- alternative use the documentation mentioned in the next link
  - https://blogs.vmware.com/PowerCLI/2017/04/powercli-install-process-powershell-gallery.html
~~credentials to connect to VMware can be a challenge as I tested this~~
- ~~you might need to have the service account of the PRTG probe have sufficient access rights – needs working SSO~~
- ~~alternative use a stored credentials file in PowerShell – somewhat secure~~
- ~~or provide the credentials clear text in PowerShell – least secure~~
- ~~please see line 20 respective the command “connect-viserver” for more details~~
updated the script – it now expects username and password as parameter

You might wanna test the script before you add a sensor to PRTG – the best way to do this is directly on the PRTG server with the service account of the PRTG probe to make sure it will work as a sensor later on.

Keep in mind that the script expects a parameter – the VMware vSphere server name / web-address.

This was also posted on the PRTG KB here.

December 3, 2018 by Florian Rossmark monitoring prtg scripts server solutions

Monitor multiple website certificates with a single PRTG sensor

Due to a request on the PRTG KB of someone needing a single sensor that monitors multiple URLs for their certificate expiration I came up with the following script that is posted on this PRTG KB as well. The modified PowerShell script was provided there – it is mentioned it sourced from Stack Overflow – I found it on this link: https://stackoverflow.com/questions/28386579/modifying-ssl-cert-check-powershell-script-to-loop-through-multiple-sites

The result would look like this:

<PRTG>
  <result>
    <channel>google.com</channel>
    <value>66</value>
    <CustomUnit>days</CustomUnit>
    <LimitMinWarning>30</LimitMinWarning>
    <LimitMinError>5</LimitMinError>
  </result>
  <result>
    <channel>aol.com</channel>
    <value>143</value>
    <CustomUnit>days</CustomUnit>
    <LimitMinWarning>30</LimitMinWarning>
    <LimitMinError>5</LimitMinError>
  </result>
  <result>
    <channel>yahoo.com</channel>
    <value>96</value>
    <CustomUnit>days</CustomUnit>
    <LimitMinWarning>30</LimitMinWarning>
    <LimitMinError>5</LimitMinError>
  </result>
  <result>
    <channel>espn.com</channel>
    <value>-999</value>
    <CustomUnit>days</CustomUnit>
    <LimitMinWarning>30</LimitMinWarning>
    <LimitMinError>5</LimitMinError>
  </result>
  <text>Not reached pages: espn.com</text>
</PRTG>

<PRTG>

<channel>google.com</channel>

</result>

</result>

<channel>yahoo.com</channel>

</result>

</result>

<text>Not reached pages: espn.com</text>

</PRTG>

To make it more usable – you can input parameters from PRTG like this:

@("domain.com","domain2.com","domain3.com")

1	@("domain.com","domain2.com","domain3.com")

or this for limits – warning 60 and error 10 – you could name them but this should work as well…

@("domain.com","domain2.com","domain3.com") 60 10

1	@("domain.com","domain2.com","domain3.com") 60 10

And here is the modified script:

param(
    $WebsiteURLs= @("google.com","aol.com","yahoo.com","espn.com"),
    $LimitMinDaysWarning=30,
    $LimitMinDaysError=5
)

$WebsitePort=443
$Threshold=120
$Severe=30
$ID=0

$XML = "<PRTG>"
$TextError = ""
foreach ($WebsiteURL in $WebsiteURLs){
    $CommonName=$WebsiteURL
    $ID+=1
    Try{
        $Conn = New-Object System.Net.Sockets.TcpClient($WebsiteURL,$WebsitePort) 
        Try {
            $Stream = New-Object System.Net.Security.SslStream($Conn.GetStream(),$false, {
                param($sender, $certificate, $chain, $sslPolicyErrors) 
                return $true
            })
            $Stream.AuthenticateAsClient($CommonName) 

            $Cert = $Stream.Get_RemoteCertificate()
            $CN=(($cert.Subject -split "=")[1] -split ",")[0]
            $ValidTo = [datetime]::Parse($Cert.GetExpirationDatestring())

            $ValidDays = $($ValidTo - [datetime]::Now).Days
            $MyFontColor="darkgreen"
            if ($ValidDays -lt $Threshold) {
                $MyFontColor="darkyellow"
            } 
            if ($ValidDays -lt $Severe) {
                $MyFontColor="red"
            }
            $XML += "<result><channel>$WebsiteURL</channel><value>$ValidDays</value><CustomUnit>days</CustomUnit><LimitMinWarning>$LimitMinDaysWarning</LimitMinWarning><LimitMinError>$LimitMinDaysError</LimitMinError></result>"
        } Catch { 
            Throw $_ 
        } Finally { 
            $Conn.close() 
        }
    } Catch {
        $XML += "<result><channel>$WebsiteURL</channel><value>-999</value><CustomUnit>days</CustomUnit><LimitMinWarning>$LimitMinDaysWarning</LimitMinWarning><LimitMinError>$LimitMinDaysError</LimitMinError></result>"
        If ($TextError.Length -gt 0) {
            $TextError += " / "
        }
        $TextError += "$WebsiteURL"
    }
}
If ($TextError.Length -gt 0) {
    $XML += "<text>Not reached pages: $TextError</text>"
}
$XML += "</PRTG>"

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}
WriteXmlToScreen "$XML"

param(

$WebsiteURLs= @("google.com","aol.com","yahoo.com","espn.com"),

$LimitMinDaysWarning=30,

$LimitMinDaysError=5

)

$WebsitePort=443

$Threshold=120

$Severe=30

$ID=0

$XML = "<PRTG>"

$TextError = ""

foreach ($WebsiteURL in $WebsiteURLs){

$CommonName=$WebsiteURL

$ID+=1

Try{

$Conn = New-Object System.Net.Sockets.TcpClient($WebsiteURL,$WebsitePort)

Try {

$Stream = New-Object System.Net.Security.SslStream($Conn.GetStream(),$false, {

param($sender, $certificate, $chain, $sslPolicyErrors)

return $true

})

$Stream.AuthenticateAsClient($CommonName)

$Cert = $Stream.Get_RemoteCertificate()

$CN=(($cert.Subject -split "=")[1] -split ",")[0]

$ValidTo = [datetime]::Parse($Cert.GetExpirationDatestring())

$ValidDays = $($ValidTo - [datetime]::Now).Days

$MyFontColor="darkgreen"

if ($ValidDays -lt $Threshold) {

$MyFontColor="darkyellow"

}

if ($ValidDays -lt $Severe) {

$MyFontColor="red"

}

$XML += "<result><channel>$WebsiteURL</channel><value>$ValidDays</value><CustomUnit>days</CustomUnit><LimitMinWarning>$LimitMinDaysWarning</LimitMinWarning><LimitMinError>$LimitMinDaysError</LimitMinError></result>"

} Catch {

Throw $_

} Finally {

$Conn.close()

}

} Catch {

$XML += "<result><channel>$WebsiteURL</channel><value>-999</value><CustomUnit>days</CustomUnit><LimitMinWarning>$LimitMinDaysWarning</LimitMinWarning><LimitMinError>$LimitMinDaysError</LimitMinError></result>"

If ($TextError.Length -gt 0) {

$TextError += " / "

}

$TextError += "$WebsiteURL"

}

If ($TextError.Length -gt 0) {

$XML += "<text>Not reached pages: $TextError</text>"

}

$XML += "</PRTG>"

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen "$XML"

November 9, 2018 by Florian Rossmark monitoring prtg scripts security web

Consolidate many line based .CSV files in to a single .CSV with one header line and per file data lines

Summarize a huge amount of files that have line based columns and data in to a single file with the first line the headers found in all files and the actual data as per row for each file, while the headers might change throughout the source files and need to be added dynamically.

This is a special script I wrote for someone else that had about 45k files to process. It is crazy enough to be worth posting here 🙂 and can be found on Spiceworks as well.

Situation:

many .CSV files
all have the columns per line instead of in the first line
the data looks like
- column,data
- column,data
he needs all files transferred in to one file in this format
- header,header,header
- data,data,data
- data,data,data
from per line to one line as a header and the data in each line per file
additional challenge
- the headers might change throughout the files and add more headers

What the script does:

cycle through all files
1. detect all headers
cycle a second time through all files
1. detect all the data
2. write the data in the right column per line per file

Flaws:

The script does not obey if there is data with a comma “,” – it would ignore what is behind that comma

Output:

Output file is a single .CSV file, comma separated columns

Execute this way:

Source Directory – where the .csv files reside
Target Directory – where the new output .csv will be created
open CMD / command prompt
go to the script-directory (where you saved it)
1. CSCRIPT scriptname.vbs “c:\sourcedirectory” “c:\targetdirectory”

CSCRIPT will avoid that you see a million message boxes – it will output directory on your CMD / command prompt window…

Dim strPath, strOutputPath

If WScript.Arguments.Count = 2 Then
	strPath = WScript.Arguments.Item(0)
	strOutputPath = WScript.Arguments.Item(1)
Else
	Wscript.Echo "Usage: cscript AddContent.vbs ""C:\path"" ""C:\outputpath"""
	Wscript.Quit
End If

Const OutPutFileName = "OutputFile.csv"

Dim objFS
Set objFS = CreateObject("Scripting.FileSystemObject")
	If objFS.FolderExists(strPath) And objFS.FolderExists(strOutputPath) Then
		WScript.Echo "Working in directory: " & strPath
		Dim objFolder
		Set objFolder = objFS.GetFolder(strPath)
			Set outputFile = objFS.OpenTextFile(strOutputPath & "\" & OutPutFileName, 2, True) 'write/replace - don't append - create
			Dim strHeader, strLine
			For Each objFile in objFolder.Files		
				If LCase(Right(objFile.Name, 4)) = LCase(".csv") Then 'only for .CSV files
					WScript.Echo "Processing file for headers: " & objFile.Name
					Set inputFile = objFS.OpenTextFile(strPath & "\" & objFile.Name, 1) 'reading
						Do While Not inputFile.AtEndOfStream
							strLine = Split(inputFile.ReadLine, ",", -1, 1)
							If Ubound(strLine) > 0 Then 'no content in this line
								If Not Instr(1, (strHeader & ","), (strLine(0) & ","), 1) > 0 Then 'find if we have this header
									If Len(strHeader) > 0 Then strHeader = strHeader & ","
									strHeader = strHeader & strLine(0)
								End If
							End If
						Loop						
					inputFile.Close
					Set inputFile = Nothing
				End If
			Next	
			outputFile.WriteLine strHeader 'let's write our first line - the headers we have
			WScript.Echo "..Finsihed processing the headers - wrote to file: " & strOutputPath & "\" & OutPutFileName
			
			Dim arrSplitHeader, iHeaders, cntHeaders
			arrSplitHeader = Split(strHeader, ",", -1, 1)
			iHeaders = Ubound(arrSplitHeader) 

			Dim arrLines()
			ReDim arrLines((iHeaders))
			Dim strOutLine 

			For Each objFile in objFolder.Files		
				For cntHeaders = 0 To iHeaders 'cleanup
					arrLines(cntHeaders) = ""
				Next
				If LCase(Right(objFile.Name, 4)) = LCase(".csv") Then 'only for .CSV files
					WScript.Echo "Processing file for data: " & objFile.Name
					Set inputFile = objFS.OpenTextFile(strPath & "\" & objFile.Name, 1) 'reading
						Do While Not inputFile.AtEndOfStream
							strLine = Split(inputFile.ReadLine, ",", -1, 1)
							If Ubound(strLine) > 0 Then 'we do have data
								For cntHeaders = 0 To iHeaders
									If arrSplitHeader(cntHeaders) = strLine(0) Then
										arrLines(cntHeaders) = strLine(1)
										Exit For
									End If
								Next 
							End If
						Loop	
					inputFile.Close
					Set inputFile = Nothing
					strOutLine = ""
					For cntHeaders = 0 To iHeaders 'writeout
						If Len(strOutLine) > 0 Then strOutLine = strOutLine & ","
						strOutLine = strOutLine & arrLines(cntHeaders)
					Next
					outputFile.WriteLine strOutLine
				End If
			Next			
			outputFile.Close			
			Set outputFile = Nothing
			WScript.Echo "..Finsihed - wrote to file: " & strOutputPath & "\" & OutPutFileName
		Set objFolder = Nothing
	End If
Set objFS = Nothing

Dim strPath, strOutputPath

If WScript.Arguments.Count = 2 Then

strPath = WScript.Arguments.Item(0)

strOutputPath = WScript.Arguments.Item(1)

Else

Wscript.Echo "Usage: cscript AddContent.vbs ""C:\path"" ""C:\outputpath"""

Wscript.Quit

End If

Const OutPutFileName = "OutputFile.csv"

Dim objFS

Set objFS = CreateObject("Scripting.FileSystemObject")

If objFS.FolderExists(strPath) And objFS.FolderExists(strOutputPath) Then

WScript.Echo "Working in directory: " & strPath

Dim objFolder

Set objFolder = objFS.GetFolder(strPath)

Set outputFile = objFS.OpenTextFile(strOutputPath & "\" & OutPutFileName, 2, True) 'write/replace - don't append - create

Dim strHeader, strLine

For Each objFile in objFolder.Files

If LCase(Right(objFile.Name, 4)) = LCase(".csv") Then 'only for .CSV files

WScript.Echo "Processing file for headers: " & objFile.Name

Set inputFile = objFS.OpenTextFile(strPath & "\" & objFile.Name, 1) 'reading

Do While Not inputFile.AtEndOfStream

strLine = Split(inputFile.ReadLine, ",", -1, 1)

If Ubound(strLine) > 0 Then 'no content in this line

If Not Instr(1, (strHeader & ","), (strLine(0) & ","), 1) > 0 Then 'find if we have this header

If Len(strHeader) > 0 Then strHeader = strHeader & ","

strHeader = strHeader & strLine(0)

End If

Loop

inputFile.Close

Set inputFile = Nothing

End If

outputFile.WriteLine strHeader 'let's write our first line - the headers we have

WScript.Echo "..Finsihed processing the headers - wrote to file: " & strOutputPath & "\" & OutPutFileName

Dim arrSplitHeader, iHeaders, cntHeaders

arrSplitHeader = Split(strHeader, ",", -1, 1)

iHeaders = Ubound(arrSplitHeader)

Dim arrLines()

ReDim arrLines((iHeaders))

Dim strOutLine

For Each objFile in objFolder.Files

For cntHeaders = 0 To iHeaders 'cleanup

arrLines(cntHeaders) = ""

If LCase(Right(objFile.Name, 4)) = LCase(".csv") Then 'only for .CSV files

WScript.Echo "Processing file for data: " & objFile.Name

Set inputFile = objFS.OpenTextFile(strPath & "\" & objFile.Name, 1) 'reading

Do While Not inputFile.AtEndOfStream

strLine = Split(inputFile.ReadLine, ",", -1, 1)

If Ubound(strLine) > 0 Then 'we do have data

For cntHeaders = 0 To iHeaders

If arrSplitHeader(cntHeaders) = strLine(0) Then

arrLines(cntHeaders) = strLine(1)

Exit For

End If

Loop

inputFile.Close

Set inputFile = Nothing

strOutLine = ""

For cntHeaders = 0 To iHeaders 'writeout

If Len(strOutLine) > 0 Then strOutLine = strOutLine & ","

strOutLine = strOutLine & arrLines(cntHeaders)

outputFile.WriteLine strOutLine

End If

outputFile.Close

Set outputFile = Nothing

WScript.Echo "..Finsihed - wrote to file: " & strOutputPath & "\" & OutPutFileName

Set objFolder = Nothing

End If

Set objFS = Nothing

November 9, 2018 by Florian Rossmark scripts solutions

Secured WinRM SSL session and PowerShell WinRM queries – example with a PRTG sensor for CPU, HDD and RAM

Windows Mangement Remote Mangement / WinRM can be configured as HTTPS / encrypted connection instead of using clear text transfer of the provided information. In order to do this you need to configure it accordingly and have a valid machine certificate installed on the system.

Now – the advantage here is clearly the added security layer while you request and receive those information. More information on how to do this can be found here: https://support.microsoft.com/en-us/help/2019527/how-to-configure-winrm-for-https

Only it becomes a challenge when you want to use PowerShell and e.g. PRTG to use this HTTPS encrypted system. I came across this request and had to create a script that actually works with such an HTTPS encrypted SSL session to WinRM. You can find it below.

What it does is rather simple:

set the CimSessionOptions to use SSL
- additionally it bypasses the certification checks by default – you might want to adjust this depending on your network configuration
it creates a new CimSession to your target system using the UseSSL option
and finally it executes a few queries against this session
the data in this example is then translated in to a PRTG compatible XML structure so you could use it in a Advanced EXE/XML sensor within PRTG

The data in this example combines information about the CPU(s), HardDrives / HDD(s) (only installed drives, not USB) and Memory usage to PRTG in a single sensor while using channels.

Due to some dynamic of the script, you want to make sure you have fixed upper and lower error limits on especially the channel Total Disks – so if something changes you can re-create the sensor due to it’s fixed channels once it did run the first time.

In theory you could provide limits within the XML response to PRTG – this is up to you – I always liked it more to configure them solely in PRTG in the sensor channels so I could adjust them per device.

PS: This was originally posted in the private PRTG channel on SpiceWorks here.

param(
[string]$device
)
#$cred = Get-Credential #you need to somehow get them in - from a file or as parameters... up to you actually... in theory the PRTG Probe would do that already and you wouldn't need it
$CIMSessionOptions = New-CimSessionOption -SkipCACheck -SkipCNCheck -SkipRevocationCheck -UseSsl #-UseSSL is important - the rest - well - I bypassed the certificate checks.. up to your environment in the end...
$CIMSession = New-CimSession -ComputerName $Server -SessionOption $CIMSessionOptions #-Credential $cred

$WMI_OS = Get-CimInstance -CimSession $CIMSession -ClassName Win32_OperatingSystem 
$WMI_CPU = Get-CimInstance -CimSession $CIMSession -ClassName  win32_processor
$WMI_DISK = Get-CimInstance -CimSession $CIMSession -ClassName  Win32_LogicalDisk


#CPU table processing
foreach ($CPU in $WMI_CPU){
    $CPUsTotal += 1
    $XMLCPUs += "<result><channel>CPU Load #$CPUsTotal</channel><value>" + $CPU.LoadPercentage + "</value><unit>Percent</unit></result>"
}

#DISK table processing
foreach ($Disk in $WMI_DISK){
    if ($Disk.DriveType -eq 3) { #DriveType = 3 means it is a LOCAL FIXED DISK
        $DISKsTotal += 1
        $XMLDisks += "<result><channel>Drive " + $Disk.DeviceID + "</channel><value>" + $Disk.FreeSpace + "</value><VolumeSize>Byte</VolumeSize></result>"
        $XMLDisks += "<result><channel>Drive " + $Disk.DeviceID + "</channel><value>" + [math]::Round(($Disk.FreeSpace/$Disk.Size)*100,2).tostring("###") + "</value><float>1</float><unit>Percent</unit></result>"
    }
}

$XML="<prtg>
    <result>
        <channel>Total CPUs</channel>
        <value>" + $CPUsTotal + "</value>
        <unit>Count</unit>
    </result>
    $XMLCPUs  
    <result>
        <channel>Total Disks</channel>
        <value>" + $DISKsTotal + "</value>
        <unit>Count</unit>
    </result>
    $XMLDisks
    <result>
        <channel>Memory total</channel>
        <value>" + $WMI_OS.TotalVisibleMemorySize + "</value>
        <VolumeSize>Bytes</VolumeSize>
    </result>
    <result>
        <channel>Memory free</channel>
        <value>" + $WMI_OS.FreeVirtualMemory + "</value>
        <VolumeSize>Bytes</VolumeSize>
    </result>
    <result>
        <channel>Memory used</channel>
        <value>" + ($WMI_OS.TotalVisibleMemorySize - $WMI_OS.FreeVirtualMemory) + "</value>
        <VolumeSize>Bytes</VolumeSize>
    </result>
    <result>
        <channel>Memory free in percent</channel>
        <value>" + [math]::Round(($WMI_OS.FreePhysicalMemory/$WMI_OS.TotalVisibleMemorySize)*100,2) + "</value>
        <float>1</float> 
        <unit>Percent</unit>
    </result>
    <result>
        <channel>Memory used in percent</channel>
        <value>" + [math]::Round(100-(($WMI_OS.FreePhysicalMemory/$WMI_OS.TotalVisibleMemorySize)*100),2) + "</value>
        <float>1</float> 
        <unit>Percent</unit>
    </result>
</prtg>
"

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

WriteXmlToScreen "$XML"

param(

[string]$device

)

#$cred = Get-Credential #you need to somehow get them in - from a file or as parameters... up to you actually... in theory the PRTG Probe would do that already and you wouldn't need it

$CIMSessionOptions = New-CimSessionOption -SkipCACheck -SkipCNCheck -SkipRevocationCheck -UseSsl #-UseSSL is important - the rest - well - I bypassed the certificate checks.. up to your environment in the end...

$CIMSession = New-CimSession -ComputerName $Server -SessionOption $CIMSessionOptions #-Credential $cred

$WMI_OS = Get-CimInstance -CimSession $CIMSession -ClassName Win32_OperatingSystem

$WMI_CPU = Get-CimInstance -CimSession $CIMSession -ClassName win32_processor

$WMI_DISK = Get-CimInstance -CimSession $CIMSession -ClassName Win32_LogicalDisk

#CPU table processing

foreach ($CPU in $WMI_CPU){

$CPUsTotal += 1

$XMLCPUs += "<result><channel>CPU Load #$CPUsTotal</channel><value>" + $CPU.LoadPercentage + "</value><unit>Percent</unit></result>"

}

#DISK table processing

foreach ($Disk in $WMI_DISK){

if ($Disk.DriveType -eq 3) { #DriveType = 3 means it is a LOCAL FIXED DISK

$DISKsTotal += 1

$XMLDisks += "<result><channel>Drive " + $Disk.DeviceID + "</channel><value>" + $Disk.FreeSpace + "</value><VolumeSize>Byte</VolumeSize></result>"

$XMLDisks += "<result><channel>Drive " + $Disk.DeviceID + "</channel><value>" + [math]::Round(($Disk.FreeSpace/$Disk.Size)*100,2).tostring("###") + "</value><float>1</float><unit>Percent</unit></result>"

}

$XML="<prtg>

<channel>Total CPUs</channel>

<value>" + $CPUsTotal + "</value>

<unit>Count</unit>

</result>

$XMLCPUs

<channel>Total Disks</channel>

<value>" + $DISKsTotal + "</value>

<unit>Count</unit>

</result>

$XMLDisks

<channel>Memory total</channel>

<value>" + $WMI_OS.TotalVisibleMemorySize + "</value>

<VolumeSize>Bytes</VolumeSize>

</result>

<channel>Memory free</channel>

<value>" + $WMI_OS.FreeVirtualMemory + "</value>

<VolumeSize>Bytes</VolumeSize>

</result>

<channel>Memory used</channel>

<value>" + ($WMI_OS.TotalVisibleMemorySize - $WMI_OS.FreeVirtualMemory) + "</value>

<VolumeSize>Bytes</VolumeSize>

</result>

<channel>Memory free in percent</channel>

<value>" + [math]::Round(($WMI_OS.FreePhysicalMemory/$WMI_OS.TotalVisibleMemorySize)*100,2) + "</value>

<unit>Percent</unit>

</result>

<channel>Memory used in percent</channel>

<value>" + [math]::Round(100-(($WMI_OS.FreePhysicalMemory/$WMI_OS.TotalVisibleMemorySize)*100),2) + "</value>

<unit>Percent</unit>

</result>

</prtg>

Function WriteXmlToScreen ([xml]$xml) #just to make it clean XML code...

{

$StringWriter = New-Object System.IO.StringWriter;

$XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;

$XmlWriter.Formatting = "indented";

$xml.WriteTo($XmlWriter);

$XmlWriter.Flush();

$StringWriter.Flush();

Write-Output $StringWriter.ToString();

}

WriteXmlToScreen "$XML"

November 8, 2018 by Florian Rossmark monitoring prtg scripts

Updated domain join script including KeePass / Pleasant Password server entries for local admins

Today I post an updated version of the domain-join script I initially posted here.

In theory you can just replace the script with the new version – assuming you did not make any changes other then adjusting it to your domain / server-names.

What changed in the newer version:

the top lines in the script hold the basic configuration parameters
- line 1: NetBIOS name of your Active Directory domain
- line 2: your DNS domain name
- line 3: your distinguished domain name / root DN of your domain
- line 4: your default OU for new workstations
- line 5: empty
- line 6: KeePass / Pleasant Password Server URL
- line 7: KeePass folder to store the password in
the script now relies on the above parameters rather then specifying them in various areas in the script, making the whole use / adjustment of the script way easier
advanced error handling
- after the user entered the computer name and his domain admin credentials the systems checks if it can connect to the domain and if the computer name already exists
  - if the domain credentials are invalid (can be a non-admin – as long they are valid) you get a message explaining that the script will stop due to wrong credentials
  - if the computer name already exists in the domain, you get a message about it and the script stops
- KeePass or Pleasant Password Server connection – if it fails to connect with the credentials provided, you get a message about it and the script will stop
adjusted messages with various colours
- white text – standard as it was before
- yellow text – highlighted information so it sticks better out for the end-user
- magenta text – handled error / failure message – this is an explanation that something stopped the script from going further
- red text – those are real PowerShell error messages – either due to not handled errors or if the error was handled plotted out to the screen as additional reference and help

For additional information, please look at the original post here.

This script is also mentioned on the API Examples page on the Pleasant Solutions web site here.

$MyDomainNetBIOS="CONTOSO"
$MyDomain="contoso.local"
$MyDomainDN="dc=contoso,dc=local"
$MyDomainOU="OU=Computers,OU=USA,DC=contoso,DC=local"

$KeepassURL = "https://pwdbserver01v.contoso.local:10001"
$PWFolder = "Local Admins"

$ScriptVersion = "1.4 - 9/5/2018 - Florian Rossmark"
Clear-Host
Write-Host ""
Write-Host ""
Write-Host "Welcome to the Domain-Join script"
Write-Host "================================="
Write-Host ""
Write-Host ""
Write-Host "This script will do the following Steps:"
Write-Host "----------------------------------------"
Write-Host "- You enter the Computer Name / Host Name" -ForegroundColor Yellow
Write-Host "- You enter Domain Admin credentials for a Domain-Join" -ForegroundColor Yellow
Write-Host "- You enter credentials to access KeyPass Password server" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Note: always type credentials without any domain-information ($MyDomainNetBIOS\) - the script will fail otherwise."
Write-Host ""
Write-Host ""
Write-Host ""
Write-Host "- The script will automatically create a KeyPass entry with all information" -ForegroundColor Yellow
Write-Host "- The script will automatically create a specific local admin account for this machine" -ForegroundColor Yellow
Write-Host "- The script will automatically rename the system to the given name" -ForegroundColor Yellow
Write-Host "- The script will automatically join the system to the domain $MyDomain" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Script Version: $ScriptVersion"
Write-Host ""
Write-Host ""
pause

#Upper area holds functions..
Function MakeUp-String([Int]$Size = 8, [Char[]]$CharSets = "ULNS", [Char[]]$Exclude) {
    $Chars = @(); $TokenSet = @()
    If (!$TokenSets) {$Global:TokenSets = @{
        U = [Char[]]'ABCDEFGHJKLMNPQRSTUVWXYZ'                                #Upper case
        L = [Char[]]'abcdefghijkmnpqrstuvwxyz'                                #Lower case
        N = [Char[]]'23456789'                                                #Numerals
        S = [Char[]]'!@$!@$!@$!@$'                         #Symbols
    }}
    $CharSets | ForEach {
        $Tokens = $TokenSets."$_" | ForEach {If ($Exclude -cNotContains $_) {$_}}
        If ($Tokens) {
            $TokensSet += $Tokens
            If ($_ -cle [Char]"Z") {$Chars += $Tokens | Get-Random}             #Character sets defined in upper case are mandatory
        }
    }
    While ($Chars.Count -lt $Size) {$Chars += $TokensSet | Get-Random}
    ($Chars | Sort-Object {Get-Random}) -Join ""                                #Mix the (mandatory) characters and output string
}; 
#avoid certificate issues
Add-Type @"
    using System;
    using System.Net;
    using System.Net.Security;
    using System.Security.Cryptography.X509Certificates;
    public class ServerCertificateValidationCallback
    {
        public static void Ignore()
        {
            ServicePointManager.ServerCertificateValidationCallback += 
                delegate
                (
                    Object obj, 
                    X509Certificate certificate, 
                    X509Chain chain, 
                    SslPolicyErrors errors
                )
                {
                    return true;
                };
        }
    }
"@
[ServerCertificateValidationCallback]::Ignore();

#Script starts here...
Clear-Host
Write-Host ""
Write-Host ""
Write-Host "This script must be executed in a power-shell with elevated rights!" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Stop the script with: CTRL + C any time..."
Write-Host ""
Write-Host ""
Pause
Clear-Host

$ComputerName = Read-Host "Please enter new Computer Name"
$DomainJoinCredentials = Get-Credential -Message "Enter your credentials for the domain join ($MyDomainNetBIOS) without the domain part"

Try{
    $ADSISearcherDE = New-Object System.DirectoryServices.DirectoryEntry("LDAP://$MyDomain",$($DomainJoinCredentials.UserName),$($DomainJoinCredentials.GetNetworkCredential().password))
    $ADSISearcher = New-Object System.DirectoryServices.DirectorySearcher($ADSISearcherDE,"(&(objectCategory=Computer)(name=$ComputerName))")
    $ADSISearcherResults = $ADSISearcher.FindAll()
    if ($ADSISearcherResults.Count -gt 0)
    {
        Clear-Host
        Write-Host ""
        Write-Host ""
        Write-Host "Computer account with this name '$ComputerName' already exists in the domain '$MyDomain'." -ForegroundColor Magenta
        Write-Host ""
        Write-Host ""
        Write-Host "You need to delete the account first or make sure you entered the right name." -ForegroundColor Magenta
        Write-Host ""
        Write-Host ""
        Write-Host "Script will stop now." -ForegroundColor Magenta
        Write-Host ""
        Write-Host ""
        Pause
        Exit
    }
} Catch {
    Clear-Host
    Write-Host ""
    Write-Host ""
    Write-Host "Your Domain Admin credentials where wrong." -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host "Script will stop now." -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host ""
    Write-Host "================================================================================================================"
    Write-Host "Error Details:"
    Write-Host "=============="
    write-host "Caught exception:" -ForegroundColor Red
    write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
    write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "================================================================================================================"
    Write-Host ""
    Write-Host ""
    Pause
    Exit
}

$KeePassCredentials = Get-Credential -Message “Enter your credentials to access Keepass Server without the domain part” 
$localAdminUser = $("$ComputerName" + "_Admin")
$PW = MakeUp-String(12)
$PWencrypted = ConvertTo-SecureString $PW -AsPlainText -Force

Clear-Host
Write-Host ""
Write-Host ""
Write-Host "Checking KeePass to see if those credentials already exist"
Write-Host ""
Write-Host ""

#Collecting MACs
$colMACItems = get-wmiobject Win32_NetworkAdapter | Where-Object {$_.MACAddress -like "*:*"}
foreach ($objMACItem in $colMACItems) {  
    $MACobj = $objMACItem |select Description,MACAddress   
    $MACs += $MACobj.MACAddress + " - " + $MACobj.Description + "
"
}
#Collecting SN/ServiceTag and HW information
$SN = "ServiceTag / SN: "+(Get-WmiObject win32_bios | select SerialNumber).SerialNumber
$Model = "Model: "+(Get-WmiObject win32_computersystem | select Model).Model
$Manufacturer = "Manufacturer: "+(Get-WmiObject win32_computersystem | select Manufacturer).Manufacturer
$UEFIKey = wmic path softwarelicensingservice get OA3xOriginalProductKey
$PWName = "Individual Admin Account on $ComputerName"
$PWUser = $localAdminUser
$PWPassword = $PW
$PWDescription = "Hardware information:
=====================
$Manufacturer
$Model
$SN

UEFI BIOS Windows Key:
======================
$UEFIKey

Known MAC Addresses:
====================
$MACs
(MACs might be subject to change with Docking-Stations)"

$tokenParams = @{
    grant_type='password';
    username=$KeePassCredentials.UserName;
    password=$KeePassCredentials.GetNetworkCredential().password;}

Try{
    $JSON = Invoke-WebRequest -Uri "$KeepassURL/OAuth2/Token" -Method POST -Body $tokenParams -ContentType "application/x-www-form-urlencoded" -UseBasicParsing 
    $Token = (ConvertFrom-Json $JSON.Content).access_token
} Catch {
    Clear-Host
    Write-Host ""
    Write-Host ""
    Write-Host "Your KeePass credentials did not work." -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host "Script will stop now." -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host ""
    Write-Host "================================================================================================================"
    Write-Host "Error Details:"
    Write-Host "=============="
    write-host "Caught exception:" -ForegroundColor Red
    write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
    write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "================================================================================================================"
    Write-Host ""
    Write-Host ""
    Pause
    Exit
}

$headers = @{
    "Accept" = "application/json"
    "Authorization" = "$Token"}
    
$GroupSearch = @{“search” = $PWFolder}
$Group = Invoke-RestMethod -Method post -Uri "$KeepassURL/api/v4/rest/search"-body (ConvertTo-Json $GroupSearch) -Headers $headers -ContentType 'application/json' -UseBasicParsing
$GroupID = $Group.Groups.Id

$SearchPhrase = $PWUser
$searchbody = @{“search” = $SearchPhrase}
$Search = Invoke-RestMethod -Method post -Uri "$KeepassURL/api/v4/rest/search"-body (ConvertTo-Json $searchbody) -Headers $headers -ContentType 'application/json' -UseBasicParsing
$SearchID = $Search.Credentials.Id

$KeePassResultscnt = 0
ForEach($Result in $Search.credentials)
{
    $CredentialID = $Result.id
    If ($Result.GroupId -eq $GroupID)
    {
        $KeePassResultscnt += 1
    }        
}
If ($KeePassResultscnt -ge 1)
{
    Write-Host "Total number of results found: $cnt" -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host "ALERT - KeePass already has credentials for this system - can not proceed." -ForegroundColor Magenta
    Write-Host "==========================================================================" -ForegroundColor Magenta
    Write-Host ""
    Write-Host "Search-Phrase: $SearchPhrase" -ForegroundColor Magenta
    Write-Host "Search-Folder: $PWFolder" -ForegroundColor Magenta
    Write-Host "Amount of results found: $KeePassResultscnt" -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host ""
    Write-Host "Next Steps:" -ForegroundColor Magenta
    Write-Host "===========" -ForegroundColor Magenta
    Write-Host "- This script will exit now!" -ForegroundColor Magenta
    Write-Host "- No changes to the system of KeePass have been applied" -ForegroundColor Magenta
    Write-Host "- Go to KeePass and check if this is an old entry and can be renamed/moved" -ForegroundColor Magenta
    Write-Host "- Execute the script again" -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    pause
    EXIT
} Else {
    Write-Host ""
    Write-Host ""
    Write-Host "Writing new credentials to the KeePass server/database"
    Write-Host ""
    Write-Host ""
    $PWEntryDT = Get-Date -Format "O"
    $CredEntry = @{
        Id = "00000000-0000-0000-0000-000000000000"
        Name = "$PWName"
        Username = "$PWUser"
        Password = "$PWPassword"
        Created = "$PWEntryDT"
        Modified = "$PWEntryDT"
        GroupId = "$GroupID"
        Notes = "$PWDescription"
        Url = "$ComputerName"
    }
    Invoke-RestMethod -Method post -Uri "$KeepassURL/api/v4/rest/credential/00000000-0000-0000-0000-000000000000"-body @(ConvertTo-Json $CredEntry) -Headers $headers -ContentType 'application/json' -UseBasicParsing
    Write-Host ""
    Write-Host ""
    Write-Host "Finished writing to KeePass server/database"
    Write-Host ""
    Write-Host ""
}
pause

Clear-Host
Write-Host ""
Write-Host ""
Write-Host "Please check the following information:"
Write-Host "======================================="
Write-Host ""
Write-Host "Computer Name: $ComputerName" -ForegroundColor Yellow
Write-Host "local Admin: $localAdminUser" -ForegroundColor Yellow
Write-Host "Admin-PW: $PW" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Make sure the information above are correct!"
Write-Host ""
Write-Host ""
Write-Host "Verify the local Admin and Admin-PW in KeePass!" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
pause
Write-Host ""
Write-Host ""
Write-Host "All information are verified?"
Write-Host ""
Write-Host ""
pause

Clear-Host
Write-Host "Checking and removing (if exists) old local user: $localAdminUser"
Get-LocalUser -Name $localAdminUser | Remove-LocalUser 
Clear-Host
Write-Host "Adding new local administrator account: $localAdminUser"
New-LocalUser -Name $localAdminUser -Password $PWencrypted -AccountNeverExpires -PasswordNeverExpires -Description "System specific local administrator account"
$NewUser = Get-LocalUser -Name $localAdminUser
$AdminGroup = Get-LocalGroup -Name "Administrators"
Add-LocalGroupMember -Name $AdminGroup -Member $NewUser

Write-Host ""
Write-Host ""
Write-Host "Finished."
Write-Host ""
Write-Host ""
pause

Write-Host ""
Write-Host ""
Write-Host "Joining this system to the domain and reboot automatically - stay put" -ForegroundColor Yellow
Write-Host ""
Write-Host ""
Write-Host "Note: The system will not reboot if an error occurs. Check the error message in case you see an error." -ForegroundColor Yellow
Write-Host ""
Write-Host ""

Try{
    Add-Computer -Domain $MyDomain -Credential $DomainJoinCredentials -NewName $ComputerName -Restart -Force -OUPath $MyDomainOU
} Catch {
    Write-Host ""
    Write-Host ""
    Write-Host "Domain-Join did not work - Error:" -ForegroundColor Magenta
    Write-Host ""
    Write-Host ""
    Write-Host ""
    Write-Host "================================================================================================================"
    Write-Host "Error Details:"
    Write-Host "=============="
    write-host "Caught exception:" -ForegroundColor Red
    write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
    write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
    Write-Host "================================================================================================================"
    Write-Host ""
    Write-Host ""
    Pause
} Finally {
    Exit
}

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

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

$MyDomainNetBIOS="CONTOSO"

$MyDomain="contoso.local"

$MyDomainDN="dc=contoso,dc=local"

$MyDomainOU="OU=Computers,OU=USA,DC=contoso,DC=local"

$KeepassURL = "https://pwdbserver01v.contoso.local:10001"

$PWFolder = "Local Admins"

$ScriptVersion = "1.4 - 9/5/2018 - Florian Rossmark"

Clear-Host

Write-Host ""

Write-Host "Welcome to the Domain-Join script"

Write-Host "================================="

Write-Host ""

Write-Host "This script will do the following Steps:"

Write-Host "----------------------------------------"

Write-Host "- You enter the Computer Name / Host Name" -ForegroundColor Yellow

Write-Host "- You enter Domain Admin credentials for a Domain-Join" -ForegroundColor Yellow

Write-Host "- You enter credentials to access KeyPass Password server" -ForegroundColor Yellow

Write-Host ""

Write-Host "Note: always type credentials without any domain-information ($MyDomainNetBIOS\) - the script will fail otherwise."

Write-Host ""

Write-Host "- The script will automatically create a KeyPass entry with all information" -ForegroundColor Yellow

Write-Host "- The script will automatically create a specific local admin account for this machine" -ForegroundColor Yellow

Write-Host "- The script will automatically rename the system to the given name" -ForegroundColor Yellow

Write-Host "- The script will automatically join the system to the domain $MyDomain" -ForegroundColor Yellow

Write-Host ""

Write-Host "Script Version: $ScriptVersion"

Write-Host ""

pause

#Upper area holds functions..

Function MakeUp-String([Int]$Size = 8, [Char[]]$CharSets = "ULNS", [Char[]]$Exclude) {

$Chars = @(); $TokenSet = @()

If (!$TokenSets) {$Global:TokenSets = @{

U = [Char[]]'ABCDEFGHJKLMNPQRSTUVWXYZ' #Upper case

L = [Char[]]'abcdefghijkmnpqrstuvwxyz' #Lower case

N = [Char[]]'23456789' #Numerals

S = [Char[]]'!@$!@$!@$!@$' #Symbols

}}

$CharSets | ForEach {

$Tokens = $TokenSets."$_" | ForEach {If ($Exclude -cNotContains $_) {$_}}

If ($Tokens) {

$TokensSet += $Tokens

If ($_ -cle [Char]"Z") {$Chars += $Tokens | Get-Random} #Character sets defined in upper case are mandatory

}

While ($Chars.Count -lt $Size) {$Chars += $TokensSet | Get-Random}

($Chars | Sort-Object {Get-Random}) -Join "" #Mix the (mandatory) characters and output string

};

#avoid certificate issues

Add-Type @"

using System;

using System.Net;

using System.Net.Security;

using System.Security.Cryptography.X509Certificates;

public class ServerCertificateValidationCallback

{

public static void Ignore()

{

ServicePointManager.ServerCertificateValidationCallback +=

delegate

(

Object obj,

X509Certificate certificate,

X509Chain chain,

SslPolicyErrors errors

)

{

return true;

};

}

[ServerCertificateValidationCallback]::Ignore();

#Script starts here...

Clear-Host

Write-Host ""

Write-Host "This script must be executed in a power-shell with elevated rights!" -ForegroundColor Yellow

Write-Host ""

Write-Host "Stop the script with: CTRL + C any time..."

Write-Host ""

Pause

Clear-Host

$ComputerName = Read-Host "Please enter new Computer Name"

$DomainJoinCredentials = Get-Credential -Message "Enter your credentials for the domain join ($MyDomainNetBIOS) without the domain part"

Try{

$ADSISearcherDE = New-Object System.DirectoryServices.DirectoryEntry("LDAP://$MyDomain",$($DomainJoinCredentials.UserName),$($DomainJoinCredentials.GetNetworkCredential().password))

$ADSISearcher = New-Object System.DirectoryServices.DirectorySearcher($ADSISearcherDE,"(&(objectCategory=Computer)(name=$ComputerName))")

$ADSISearcherResults = $ADSISearcher.FindAll()

if ($ADSISearcherResults.Count -gt 0)

{

Clear-Host

Write-Host ""

Write-Host "Computer account with this name '$ComputerName' already exists in the domain '$MyDomain'." -ForegroundColor Magenta

Write-Host ""

Write-Host "You need to delete the account first or make sure you entered the right name." -ForegroundColor Magenta

Write-Host ""

Write-Host "Script will stop now." -ForegroundColor Magenta

Write-Host ""

Pause

Exit

}

} Catch {

Clear-Host

Write-Host ""

Write-Host "Your Domain Admin credentials where wrong." -ForegroundColor Magenta

Write-Host ""

Write-Host "Script will stop now." -ForegroundColor Magenta

Write-Host ""

Write-Host "================================================================================================================"

Write-Host "Error Details:"

Write-Host "=============="

write-host "Caught exception:" -ForegroundColor Red

write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red

write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red

Write-Host "================================================================================================================"

Write-Host ""

Pause

Exit

}

$KeePassCredentials = Get-Credential -Message “Enter your credentials to access Keepass Server without the domain part”

$localAdminUser = $("$ComputerName" + "_Admin")

$PW = MakeUp-String(12)

$PWencrypted = ConvertTo-SecureString $PW -AsPlainText -Force

Clear-Host

Write-Host ""

Write-Host "Checking KeePass to see if those credentials already exist"

Write-Host ""

#Collecting MACs

$colMACItems = get-wmiobject Win32_NetworkAdapter | Where-Object {$_.MACAddress -like "*:*"}

foreach ($objMACItem in $colMACItems) {

$MACobj = $objMACItem |select Description,MACAddress

$MACs += $MACobj.MACAddress + " - " + $MACobj.Description + "

}

#Collecting SN/ServiceTag and HW information

$SN = "ServiceTag / SN: "+(Get-WmiObject win32_bios | select SerialNumber).SerialNumber

$Model = "Model: "+(Get-WmiObject win32_computersystem | select Model).Model

$Manufacturer = "Manufacturer: "+(Get-WmiObject win32_computersystem | select Manufacturer).Manufacturer

$UEFIKey = wmic path softwarelicensingservice get OA3xOriginalProductKey

$PWName = "Individual Admin Account on $ComputerName"

$PWUser = $localAdminUser

$PWPassword = $PW

$PWDescription = "Hardware information:

=====================

$Manufacturer

$Model

$SN

UEFI BIOS Windows Key:

======================

$UEFIKey

Known MAC Addresses:

====================

$MACs

(MACs might be subject to change with Docking-Stations)"

$tokenParams = @{

grant_type='password';

username=$KeePassCredentials.UserName;

password=$KeePassCredentials.GetNetworkCredential().password;}

Try{

$JSON = Invoke-WebRequest -Uri "$KeepassURL/OAuth2/Token" -Method POST -Body $tokenParams -ContentType "application/x-www-form-urlencoded" -UseBasicParsing

$Token = (ConvertFrom-Json $JSON.Content).access_token

} Catch {

Clear-Host

Write-Host ""

Write-Host "Your KeePass credentials did not work." -ForegroundColor Magenta

Write-Host ""

Write-Host "Script will stop now." -ForegroundColor Magenta

Write-Host ""

Write-Host "================================================================================================================"

Write-Host "Error Details:"

Write-Host "=============="

write-host "Caught exception:" -ForegroundColor Red

write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red

write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red

Write-Host "================================================================================================================"

Write-Host ""

Pause

Exit

}

$headers = @{

"Accept" = "application/json"

"Authorization" = "$Token"}

$GroupSearch = @{“search” = $PWFolder}

$Group = Invoke-RestMethod -Method post -Uri "$KeepassURL/api/v4/rest/search"-body (ConvertTo-Json $GroupSearch) -Headers $headers -ContentType 'application/json' -UseBasicParsing

$GroupID = $Group.Groups.Id

$SearchPhrase = $PWUser

$searchbody = @{“search” = $SearchPhrase}

$Search = Invoke-RestMethod -Method post -Uri "$KeepassURL/api/v4/rest/search"-body (ConvertTo-Json $searchbody) -Headers $headers -ContentType 'application/json' -UseBasicParsing

$SearchID = $Search.Credentials.Id

$KeePassResultscnt = 0

ForEach($Result in $Search.credentials)

{

$CredentialID = $Result.id

If ($Result.GroupId -eq $GroupID)

{

$KeePassResultscnt += 1

}

If ($KeePassResultscnt -ge 1)

{

Write-Host "Total number of results found: $cnt" -ForegroundColor Magenta

Write-Host ""

Write-Host "ALERT - KeePass already has credentials for this system - can not proceed." -ForegroundColor Magenta

Write-Host "==========================================================================" -ForegroundColor Magenta

Write-Host ""

Write-Host "Search-Phrase: $SearchPhrase" -ForegroundColor Magenta

Write-Host "Search-Folder: $PWFolder" -ForegroundColor Magenta

Write-Host "Amount of results found: $KeePassResultscnt" -ForegroundColor Magenta

Write-Host ""

Write-Host "Next Steps:" -ForegroundColor Magenta

Write-Host "===========" -ForegroundColor Magenta

Write-Host "- This script will exit now!" -ForegroundColor Magenta

Write-Host "- No changes to the system of KeePass have been applied" -ForegroundColor Magenta

Write-Host "- Go to KeePass and check if this is an old entry and can be renamed/moved" -ForegroundColor Magenta

Write-Host "- Execute the script again" -ForegroundColor Magenta

Write-Host ""

pause

EXIT

} Else {

Write-Host ""

Write-Host "Writing new credentials to the KeePass server/database"

Write-Host ""

$PWEntryDT = Get-Date -Format "O"

$CredEntry = @{

Id = "00000000-0000-0000-0000-000000000000"

Name = "$PWName"

Username = "$PWUser"

Password = "$PWPassword"

Created = "$PWEntryDT"

Modified = "$PWEntryDT"

GroupId = "$GroupID"

Notes = "$PWDescription"

Url = "$ComputerName"

}

Invoke-RestMethod -Method post -Uri "$KeepassURL/api/v4/rest/credential/00000000-0000-0000-0000-000000000000"-body @(ConvertTo-Json $CredEntry) -Headers $headers -ContentType 'application/json' -UseBasicParsing

Write-Host ""

Write-Host "Finished writing to KeePass server/database"

Write-Host ""

}

pause

Clear-Host

Write-Host ""

Write-Host "Please check the following information:"

Write-Host "======================================="

Write-Host ""

Write-Host "Computer Name: $ComputerName" -ForegroundColor Yellow

Write-Host "local Admin: $localAdminUser" -ForegroundColor Yellow

Write-Host "Admin-PW: $PW" -ForegroundColor Yellow

Write-Host ""

Write-Host "Make sure the information above are correct!"

Write-Host ""

Write-Host "Verify the local Admin and Admin-PW in KeePass!" -ForegroundColor Yellow

Write-Host ""

pause

Write-Host ""

Write-Host "All information are verified?"

Write-Host ""

pause

Clear-Host

Write-Host "Checking and removing (if exists) old local user: $localAdminUser"

Get-LocalUser -Name $localAdminUser | Remove-LocalUser

Clear-Host

Write-Host "Adding new local administrator account: $localAdminUser"

New-LocalUser -Name $localAdminUser -Password $PWencrypted -AccountNeverExpires -PasswordNeverExpires -Description "System specific local administrator account"

$NewUser = Get-LocalUser -Name $localAdminUser

$AdminGroup = Get-LocalGroup -Name "Administrators"

Add-LocalGroupMember -Name $AdminGroup -Member $NewUser

Write-Host ""

Write-Host "Finished."

Write-Host ""

pause

Write-Host ""

Write-Host "Joining this system to the domain and reboot automatically - stay put" -ForegroundColor Yellow

Write-Host ""

Write-Host "Note: The system will not reboot if an error occurs. Check the error message in case you see an error." -ForegroundColor Yellow

Write-Host ""

Try{

Add-Computer -Domain $MyDomain -Credential $DomainJoinCredentials -NewName $ComputerName -Restart -Force -OUPath $MyDomainOU

} Catch {

Write-Host ""

Write-Host "Domain-Join did not work - Error:" -ForegroundColor Magenta

Write-Host ""

Write-Host "================================================================================================================"

Write-Host "Error Details:"

Write-Host "=============="

write-host "Caught exception:" -ForegroundColor Red

write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red

write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red

Write-Host "================================================================================================================"

Write-Host ""

Pause

} Finally {

Exit

}

September 6, 2018 by Florian Rossmark recommendations scripts server solutions web

Gathering profile information from computer

Every now an then you might need to know who logged on and when was the last logon of which user to a specific workstation as well as the size of each user profile. For this I once wrote the PowerShell script you can find below. It does a WMI query against a list of one or more target computers and reads out the information reporting it back.

As input parameter use a comma separated list of computer names – those must be reachable and administrative accessible (you need at least admin rights on the target system). You then get a output set per profile.

The output can e.g. be transformed with the parameter |ft to see it in table format – like typical in PowerShell.

Output values are:

ComputerName
ProfileName
ProfilePath
ProfileType
- Temporary
- Roaming
- Mandatory
- Corrupted
- local
IsinUse
IsSystemAccount
Size
- this needs to most processing time – it is a manual size check including even temp files.. – other then what Windows shows you
LastUseTime

[cmdletbinding()]
[cmdletbinding()]
param (
[parameter(ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
[string[]]$ComputerName = $env:computername
)            
 
foreach ($Computer in $ComputerName) {
 $Profiles = Get-WmiObject -Class Win32_UserProfile -Computer $Computer -ea 0
 foreach ($profile in $profiles) {
  try {
      $objSID = New-Object System.Security.Principal.SecurityIdentifier($profile.sid)
      $objuser = $objsid.Translate([System.Security.Principal.NTAccount])
      $objusername = $objuser.value
  } catch {
        $objusername = $profile.sid
  }
  switch($profile.status){
   1 { $profileType="Temporary" }
   2 { $profileType="Roaming" }
   4 { $profileType="Mandatory" }
   8 { $profileType="Corrupted" }
   default { $profileType = "LOCAL" }
  }
  $size = 0
  try{
    $profilepath=$profile.localpath -replace "C:","\\$Computer\C$"
    $size=(dir $profilepath -recurse -force -ea silentlycontinue | Measure-Object ‘length’ -sum -Maximum).sum
    $size=[math]::Round(($size/1GB),2)
    #$size={{0:n2} -f $size}
    $size2=[string]::Format("{0:0.00} GB", $size)
  } catch {
    $size = -1
  }

  $User = $objUser.Value
  try {
	$ProfileLastUseTime = ([WMI]"").Converttodatetime($profile.lastusetime)
  } catch {
	$ProfileLastUseTime = ""
  }
  $OutputObj = New-Object -TypeName PSobject
  $OutputObj | Add-Member -MemberType NoteProperty -Name ComputerName -Value $Computer.toUpper()
  $OutputObj | Add-Member -MemberType NoteProperty -Name ProfileName -Value $objusername
  $OutputObj | Add-Member -MemberType NoteProperty -Name ProfilePath -Value $profile.localpath
  $OutputObj | Add-Member -MemberType NoteProperty -Name ProfileType -Value $ProfileType
  $OutputObj | Add-Member -MemberType NoteProperty -Name IsinUse -Value $profile.loaded
  $OutputObj | Add-Member -MemberType NoteProperty -Name IsSystemAccount -Value $profile.special
  $OutputObj | Add-Member -MemberType NoteProperty -Name Size -Value $size2
  $OutputObj | Add-Member -MemberType NoteProperty -Name LastUseTime -Value $ProfileLastUseTime
  $OutputObj
  
 }
}

[cmdletbinding()]

param (

[parameter(ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]

[string[]]$ComputerName = $env:computername

)

foreach ($Computer in $ComputerName) {

$Profiles = Get-WmiObject -Class Win32_UserProfile -Computer $Computer -ea 0

foreach ($profile in $profiles) {

try {

$objSID = New-Object System.Security.Principal.SecurityIdentifier($profile.sid)

$objuser = $objsid.Translate([System.Security.Principal.NTAccount])

$objusername = $objuser.value

} catch {

$objusername = $profile.sid

}

switch($profile.status){

1 { $profileType="Temporary" }

2 { $profileType="Roaming" }

4 { $profileType="Mandatory" }

8 { $profileType="Corrupted" }

default { $profileType = "LOCAL" }

}

$size = 0

try{

$profilepath=$profile.localpath -replace "C:","\\$Computer\C$"

$size=(dir $profilepath -recurse -force -ea silentlycontinue | Measure-Object ‘length’ -sum -Maximum).sum

$size=[math]::Round(($size/1GB),2)

#$size={{0:n2} -f $size}

$size2=[string]::Format("{0:0.00} GB", $size)

} catch {

$size = -1

}

$User = $objUser.Value

try {

$ProfileLastUseTime = ([WMI]"").Converttodatetime($profile.lastusetime)

} catch {

$ProfileLastUseTime = ""

}

$OutputObj = New-Object -TypeName PSobject

$OutputObj | Add-Member -MemberType NoteProperty -Name ComputerName -Value $Computer.toUpper()

$OutputObj | Add-Member -MemberType NoteProperty -Name ProfileName -Value $objusername

$OutputObj | Add-Member -MemberType NoteProperty -Name ProfilePath -Value $profile.localpath

$OutputObj | Add-Member -MemberType NoteProperty -Name ProfileType -Value $ProfileType

$OutputObj | Add-Member -MemberType NoteProperty -Name IsinUse -Value $profile.loaded

$OutputObj | Add-Member -MemberType NoteProperty -Name IsSystemAccount -Value $profile.special

$OutputObj | Add-Member -MemberType NoteProperty -Name Size -Value $size2

$OutputObj | Add-Member -MemberType NoteProperty -Name LastUseTime -Value $ProfileLastUseTime

$OutputObj

}

August 20, 2018 by Florian Rossmark recommendations scripts solutions

Monitor user accounts in Active Directory with PRTG

The following script will read through your current Active Directory and filter for user accounts with the following specific conditions:

Lockedout users – please read below for further information about this
- all users that are lockedout
- must be an enabled user
- that is not expired
disabled users
- all users that have been disabled
expired users
- must be an enabled user
- the expiration date is set and past the current date
users with password never expires set
- must be an enabled user

This will give you a pure counter output per channel in an for PRTG Extended script sensor XML result.

But there is a theoretical flaw in one of the methods – the locked out users. Now, user accounts get locked out in Active Directory due to too many logon attempts with an invalid password. This causes Active Directory to set the lockedout bit in the object properties. The issue here is that this bit will not be set back to 0 after the defined lockout duration (GPO) is past, the property will only be set back to 0 once the lockout duration is passed and he successfully logged on.

This means, the counter might give you more results then currently true, it might count users that have been locked out but the lockout-duration passed – but they did not yet logon successfully. This is somehow a false positive, while not totally false. In any case, you need to be aware of this.

The script could be more efficient as well in the way it filters a few things, so far I optimized it as far as I could – the LockedOut value can not be set as a -Filter, in theory it might be possible to speed it up with a -Filter to the UserAccountControl (if that is even possible – not tested) – but I am not certain this would work. If you really want to speed it up you would need to work with -LDAPFilter – but this actually needs to completely replace the internal filter capabilities of Get-ADUser – you can’t use both – it is one or the other.

Import-Module ActiveDirectory

#you could add - filters, a OU limitation or a server against whom this would be executed.. see Get-ADUser options for more details
#$Server = ""
#$OU = ""
#Get-ADUser -Server $Server -Filter
#Get-ADUser -SearchBase $OU -Filter

#all locked users that aren't disabled or expired
$LockedOutUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {$_.lockedout -eq $True -and (($_.AccountExpirationDate -gt (Get-Date) -or ($_.AccountExpirationDate -eq $null)))} 

#all users that are disabled - this is a manual action in Active Directory
$DisabledUsers=Get-ADUser -Filter {Enabled -eq $False -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate

#all users that are not disabled but expired already
$ExpiredUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {(($_.AccountExpirationDate -lt (Get-Date) -and ($_.AccountExpirationDate -ne $null)))} 

#users with not expiring passwords and are enabled and not expired
$NotExpiringPWD=Get-ADUser -Filter {Enabled -eq $True -and PasswordNeverExpires -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate 


If ($LockedOutUsers.count -eq $null -and $LockedOutUsers -eq $null){
    $cntLockedOutUsers=0
}Elseif ($LockedOutUsers.count -eq $null -and $LockedOutUsers -ne $null){
    $cntLockedOutUsers=1
}Else{
    $cntLockedOutUsers=@($LockedOutUsers.count)
}

If ($DisabledUsers.count -eq $null -and $DisabledUsers -eq $null){
    $cntDisabledUsers=0
}Elseif ($DisabledUsers.count -eq $null -and $DisabledUsers -ne $null){
    $cntDisabledUsers=1
}Else{
    $cntDisabledUsers=@($DisabledUsers.count)
}

If ($ExpiredUsers.count -eq $null -and $ExpiredUsers -eq $null){
    $cntExpiredUsers=0
}Elseif ($ExpiredUsers.count -eq $null -and $ExpiredUsers -ne $null){
    $cntExpiredUsers=1
}Else{
    $cntExpiredUsers=@($ExpiredUsers.count)
}

If ($NotExpiringPWD.count -eq $null -and $NotExpiringPWD -eq $null){
    $cntNotExpiringPWD=0
}Elseif ($NotExpiringPWD.count -eq $null -and $NotExpiringPWD -ne $null){
    $cntNotExpiringPWD=1
}Else{
    $cntNotExpiringPWD=@($NotExpiringPWD.count)
}

$XML += "<prtg>"
$XML += "<result>" 
$XML += "<channel>Locked Out Users</channel>" 
$XML += "<value>$cntLockedOutUsers</value>" 
$XML += "</result>"
$XML += "<result>" 
$XML += "<channel>Disabled Users</channel>" 
$XML += "<value>$cntDisabledUsers</value>" 
$XML += "</result>"
$XML += "<result>" 
$XML += "<channel>Expired Users - not disabled</channel>" 
$XML += "<value>$cntExpiredUsers</value>" 
$XML += "</result>"
$XML += "<result>" 
$XML += "<channel>Users with password never expires</channel>" 
$XML += "<value>$cntNotExpiringPWD</value>" 
$XML += "</result>"
$XML += "</prtg>"

Function WriteXmlToScreen ([xml]$xml)
{
    $StringWriter = New-Object System.IO.StringWriter;
    $XmlWriter = New-Object System.Xml.XmlTextWriter $StringWriter;
    $XmlWriter.Formatting = "indented";
    $xml.WriteTo($XmlWriter);
    $XmlWriter.Flush();
    $StringWriter.Flush();
    Write-Output $StringWriter.ToString();
}

WriteXmlToScreen $XML

Import-Module ActiveDirectory

#you could add - filters, a OU limitation or a server against whom this would be executed.. see Get-ADUser options for more details

#$Server = ""

#$OU = ""

#Get-ADUser -Server $Server -Filter

#Get-ADUser -SearchBase $OU -Filter

#all locked users that aren't disabled or expired

$LockedOutUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {$_.lockedout -eq $True -and (($_.AccountExpirationDate -gt (Get-Date) -or ($_.AccountExpirationDate -eq $null)))}

#all users that are disabled - this is a manual action in Active Directory

$DisabledUsers=Get-ADUser -Filter {Enabled -eq $False -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate

#all users that are not disabled but expired already

$ExpiredUsers=Get-ADUser -Filter {Enabled -eq $True -and objectCategory -eq "person" -and objectClass -eq "user"} -Properties sAMAccountName,DisplayName,LockedOut,LockoutTime,Enabled,AccountExpirationDate | where {(($_.AccountExpirationDate -lt (Get-Date) -and ($_.AccountExpirationDate -ne $null)</