Telegraf and PowerShell to create list of users in Grafana Table

Telegraf
1

I have a simple PowerShell script to get a total count of locked out account and also a list of them, because I want a Grafana dashboard that has both the total number of locked out account and then a table that will list all the accounts.

Here is the simple one liners:

#AD Accounts Disabled
$ADAccountDisabled=(Get-ADUser -properties * -filter {(enabled -eq $false)} | Select-Object Name)
$ADAccountDisabled_total = $ADAccountDisabled.count 

Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled_total=$ADAccountDisabled_total"
Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled=$ADAccountDisabled"

Here is the output:

PS C:\Telegraf\scripts> .\ADSecurityStats.ps1
ad_accounts,host=svr1.local,ad_value=ADAccountsTotal_total=45
ad_accounts,host=svr1.local,ad_value=ADAccountExpired_total=23
ad_accounts,host=svr1.local,ad_value=ADAccountDisabled_total=13
ad_accounts,host=svr1.local,ad_value=ADCountLockedOut_total=0
ad_accounts,host=svr1.local,ad_value=ADAccountExp7days_total=0
ad_accounts,host=svr1.local,ad_value=ADAccountInactive30days_total=23
ad_accounts,host=svr1.local,ad_value=ADPasswordNeverExpire_total=4
ad_accounts,host=svr1.local,ad_value=ADAccountExpired=
ad_accounts,host=svr1.local,ad_value=ADAccountDisabled=
ad_accounts,host=svr1.local,ad_value=ADCountLockedOut=
ad_accounts,host=svr1.local,ad_value=ADAccountExp7days=
ad_accounts,host=svr1.local,ad_value=ADAccountInactive30days=
ad_accounts,host=svr1.local,ad_value=ADPasswordNeverExpire=svc.activeroles@test.local svc.backup@test.local user1@test.local test@test.local

Here is my line in the telegraf configuration file:

  [[inputs.exec]] 
    commands = ['powershell "C:\\Telegraf\\scripts\\ADSecurityStats.ps1"'] timeout = "60s" data_format = "influx" interval = "300s"

I get the correct total value in the influxdb, but I don’t get the list of users. When I run the command I get a list of user that are separated with a space in a ling string. Is this the correct way of doing it or should I do it another way?

2

Hi,

ad_accounts,host=$hostname,ad_value=ADAccountDisabled=$ADAccountDisabled

Do you have a missing space? As-is this is not valid line protocol, you should be getting an error from telegraf as there is no space for a field, a metric with no fields is not a valid metric.

Can you print out the actual metric with [[outputs.file]]?

3

Here is my entire script with updates to place 0 when no list of users are returned

