Powershell: Auslesen welche Empirum Software sich in welcher Empirum Softwareklasse befindet

Für die, die in Empirum mit sehr vielen Softwareklassen arbeiten habe ich ein kleines PowerShell Skript geschrieben, über das geprüft werden kann, in welchen Softwareklassen sich eine Software befindet.

Um das Skript verwenden zu können, muss die Variable $global:cc_database_server  mit dem Namen des Datenbankservers (Dantenbankservername\Instanzname), sowie die Variable $global:cc_database_db mit dem Namen der Datenbank befüllt werden.

#-----Variablen-------------------------------------------------------------
$global:cc_database_server = "EmpirumServer\SQLExpress"
$global:cc_database_db = "EmpirumDB"

Powershell Skript:   [ddownload id=522]

[php]cls

#—–Variablen————————————————————-
$global:cc_database_server = "empirumserver\instanz"
$global:cc_database_db = "Empirum"
$global:cc_multi_output1 = ""
$global:cc_multi_output2 = ""
#—————————————————————————

#—–Auslesen des Skriptpfades———————————————
$global:ScriptDir = Split-Path
$MyInvocation.MyCommand.Path –Parent
#—————————————————————————

#—-Datenbankzugriff / Auslesen der Pakete / Ablage in Datei—————
function execute-sql-multi-output1 {
param (
[string]$dbserver,
[string]$db,
[string]$SQLCommand
)

#Connection to SQL-Server  $SqlConnection = New-Object System.Data.SqlClient.SqlConnection  $SqlConnection.ConnectionString = "Server = $dbserver; Database = $db; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLCommand
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd

$global:cc_multi_output1 = New-Object System.Data.DataSet  $SqlAdapter.Fill($global:cc_multi_output1)  $SqlConnection.Close()   }

$cc_output_count = execute-sql-multi-output1 "$global:cc_database_server" "$global:cc_database_db" "SELECT * FROM Classes"
$global:cc_ClassesInfo = $global:cc_multi_output1   $global:cc_multi_output1 = ""

$cc_output_count = execute-sql-multi-output1 "$global:cc_database_server" "$global:cc_database_db" "SELECT * FROM ClassesToSoftware"
$global:cc_ClassesToSoftware = $global:cc_multi_output1   $global:cc_multi_output1 = ""

$cc_output_count = execute-sql-multi-output1 "$global:cc_database_server" "$global:cc_database_db" "SELECT * FROM Software"
$global:cc_Software = $global:cc_multi_output1   $global:cc_multi_output1 = ""

#—————————————————————————

function fu_search () {
$Global:SelectedSoftware = $objComboBox.SelectedItem.ToString()
$Global:ClassesIDArray = @()
$Global:ClassesNameArray = @()
$global:output = ""

$h=0
foreach ($entry in $global:cc_Software.Tables[0]){
$cc_Software_SoftwareID = $global:cc_Software.Tables[0].Rows[$h][0]
$cc_Software_SoftwareName = $global:cc_Software.Tables[0].Rows[$h][3]
If ($cc_Software_SoftwareName -eq $Global:SelectedSoftware){
break
}
$h++
}

$h=0
foreach ($entry in $global:cc_ClassesToSoftware.Tables[0]){
$cc_ClassesToSoftware_ClassesID = $global:cc_ClassesToSoftware.Tables[0].Rows[$h][0]
$cc_ClassesToSoftware_SoftwareID = $global:cc_ClassesToSoftware.Tables[0].Rows[$h][1]
If ($cc_ClassesToSoftware_SoftwareID -eq $cc_Software_SoftwareID){
$Global:ClassesIDArray += $cc_ClassesToSoftware_ClassesID
}
$h++
}

$h=0
foreach ($entry in $global:cc_ClassesInfo.Tables[0]){
$cc_Classes_ClassesID = $global:cc_ClassesInfo.Tables[0].Rows[$h][0]
$cc_Classes_Name = $global:cc_ClassesInfo.Tables[0].Rows[$h][2]
foreach ($value in $Global:ClassesIDArray){
If ($value -eq $cc_Classes_ClassesID){
$Global:ClassesNameArray += $cc_Classes_Name
}
}
$h++
}

foreach ($entry in $Global:ClassesNameArray){
$global:output = $global:output + $entry + "`r`n"
}

If ($global:output) {$objTextBox1.Text = "$global:output"}
Else {$objTextBox1.Text = "Keine Zuweisung"}

$objForm.Update()

}

#—————————————————————————

#—–GUI——————————————————————-   [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")   [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

$objForm = New-Object System.Windows.Forms.Form   $objForm.Text = "Matrix42 Empirum Software Classes"   $objForm.Size = New-Object System.Drawing.Size(600,240)   $objForm.StartPosition = "CenterScreen"   $objForm.autoscroll = $false   $objForm.FormBorderStyle = 3

$objForm.KeyPreview = $True   $objForm.Add_KeyDown({if ($_.KeyCode -eq "Enter"){fu_search}})   $objForm.Add_KeyDown({if ($_.KeyCode -eq "Escape"){$objForm.Close()}})

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,32)
$objLabel.Size = New-Object System.Drawing.Size(120,20)
$objLabel.Font = new-object System.Drawing.Font("Arial",11,[Drawing.FontStyle]’regular‘ )   $objLabel.Text = "Softwarename"
$objForm.Controls.Add($objLabel)

$objComboBox = new-object System.Windows.Forms.ComboBox
$objComboBox.Location = new-object System.Drawing.Size(130,32)
$objComboBox.Size = new-object System.Drawing.Size(420,20)
$objComboBox.Font = new-object System.Drawing.Font("Arial",9 )
$j=0
foreach ($entry in $global:cc_Software.Tables[0]){
$cc_Software_SoftwareName = $global:cc_Software.Tables[0].Rows[$j][3]
$objComboBox.Items.Add($cc_Software_SoftwareName)  $j++   }
$objComboBox.Sorted = $true
$objForm.Controls.Add($objComboBox)

$SearchButton = New-Object System.Windows.Forms.Button
$SearchButton.Location = New-Object System.Drawing.Size(260,60)
$SearchButton.Size = New-Object System.Drawing.Size(80,23)
$SearchButton.Font = new-object System.Drawing.Font("Arial",10,[Drawing.FontStyle]’bold‘ )   $SearchButton.Text = "Suchen…"
$SearchButton.Add_Click({fu_search})
$objForm.Controls.Add($SearchButton)

$objTextBox1 = New-Object System.Windows.Forms.TextBox
$objTextBox1.Location = New-Object System.Drawing.Size(10,100)
$objTextBox1.Size = New-Object System.Drawing.Size(450,100)
$objTextBox1.Multiline = $true
$objTextBox1.ScrollBars = "Vertical"
$objTextBox1.Text = ""
$objForm.Controls.Add($objTextBox1)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(500,177)
$CancelButton.Size = New-Object System.Drawing.Size(80,23)
$CancelButton.Font = new-object System.Drawing.Font("Arial",10,[Drawing.FontStyle]’bold‘ )   $CancelButton.Text = "Close"
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)

$objForm.Topmost = $True

$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()
#—————————————————————————[/php]