{"id":377,"date":"2023-10-09T15:22:27","date_gmt":"2023-10-09T18:22:27","guid":{"rendered":"https:\/\/blog.4servers.co\/?p=377"},"modified":"2023-10-09T15:22:27","modified_gmt":"2023-10-09T18:22:27","slug":"usando-powershell-para-exportar-logonhours","status":"publish","type":"post","link":"https:\/\/blog.4srv.net\/index.php\/2023\/10\/09\/usando-powershell-para-exportar-logonhours\/","title":{"rendered":"Usando PowerShell para exportar LogonHours"},"content":{"rendered":"\n

Oi Pessoal!!!<\/p>\n\n\n\n

Hoje tive a necessidade de exportar de um AD (Active Directory) uma listagem de todos os usuarios com o limite de horas de acesso (a variavel LogonHours<\/em>), a\u00ed encontrei alguns scripts e tive que adapta-los para a necessidade, com isso resolvi publica-los para poder ajudar a outras pessoas assim como ele me ajudou, na realidade deu um trabalh\u00e3o gerar isso.<\/p>\n\n\n\n

Ent\u00e3o com acabei fazendo de duas formas, um em txt e o outro em csv.<\/p>\n\n\n\n

1. Arquivo PS1<\/h2>\n\n\n\n

Usando c\u00f3digo abaixo devemos criar um arquivo com a extens\u00e3o ps1<\/em><\/strong> (neste exemplo vou usar script.ps1<\/strong><\/em>)<\/p>\n\n\n

Function<\/span> OctetToHours<\/span> ($Octet<\/span>)<\/span><\/span>\n<\/span><\/span>{\n<\/span><\/span>    For<\/span> ($j<\/span> = 0<\/span>; $j<\/span> -le<\/span> 20<\/span>; $j<\/span> = $j<\/span> + 1<\/span>)\n<\/span><\/span>    {\n<\/span><\/span>        For<\/span> ($k<\/span> = 7<\/span>; $k<\/span> -ge<\/span> 0<\/span>; $k<\/span> = $k<\/span> - 1<\/span>)\n<\/span><\/span>        {\n<\/span><\/span>            $m<\/span> = 8<\/span>*$j<\/span> + $k<\/span> - $Bias<\/span>\n<\/span><\/span>            If<\/span> ($m<\/span> -lt<\/span> 0<\/span>) {$m<\/span> = $m<\/span> + 168<\/span>}\n<\/span><\/span>            If<\/span> ($Octet<\/span>[$j<\/span>] -band<\/span> [Math<\/span>]::Pow(2<\/span>, $k<\/span>)) {$LH<\/span>[$m<\/span>] = \"1\"<\/span>}\n<\/span><\/span>            Else<\/span> {$LH<\/span>[$m<\/span>] = \"0\"<\/span>}\n<\/span><\/span>        }\n<\/span><\/span>    }\n<\/span><\/span>    \"            --------------- Horas do dia ------------------\"<\/span>\n<\/span><\/span>    \"             0-3   3-6   6-9  9-12  12-15 15-18 18-21 21-0\"<\/span>\n<\/span><\/span>    $arrDays<\/span> = (\"Dom\"<\/span>, \"Seg\"<\/span>, \"Ter\"<\/span>, \"Qua\"<\/span>, \"Qui\"<\/span>, \"Sex\"<\/span>, \"Sab\"<\/span>)\n<\/span><\/span>    \n<\/span><\/span>    For<\/span> ($j<\/span> = 0<\/span>; $j<\/span> -le<\/span> 167<\/span>; $j<\/span> = $j<\/span> + 1<\/span>)\n<\/span><\/span>    {\n<\/span><\/span>        If<\/span> (($j<\/span> % 24<\/span>) -eq<\/span> 0<\/span>)\n<\/span><\/span>        {\n<\/span><\/span>            $Line<\/span> = \"    \"<\/span> + $arrDays<\/span>[$j<\/span> \/ 24<\/span>] + \"   \"<\/span>\n<\/span><\/span>        }\n<\/span><\/span>        If<\/span> (($j<\/span> %3<\/span>) -eq<\/span> 0<\/span>)\n<\/span><\/span>        {\n<\/span><\/span>            $Line<\/span> = $Line<\/span> + \"   \"<\/span>\n<\/span><\/span>        }\n<\/span><\/span>        $Line<\/span> = $Line<\/span> + $LH<\/span>[$j<\/span>]<\/span>\n<\/span><\/span>        If<\/span> ((($j<\/span> + 1<\/span>) % 24<\/span>) -eq<\/span> 0<\/span>) {$Line<\/span>}\n<\/span><\/span>    }\n<\/span><\/span>}\n<\/span><\/span>\n<\/span><\/span>$Domain<\/span> = New-Object<\/span> System.DirectoryServices.DirectoryEntry\n<\/span><\/span>$Searcher<\/span> = New-Object<\/span> System.DirectoryServices.DirectorySearcher\n<\/span><\/span>$Searcher<\/span>.SearchRoot = $Domain<\/span>\n<\/span><\/span>$Searcher<\/span>.PageSize = 200<\/span>\n<\/span><\/span>$Searcher<\/span>.Filter = \"(&(objectCategory=person)(objectClass=user))\"<\/span>\n<\/span><\/span>\n<\/span><\/span>$Searcher<\/span>.PropertiesToLoad.Add(\"logonHours\"<\/span>) > $Null<\/span>\n<\/span><\/span>$Searcher<\/span>.PropertiesToLoad.Add(\"distinguishedName\"<\/span>) > $Null<\/span>\n<\/span><\/span>$Searcher<\/span>.PropertiesToLoad.Add(\"name\"<\/span>) > $Null<\/span>\n<\/span><\/span>$Results<\/span> = $Searcher<\/span>.FindAll()\n<\/span><\/span>\n<\/span><\/span>$Bias<\/span> = [Math<\/span>]::Round((Get-ItemProperty<\/span> `\n<\/span><\/span>    -Path<\/span> HKLM:\\System\\CurrentControlSet\\Control\\TimeZoneInformation).Bias\/60<\/span>, `\n<\/span><\/span>    0<\/span>, [MidpointRounding<\/span>]::AwayFromZero)\n<\/span><\/span>\n<\/span><\/span>$LH<\/span> = New-Object<\/span> 'object[]'<\/span> 168<\/span>\n<\/span><\/span>\n<\/span><\/span>ForEach<\/span> ($Result<\/span> in<\/span> $Results<\/span>)\n<\/span><\/span>{\n<\/span><\/span>    $DN<\/span> = \"`r`n`r`n Usuario: \"<\/span> + $Result<\/span>.Properties.Item(\"name\"<\/span>)\n<\/span><\/span>    $Values<\/span> = $Result<\/span>.Properties.Item(\"logonHours\"<\/span>)\n<\/span><\/span>    $DN<\/span>\n<\/span><\/span>    $Flag<\/span> = $False<\/span>\n<\/span><\/span>    ForEach<\/span> ($Value<\/span> In<\/span> $Values<\/span>)\n<\/span><\/span>    {\n<\/span><\/span>        OctetToHours $Value<\/span>\n<\/span><\/span>        $Flag<\/span> = $True<\/span>\n<\/span><\/span>    }\n<\/span><\/span>    If<\/span> ($Flag<\/span> -eq<\/span> $False<\/span>) {\"    Liberado Geral\"<\/span>}\n<\/span><\/span>}\n<\/span><\/span><\/code><\/span>Code language:<\/span> PowerShell<\/span> (<\/span>powershell<\/span>)<\/span><\/small><\/pre>\n\n\n
Depois de voce copiar o codigo acima e criar o arquivo descrito, abrimos o PowerShell (eu particularmente gosto muito de usar a interface ISE).<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Para poder conectar corretamente no dominio e exportar os dados que necessitamos, devemos executar o PowerShell como administrador.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Com o programa aberto, podemos abrir o script e verificar a sintaxe.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
E finalmente para executar digitamoso comando como na figura acima.<\/p>\n\n\n\n
.\/script.ps1 * > result.txt<\/strong><\/p>\n\n\n\n
e o resultado do comando \u00e9 escrito no arquivo texto.<\/p>\n\n\n\n
O retorno do programa ser\u00e1 parecido com esse:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
2. Segundo Exemplo<\/h2>\n\n\n\n
O segundo exemplo eu exportei as informa\u00e7\u00f5es para um arquivo csv que da para abrir no excel, da mesma forma que o exemplo anterior, podemos abrir na interface do PowerShell e executar diretamente nele, clicando em executar ou apretando F5<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Nesta exemplo o resultado desse comando sera exportado para o arquivo usuarios.csv<\/em><\/strong><\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Depois de pronto e antes de abrir o arquivo no Excel uma dica de ouro, \u00e9 abrir o arquivo no notepad++ ou qualquer editor de textos e adicionar a linha<\/p>\n\n\n\n
SEP=,<\/strong><\/em><\/p>\n\n\n\n
antes de todo o resultado , isso far\u00e1 com que o excel ja reconhe\u00e7a as separa\u00e7\u00f5es do arquivo e j\u00e1 o abra formatado<\/p>\n\n\n\n
O codigo completo deste segundo exemplo est\u00e1 logo abaixo:<\/p>\n\n\n
Function<\/span> Convert-ADLogonHours<\/span><\/span> {\n<\/span><\/span>    Param<\/span>(\n<\/span><\/span>        [Parameter<\/span>(Mandatory<\/span>=$True<\/span>,ValueFromPipeline<\/span>=$True<\/span>)]\n<\/span><\/span>        [ValidateNotNullOrEmpty<\/span>()]\n<\/span><\/span>        [Microsoft.ActiveDirectory.Management.ADUser<\/span>]$User<\/span>,\n<\/span><\/span>        [Parameter<\/span>(Mandatory<\/span>=$False<\/span>)]\n<\/span><\/span>        [Switch<\/span>]$NoWarnings<\/span> = $False<\/span>\n<\/span><\/span>    )\n<\/span><\/span>    Process<\/span> {\n<\/span><\/span>        $Properties<\/span> = $User<\/span> | Get-Member<\/span> -MemberType<\/span> Property | Select-Object<\/span> -ExpandProperty<\/span> Name\n<\/span><\/span>        If<\/span> ($Properties<\/span> -contains<\/span> \"LogonHours\"<\/span>) {\n<\/span><\/span>            If<\/span> ($User<\/span>.LogonHours.Count -eq<\/span> 21<\/span>) {\n<\/span><\/span>                $ExportArray<\/span> = For<\/span> ($Inc<\/span> = 0<\/span>; $Inc<\/span> -lt<\/span> $User<\/span>.LogonHours.Count; $Inc<\/span> += 3<\/span>) {\n<\/span><\/span>        \n<\/span><\/span>                    $EndIndex<\/span> = $Inc<\/span> + 2<\/span>\n<\/span><\/span>\n<\/span><\/span>                    Switch<\/span> ($Inc<\/span>) {\n<\/span><\/span>                        0<\/span> {$DayOfWeek<\/span> = \"Domingo\"<\/span>}\n<\/span><\/span>                        3<\/span> {$DayOfWeek<\/span> = \"Segunda\"<\/span>}\n<\/span><\/span>                        6<\/span> {$DayOfWeek<\/span> = \"Terca\"<\/span>}\n<\/span><\/span>                        9<\/span> {$DayOfWeek<\/span> = \"Quarta\"<\/span>}\n<\/span><\/span>                        12<\/span> {$DayOfWeek<\/span> = \"Quinta\"<\/span>}\n<\/span><\/span>                        15<\/span> {$DayOfWeek<\/span> = \"Sexta\"<\/span>}\n<\/span><\/span>                        18<\/span> {$DayOfWeek<\/span> = \"Sabado\"<\/span>}\n<\/span><\/span>                    }\n<\/span><\/span>\n<\/span><\/span>                    $BinaryArray<\/span> = $User<\/span>.LogonHours[$Inc<\/span>..<\/span>$EndIndex<\/span>] | ForEach<\/span> {\n<\/span><\/span>                        $TempArray<\/span> = [Convert<\/span>]::ToString($_<\/span>,2<\/span>).PadLeft(8<\/span>,'0'<\/span>).ToCharArray()\n<\/span><\/span>                        [Array<\/span>]::Reverse($TempArray<\/span>)\n<\/span><\/span>                        $TempArray<\/span>\n<\/span><\/span>                    }\n<\/span><\/span>\n<\/span><\/span>                    $ExportObj<\/span> = New-Object<\/span> PsObject\n<\/span><\/span>                    $ExportObj<\/span> | Add-Member<\/span> -MemberType<\/span> NoteProperty -Name<\/span> \"Nome\"<\/span> -Value<\/span> $User<\/span>.Name\n<\/span><\/span>                    $ExportObj<\/span> | Add-Member<\/span> -MemberType<\/span> NoteProperty -Name<\/span> \"Ultimo Login\"<\/span> -Value<\/span> $User<\/span>.LastLogonDate\n<\/span><\/span>                    $ExportObj<\/span> | Add-Member<\/span> -MemberType<\/span> NoteProperty -Name<\/span> \"Dia das semana\"<\/span> -Value<\/span> $DayOfWeek<\/span>\n<\/span><\/span>\n<\/span><\/span>                    For<\/span> ($Hour<\/span> = 0<\/span>; $Hour<\/span> -lt<\/span> 24<\/span>; $Hour<\/span>++) {\n<\/span><\/span>\n<\/span><\/span>                        If<\/span> ($Hour<\/span> -eq<\/span> 23<\/span>) {$EndHour<\/span> = 0<\/span>}\n<\/span><\/span>                        Else<\/span> {$EndHour<\/span> = $Hour<\/span> + 1<\/span>}\n<\/span><\/span>\n<\/span><\/span>                        Switch<\/span> ($BinaryArray<\/span>[$Hour<\/span>]) {\n<\/span><\/span>                            '1'<\/span> {$LogonAuth<\/span> = 'Permitido'<\/span>}\n<\/span><\/span>                            '0'<\/span> {$LogonAuth<\/span> = 'Negado'<\/span>}\n<\/span><\/span>                        }\n<\/span><\/span>                \n<\/span><\/span>                        $ExportObj<\/span> | Add-Member<\/span> -MemberType<\/span> NoteProperty -Name<\/span> \"$Hour<\/span>-$EndHour<\/span> h\"<\/span> -Value<\/span> $LogonAuth<\/span>\n<\/span><\/span>                    }\n<\/span><\/span>                    $ExportObj<\/span>\n<\/span><\/span>                }\n<\/span><\/span>                Return<\/span> $ExportArray<\/span>\n<\/span><\/span>            }\n<\/span><\/span>            ElseIf<\/span> (!$NoWarnings<\/span>) {Write-Warning<\/span> -Message<\/span> \"Hora de Logon incorreta para: $_<\/span>\"<\/span>}\n<\/span><\/span>        }\n<\/span><\/span>        ElseIf<\/span> (!$NoWarnings<\/span>) {\n<\/span><\/span>        \n<\/span><\/span>        \n<\/span><\/span>        Write-Warning<\/span> -Message<\/span> \"Hora nao definida para object: $_<\/span> \"<\/span>}\n<\/span><\/span>    }\n<\/span><\/span>}\n<\/span><\/span>\n<\/span><\/span>Get-ADUser<\/span> -Filter<\/span> * -SearchBase<\/span> \"OU=Usuarios,DC=dominio,DC=com\"<\/span> -Properties<\/span> LogonHours, LastLogonDate  | Convert-ADLogonHours<\/span> |Export-csv<\/span> -path<\/span> c:\\usuarios.csv\n<\/span><\/span><\/code><\/span>Code language:<\/span> PowerShell<\/span> (<\/span>powershell<\/span>)<\/span><\/small><\/pre>\n\n\n
<\/p>\n\n\n\n
Espero que estas duas dicas possam ajudar… At\u00e9 a proxima!!!<\/p>\n\n\n\n
<\/p>\n\n\n\n
Atualiza\u00e7\u00e3o<\/h1>\n\n\n\n
No primeiro exemplo tive de fazer uma altera\u00e7\u00e3o para poder capturar todos os usu\u00e1rios ativos do sistema, sendo assim como j\u00e1 existia um grupo de seguran\u00e7a com estes usu\u00e1rios foi mais f\u00e1cil, bastou criar um filtro no padr\u00e3o LDAP para esse grupo (GG_Todos) e colocar no programa e executa-lo novamente isso foi feito na linha 36<\/a><\/strong> como no c\u00f3digo abaixo.<\/p>\n\n\n
$Searcher<\/span>.Filter = \"(&(&(|(&(objectCategory=person)(objectSid=<em>)(!samAccountType:1.2.840.113556.1.4.804:=3))(&(objectCategory=person)(!objectSid=<\/em>))(&(objectCategory=group)(groupType:1.2.840.113556.1.4.804:=14)))(objectCategory=user)(objectClass=user)(memberOf=CN=GG_Todos,OU=Grupos,OU=Rede,DC=dominio,DC=com)))\"<\/span>\n<\/span><\/span><\/code><\/span>Code language:<\/span> PowerShell<\/span> (<\/span>powershell<\/span>)<\/span><\/small><\/pre>\n\n\n
<\/p>\n\n\n\n
Get-ADUser<\/span> -LDAPFilter<\/span> \"(&(&(|(&(objectCategory=person)(objectSid=<em>)(!samAccountType:1.2.840.113556.1.4.804:=3))(&(objectCategory=person)(!objectSid=<\/em>))(&(objectCategory=group)(groupType:1.2.840.113556.1.4.804:=14)))(objectCategory=user)(objectClass=user)(memberOf=CN=GG_Todos,OU=Grupos,OU=Rede,DC=heca,DC=com)))\"<\/span> -SearchBase<\/span> \"OU=Usuarios,DC=dominio,DC=com\"<\/span> -Properties<\/span> LogonHours, LastLogonDate | Convert-ADLogonHours<\/span>\n<\/span><\/span><\/code><\/span>Code language:<\/span> PowerShell<\/span> (<\/span>powershell<\/span>)<\/span><\/small><\/pre>\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Como exportar de um AD (Active Directory) uma listagem de todos os usuarios com o limite de horas de acesso<\/p>\n","protected":false},"author":1,"featured_media":392,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,21,18],"tags":[31],"class_list":["post-377","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-powershell","category-scripts","category-windowsserver","tag-active-directory"],"_links":{"self":[{"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/posts\/377","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/comments?post=377"}],"version-history":[{"count":28,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/posts\/377\/revisions"}],"predecessor-version":[{"id":431,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/posts\/377\/revisions\/431"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/media\/392"}],"wp:attachment":[{"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/media?parent=377"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/categories?post=377"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.4srv.net\/index.php\/wp-json\/wp\/v2\/tags?post=377"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
J\u00e1 no segundo script alteramos a linha 63<\/a> para podermos aplicar o mesmo filtro.<\/p>\n\n\n