function Get-ADSecurity {
    $hostname = (Get-ADDomainController).domain
    $dte = (Get-Date).AddMinutes(-30)  # Last 30 minutes

    # Define the date ranges
    $Past30days = (Get-Date).AddDays(-30)
    $Future7days = (Get-Date).AddDays(7)

    ## AD Statistics total ##

    # AD Account 
    $ADAccountsTotal_total = (Get-ADUser -Filter {(enabled -eq $true)}).Count

    # AD Account Expired 30 days or longer
    $ADAccountExpired = Get-ADUser -Filter {(lastlogondate -le $Past30days)} -Properties LastLogonDate
    $ADAccountExpired_total = $ADAccountExpired.Count

    # AD Accounts Disabled
    $ADAccountDisabled = Get-ADUser -Filter {(enabled -eq $false)}
    $ADAccountDisabled_total = $ADAccountDisabled.Count

    # AD Accounts Locked out
    $ADCountLockedOut = Search-ADAccount -LockedOut
    $ADCountLockedOut_total = $ADCountLockedOut.Count

    # AD Accounts Expiring in the next 7 days
    $ADAccountExp7days = Search-ADAccount -AccountExpiring -TimeSpan "7"
    $ADAccountExp7days_total = $ADAccountExp7days.Count

    # AD Accounts not logged in for the last 30 days
    $ADAccountInactive30days = Get-ADUser -Filter {(lastlogondate -le $Past30days)} -Properties LastLogonDate
    $ADAccountInactive30days_total = $ADAccountInactive30days.Count

    # AD Passwords set never to expire
    $ADPasswordNeverExpire = Get-ADUser -Filter {PasswordNeverExpires -eq $true -and Enabled -eq $true} -Properties PasswordNeverExpires
    $ADPasswordNeverExpire_total = $ADPasswordNeverExpire.Count

    ## Password Reset stats per user ##
    $PWDUSERS = Get-ADUser -Filter 'PasswordLastSet -gt $dte' -Properties PasswordLastSet, PasswordNeverExpires | Where-Object { $_.PasswordNeverExpires -eq $true -and $_.Enabled -eq $true }
    $PWDUSERS_total = $PWDUSERS.Count

    foreach ($PWDUSER in $PWDUSERS) {
        $pwdResetTime = ([DateTimeOffset] $PWDUSER.PasswordLastSet).ToUnixTimeMilliseconds()
        $PWDUSERName = $PWDUSER.Name -replace '\s', '-'
        Write-Host "ad_accounts,host=$hostname,ad_value=PasswordLastSet,instance=$PWDUSERName status=1,pwdLastSetTime=$pwdResetTime"
    }

    # Write out data totals
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountsTotal total_=$ADAccountsTotal_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExpired total_=$ADAccountExpired_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled total_=$ADAccountDisabled_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADCountLockedOut total_=$ADCountLockedOut_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExp7days total_=$ADAccountExp7days_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountInactive30days total_=$ADAccountInactive30days_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADPasswordNeverExpire total_=$ADPasswordNeverExpire_total"

    # Additional detailed logs if needed
    if ($ADAccountExpired.Count -gt 0) {
        $ADAccountExpiredList = $ADAccountExpired | ForEach-Object { $_.Name }
        $ADAccountExpiredString = $ADAccountExpiredList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExpired value_=$ADAccountExpiredString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExpired value_=0"
    }

    if ($ADAccountDisabled.Count -gt 0) {
        $ADAccountDisabledList = $ADAccountDisabled | ForEach-Object { $_.Name }
        $ADAccountDisabledString = $ADAccountDisabledList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled value_=$ADAccountDisabledString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled value_=0"
    }

    if ($ADCountLockedOut.Count -gt 0) {
        $ADCountLockedOutList = $ADCountLockedOut | ForEach-Object { $_.Name }
        $ADCountLockedOutString = $ADCountLockedOutList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADCountLockedOut value_=$ADCountLockedOutString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADCountLockedOut value_=0"
    }

    if ($ADAccountExp7days.Count -gt 0) {
        $ADAccountExp7daysList = $ADAccountExp7days | ForEach-Object { $_.Name }
        $ADAccountExp7daysString = $ADAccountExp7daysList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExp7days value_=$ADAccountExp7daysString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExp7days value_=0"
    }

    if ($ADAccountInactive30days.Count -gt 0) {
        $ADAccountInactive30daysList = $ADAccountInactive30days | ForEach-Object { $_.Name }
        $ADAccountInactive30daysString = $ADAccountInactive30daysList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountInactive30days value_=$ADAccountInactive30daysString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountInactive30days value_=0"
    }

    if ($ADPasswordNeverExpire.Count -gt 0) {
        $ADPasswordNeverExpireList = $ADPasswordNeverExpire | ForEach-Object { $_.Name }
        $ADPasswordNeverExpireString = $ADPasswordNeverExpireList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADPasswordNeverExpire value_=$ADPasswordNeverExpireString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADPasswordNeverExpire value_=0"
    }
}

# Execute the function
Get-ADSecurity

When I run the script manually it returns correct values, real names have been removed.

PS C:\Telegraf\scripts> C:\Telegraf\scripts\ADSecurityStats.ps1
ad_accounts,host=ESD.local,ad_value=ADAccountsTotal total_=50
ad_accounts,host=ESD.local,ad_value=ADAccountExpired total_=10
ad_accounts,host=ESD.local,ad_value=ADAccountDisabled total_=8
ad_accounts,host=ESD.local,ad_value=ADCountLockedOut total_=0
ad_accounts,host=ESD.local,ad_value=ADAccountExp7days total_=0
ad_accounts,host=ESD.local,ad_value=ADAccountInactive30days total_=10
ad_accounts,host=ESD.local,ad_value=ADPasswordNeverExpire total_=14
ad_accounts,host=ESD.local,ad_value=ADAccountExpired value_=Administrator, SVC SQL
Admin2, 
ad_accounts,host=ESD.local,ad_value=ADAccountDisabled value_=Guest, krbtgt
ad_accounts,host=ESD.local,ad_value=ADCountLockedOut value_=0
ad_accounts,host=ESD.local,ad_value=ADAccountExp7days value_=0
ad_accounts,host=ESD.local,ad_value=ADAccountInactive30days value_=Administrator, 
ad_accounts,host=ESD.local,ad_value=ADPasswordNeverExpire value_=Administrator, SQL Admin, Active Roles SVC

