{"id":1944,"date":"2016-11-27T16:29:14","date_gmt":"2016-11-27T14:29:14","guid":{"rendered":"http:\/\/workplace.skyworker.de\/?p=1944"},"modified":"2023-08-22T10:39:17","modified_gmt":"2023-08-22T08:39:17","slug":"appsense-personalisierung-datengroesse-pro-benutzer-fuer-eine-personalisierungsgruppe-auslesen","status":"publish","type":"post","link":"http:\/\/workplace.skyworker.de\/?p=1944","title":{"rendered":"AppSense – Personalisierung: Datengr\u00f6\u00dfe pro Benutzer f\u00fcr eine Personalisierungsgruppe auslesen"},"content":{"rendered":"
\r\n
\r\n \r\n <\/i>\r\n <\/a>\r\n 0 <\/span>\r\n<\/div>
\r\n \r\n <\/i>\r\n <\/a>\r\n 0<\/span>\r\n<\/div><\/div>

Das folgende Skript liest die Daten pro Benutzer f\u00fcr eine dedizierte Personalisierungsgruppe aus und addiert diese zusammen. Die Abfrage findet direkt gegen die AppSense Personalisierungsdatenbank statt. Die Ausgabe ist eine csv Datei die Kommasepariert den Benutzernamen und die Datenmenge in Kilobyte angibt.<\/p>\n

Beispiel:
\nuser1,9478
\nuser2,162
\nuser3,1566
\nuser4,2764<\/p>\n

Unter Variablen muss die Personalisierungsgruppe, der Datenbankserver und der Datenbankname (Personalisierungsdatenbank) angegeben werden!<\/p>\n

[ps]
\n#—–Auslesen des Skriptpfad———————————————–
\nfunction Get-ScriptDirectory {
\n$Invocation = (Get-Variable MyInvocation -Scope 1).Value
\nSplit-Path $Invocation.MyCommand.Path
\n}
\n#—————————————————————————
\n#—–Variablen————————————————————-
\n$global:PersonalizationGroupName = "PERSONALISIERUNGSGRUPPE"
\n$global:SQLServer = "DATENBANKSERVER\\INSTANZ"
\n$global:DatabaseName = "DATENBANKNAME"
\n$global:cc_multi_output = ""
\n$global:PersonalizationGroupID = ""
\n$global:LogFile = Join-Path (Get-ScriptDirectory) "AppSense_Get.log"
\n#—————————————————————————
\n#—–Read user————————————————————-
\n$logText = "{0:yyyy-MM-dd\u00a0 HH-mm-ss} – No user file found" -f (Get-Date)
\n$userlist = Join-Path (Get-ScriptDirectory) "user.txt"
\nIf (-NOT (test-path "$userlist")){$logText | out-file -Append $global:LogFile}
\nElse {$global:UserList = Get-Content $userlist}
\n#—————————————————————————
\n#—–Write user————————————————————
\n$Global:UserOutList = Join-Path (Get-ScriptDirectory) "user_out.csv"
\nIf (test-path $Global:UserOutList){remove-item $Global:UserOutList}
\n#—————————————————————————
\n#—-Datenbankzugriff \/ Auslesen der Pakete \/ Ablage in Datei—————
\nfunction fu_execute-sql-multi-output {
\nparam (
\n[string]$dbserver,
\n[string]$db,
\n[string]$SQLCommand
\n)
\n#Connection to SQL-Server
\n$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
\n$SqlConnection.ConnectionString = "Server = $dbserver; Database = $db; Integrated Security = True"
\n$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
\n$SqlCmd.CommandText = $SQLCommand
\n$SqlCmd.Connection = $SqlConnection
\n$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
\n$SqlAdapter.SelectCommand = $SqlCmd
\n$global:cc_multi_output = New-Object System.Data.DataSet
\n$SqlAdapter.Fill($global:cc_multi_output)
\n$SqlConnection.Close()
\n}
\n#—————————————————————————
\n#—-Get Personalization Group ID——————————————-
\n$cc_output_count = fu_execute-sql-multi-output $global:SQLServer $global:DatabaseName "SELECT * FROM UserGroup where name = ‚$global:PersonalizationGroupName’"
\n$global:UserGroup = $global:cc_multi_output
\n$global:cc_multi_output = ""
\nforeach ($entry in $global:UserGroup.Tables[0]){
\n$global:PersonalizationGroupID = $global:UserGroup.Tables[0].Rows[0][0]
\n}
\n$logText = "{0:yyyy-MM-dd\u00a0 HH-mm-ss} – GroupName: $global:PersonalizationGroupName \/ GroupID: $global:PersonalizationGroupID" -f (Get-Date)
\n$logText | out-file -Append $global:LogFile
\n#—————————————————————————
\n#—-Get User IDs———————————————————–
\n$cc_output_count = fu_execute-sql-multi-output $global:SQLServer $global:DatabaseName "SELECT * FROM [User]"
\n$global:UserTable = $global:cc_multi_output
\n$global:cc_multi_output = ""
\n#—————————————————————————
\n#—-Get Application Profile Data——————————————-
\n$cc_output_count = fu_execute-sql-multi-output $global:SQLServer $global:DatabaseName "SELECT * FROM ApplicationProfile"
\n$global:ApplicationProfileTable = $global:cc_multi_output
\n$global:cc_multi_output = ""
\n#—————————————————————————
\n#—-Get user data size—————————————————–
\n$h = 0
\n$global:Size = 0
\nforeach ($entry in $global:UserTable.Tables[0]){
\n$userPK = $global:UserTable.Tables[0].Rows[$h][0]
\n$userName = $global:UserTable.Tables[0].Rows[$h][10]
\n$i = 0
\n$global:Size = 0
\n$global:InMigration = 0
\nforeach ($entry1 in $global:ApplicationProfileTable.Tables[0]){
\n$userFK = $global:ApplicationProfileTable.Tables[0].Rows[$i][0]<\/p>\n

If ($userFK -eq $userPK){
\n$UserGroupFK = $global:ApplicationProfileTable.Tables[0].Rows[$i][1]
\nIf ($global:PersonalizationGroupID -eq $UserGroupFK){
\n$global:InMigration = 1
\n$StoredSize = $global:ApplicationProfileTable.Tables[0].Rows[$i][9]
\nIf ([string]::IsNullOrEmpty($StoredSize)){$StoredSize = 0}
\n$global:Size = $global:Size + $StoredSize
\n}
\n}
\n$i++
\n}
\n$h++
\nIf ($global:InMigration -eq 1){
\n[INT]$SizeInKB = $global:Size \/ 1024
\n$OutText = "$userName,$SizeInKB"
\n$OutText | out-file -Append $Global:UserOutList
\nwrite-host $OutText
\n}
\n}
\n[\/ps]<\/p>\n","protected":false},"excerpt":{"rendered":"

0 0 Das folgende Skript liest die Daten pro Benutzer f\u00fcr eine dedizierte Personalisierungsgruppe aus und addiert diese zusammen. Die Abfrage findet direkt gegen die AppSense Personalisierungsdatenbank statt. Die Ausgabe ist eine csv Datei die Kommasepariert den Benutzernamen und die Datenmenge in Kilobyte angibt.<\/p>\n","protected":false},"author":1,"featured_media":1943,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[115,116],"tags":[117,119,118,120],"_links":{"self":[{"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/posts\/1944"}],"collection":[{"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1944"}],"version-history":[{"count":5,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/posts\/1944\/revisions"}],"predecessor-version":[{"id":1951,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/posts\/1944\/revisions\/1951"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=\/wp\/v2\/media\/1943"}],"wp:attachment":[{"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1944"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/workplace.skyworker.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}