However when looking at the output.metics file none of these values are in there, should they be? I see the other metrics that are gathered.

4

From your edit to your first post these are also invalid:

These are invalid as you have an empty value. A metric requires at least one field with a valid value. For example:

ad_accounts,host=test.local,ad_value=ADAccountExpired value=0

You’ll need to put some sort of place holder or initial value for those values to ensure they are read in.

5

Here is my entire script with updates to place 0 when no list of users are returned

function Get-ADSecurity {
    $hostname = (Get-ADDomainController).domain
    $dte = (Get-Date).AddMinutes(-30)  # Last 30 minutes

    # Define the date ranges
    $Past30days = (Get-Date).AddDays(-30)
    $Future7days = (Get-Date).AddDays(7)

    ## AD Statistics total ##

    # AD Account 
    $ADAccountsTotal_total = (Get-ADUser -Filter {(enabled -eq $true)}).Count

    # AD Account Expired 30 days or longer
    $ADAccountExpired = Get-ADUser -Filter {(lastlogondate -le $Past30days)} -Properties LastLogonDate
    $ADAccountExpired_total = $ADAccountExpired.Count

    # AD Accounts Disabled
    $ADAccountDisabled = Get-ADUser -Filter {(enabled -eq $false)}
    $ADAccountDisabled_total = $ADAccountDisabled.Count

    # AD Accounts Locked out
    $ADCountLockedOut = Search-ADAccount -LockedOut
    $ADCountLockedOut_total = $ADCountLockedOut.Count

    # AD Accounts Expiring in the next 7 days
    $ADAccountExp7days = Search-ADAccount -AccountExpiring -TimeSpan "7"
    $ADAccountExp7days_total = $ADAccountExp7days.Count

    # AD Accounts not logged in for the last 30 days
    $ADAccountInactive30days = Get-ADUser -Filter {(lastlogondate -le $Past30days)} -Properties LastLogonDate
    $ADAccountInactive30days_total = $ADAccountInactive30days.Count

    # AD Passwords set never to expire
    $ADPasswordNeverExpire = Get-ADUser -Filter {PasswordNeverExpires -eq $true -and Enabled -eq $true} -Properties PasswordNeverExpires
    $ADPasswordNeverExpire_total = $ADPasswordNeverExpire.Count

    ## Password Reset stats per user ##
    $PWDUSERS = Get-ADUser -Filter 'PasswordLastSet -gt $dte' -Properties PasswordLastSet, PasswordNeverExpires | Where-Object { $_.PasswordNeverExpires -eq $true -and $_.Enabled -eq $true }
    $PWDUSERS_total = $PWDUSERS.Count

    foreach ($PWDUSER in $PWDUSERS) {
        $pwdResetTime = ([DateTimeOffset] $PWDUSER.PasswordLastSet).ToUnixTimeMilliseconds()
        $PWDUSERName = $PWDUSER.Name -replace '\s', '-'
        Write-Host "ad_accounts,host=$hostname,ad_value=PasswordLastSet,instance=$PWDUSERName status=1,pwdLastSetTime=$pwdResetTime"
    }

    # Write out data totals
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountsTotal total_=$ADAccountsTotal_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExpired total_=$ADAccountExpired_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled total_=$ADAccountDisabled_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADCountLockedOut total_=$ADCountLockedOut_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExp7days total_=$ADAccountExp7days_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountInactive30days total_=$ADAccountInactive30days_total"
    Write-Host "ad_accounts,host=$hostname,ad_value=ADPasswordNeverExpire total_=$ADPasswordNeverExpire_total"

    # Additional detailed logs if needed
    if ($ADAccountExpired.Count -gt 0) {
        $ADAccountExpiredList = $ADAccountExpired | ForEach-Object { $_.Name }
        $ADAccountExpiredString = $ADAccountExpiredList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExpired value_=$ADAccountExpiredString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExpired value_=0"
    }

    if ($ADAccountDisabled.Count -gt 0) {
        $ADAccountDisabledList = $ADAccountDisabled | ForEach-Object { $_.Name }
        $ADAccountDisabledString = $ADAccountDisabledList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled value_=$ADAccountDisabledString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountDisabled value_=0"
    }

    if ($ADCountLockedOut.Count -gt 0) {
        $ADCountLockedOutList = $ADCountLockedOut | ForEach-Object { $_.Name }
        $ADCountLockedOutString = $ADCountLockedOutList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADCountLockedOut value_=$ADCountLockedOutString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADCountLockedOut value_=0"
    }

    if ($ADAccountExp7days.Count -gt 0) {
        $ADAccountExp7daysList = $ADAccountExp7days | ForEach-Object { $_.Name }
        $ADAccountExp7daysString = $ADAccountExp7daysList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExp7days value_=$ADAccountExp7daysString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountExp7days value_=0"
    }

    if ($ADAccountInactive30days.Count -gt 0) {
        $ADAccountInactive30daysList = $ADAccountInactive30days | ForEach-Object { $_.Name }
        $ADAccountInactive30daysString = $ADAccountInactive30daysList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountInactive30days value_=$ADAccountInactive30daysString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADAccountInactive30days value_=0"
    }

    if ($ADPasswordNeverExpire.Count -gt 0) {
        $ADPasswordNeverExpireList = $ADPasswordNeverExpire | ForEach-Object { $_.Name }
        $ADPasswordNeverExpireString = $ADPasswordNeverExpireList -join ", "
        Write-Host "ad_accounts,host=$hostname,ad_value=ADPasswordNeverExpire value_=$ADPasswordNeverExpireString"
    } else {
        Write-Host "ad_accounts,host=$hostname,ad_value=ADPasswordNeverExpire value_=0"
    }
}

# Execute the function
Get-ADSecurity

When I run the script manually it returns correct values, real names have been removed.

PS C:\Telegraf\scripts> C:\Telegraf\scripts\ADSecurityStats.ps1
ad_accounts,host=ESD.local,ad_value=ADAccountsTotal total_=50
ad_accounts,host=ESD.local,ad_value=ADAccountExpired total_=10
ad_accounts,host=ESD.local,ad_value=ADAccountDisabled total_=8
ad_accounts,host=ESD.local,ad_value=ADCountLockedOut total_=0
ad_accounts,host=ESD.local,ad_value=ADAccountExp7days total_=0
ad_accounts,host=ESD.local,ad_value=ADAccountInactive30days total_=10
ad_accounts,host=ESD.local,ad_value=ADPasswordNeverExpire total_=14
ad_accounts,host=ESD.local,ad_value=ADAccountExpired value_=Administrator, SVC SQL
Admin2....... 
ad_accounts,host=ESD.local,ad_value=ADAccountDisabled value_=Guest, krbtgt
ad_accounts,host=ESD.local,ad_value=ADCountLockedOut value_=0
ad_accounts,host=ESD.local,ad_value=ADAccountExp7days value_=0
ad_accounts,host=ESD.local,ad_value=ADAccountInactive30days value_=Administrator,...... 
ad_accounts,host=ESD.local,ad_value=ADPasswordNeverExpire value_=Administrator, SQL Admin........

Here is output of output.metrics, removed real names:

2024-06-25T17:51:37Z E! [inputs.exec] Error in plugin: metric parse error: expected field at 8:61: "ad_accounts,host=ESD.local,ad_value=ADAccountExpired value_=Administrator, SVC SQLAdmin2, switch admin, "
2024-06-25T17:51:37Z E! [telegraf] Error running agent: input plugins recorded 1 errors

Not sure what is expected?

6

Take a read about line protocol here: Line protocol | InfluxDB Cloud (TSM) Documentation

Spaces are used to seperate between metric name+tags and fields. If you have spaces in your field value, then you need to quote them. This would be valid:

ad_accounts,host=ESD.local,ad_value=ADAccountExpired value_="Administrator, SVC SQLAdmin2, switch admin"