The report essentially provides a list of all systems that have certain software installed, and presents the CPU and Operating System information for each.
The software titles to include in the report are retrieved from a separate database which can be edited by end-users with Orchestrator runbooks.
(Removed server/domain data)
The Report
The recipients of the report wanted to retrieve a list of systems based on a predefined software list (Microsoft SQL Server, Symantec NetBackup Client etc etc). I was keen not to hard code this list of software into the report query because A: there were nearly 100 software titles to report on, which would look pretty messy in the query, and B: Software titles needed to be added and removed easily.
I created a couple of data tables on a separate database (CM_Runtime) to hold the lists of software they wanted to report on, one for Windows (dbo.reportsoftware) and the other for Linux (dbo.reportapplications).
SCCM inventory data for Linux applications is stored in 'System_ADD_REMOVE_PROGRAMS0.DisplayName00' where-as the Windows software we wanted to retrieve was in 'System_INSTALLED_SOFTWARE0.ARPDisplayName00'
The query to retrieve the system lists based on the contents of dbo.reportsoftware and dbo.reportapplications is below.
select distinct
SMS_R_System.Name0 AS [Server],
SMS_R_System.Resource_Domain_OR_Workgr0 AS Domain,
___System_INSTALLED_SOFTWARE0.ARPDisplayName00 AS [Application Name],
SMS_G_System_PROCESSOR.NumberOfCores00 AS [Number of Cores],
SMS_G_System_COMPUTER_SYSTEM.NumberOfProcessors00 AS [Number of Processors],
SMS_G_System_PROCESSOR.NumberOfLogicalProcessors00 AS [Number of Logical CPUs],
SMS_G_System_OPERATING_SYSTEM.Caption00 AS [Operating System]
from
vSMS_R_System AS SMS_R_System
INNER JOIN INSTALLED_SOFTWARE_DATA AS ___System_INSTALLED_SOFTWARE0 ON ___System_INSTALLED_SOFTWARE0.MachineID = SMS_R_System.ItemKey
INNER JOIN Processor_DATA AS SMS_G_System_PROCESSOR ON SMS_G_System_PROCESSOR.MachineID = SMS_R_System.ItemKey
INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey
INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey
INNER JOIN _RES_COLL_SMSDM003 AS SMS_CM_RES_COLL_SMSDM003 ON SMS_CM_RES_COLL_SMSDM003.MachineID = SMS_R_System.ItemKey
where
___System_INSTALLED_SOFTWARE0.ARPDisplayName00 in
( SELECT InstalledSoftware
FROM CM_RunTime.dbo.reportsoftware )
UNION
select distinct
SMS_R_System.Name0 AS [Server],
SMS_R_System.Resource_Domain_OR_Workgr0 AS Domain,
__System_ADD_REMOVE_PROGRAMS0.DisplayName00 AS [Application Name],
SMS_G_System_PROCESSOR.NumberOfCores00 AS [Number of Cores],
SMS_G_System_COMPUTER_SYSTEM.NumberOfProcessors00 AS [Number of Processors],
SMS_G_System_PROCESSOR.NumberOfLogicalProcessors00 AS [Number of Logical CPUs],
SMS_G_System_OPERATING_SYSTEM.Caption00 AS [Operating System]
from
vSMS_R_System AS SMS_R_System
INNER JOIN Add_Remove_Programs_DATA AS __System_ADD_REMOVE_PROGRAMS0 ON __System_ADD_REMOVE_PROGRAMS0.MachineID = SMS_R_System.ItemKey
INNER JOIN Processor_DATA AS SMS_G_System_PROCESSOR ON SMS_G_System_PROCESSOR.MachineID = SMS_R_System.ItemKey
INNER JOIN Computer_System_DATA AS SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.MachineID = SMS_R_System.ItemKey
INNER JOIN Operating_System_DATA AS SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.MachineID = SMS_R_System.ItemKey
where __System_ADD_REMOVE_PROGRAMS0.DisplayName00
in
( SELECT InstalledApplications
FROM CM_RunTime.dbo.reportapplications )
Adding and Removing Software in Report
I made an initial bulk import of software to the data tables using PowerShell, but for ongoing management I've configured Orchestrator to remove or add rows to the tables using a couple of runbooks:
(Remove Software title)
(Add Software title)
Each runbook initialize data properties are as follows:
To add a software title the 'Write to Database' activity is used.
To remove a software title the 'Query Database' activity is used.
A list of the current software titles held in the data tables is sent each time the tables are edited using a PowerShell script to retrieve the data and create a CSV, and then it's emailed.
# Set script parameters from runbook data bus and Orchestrator global variables
# Define any inputs here and then add to the $argsArray and script block parameters below
$ReportPath = "D:\SCORCH Data\SoftwareReports\softwarereport-$(Get-Date -Format yyyyMMddHHmmss).csv"
$Username = "#SQLUSER#"
$Password = "#PASSWORD#"
$Database = "CM_Runtime"
$SQLServer = "#SQLSERVERNAME#"
#-----------------------------------------------------------------------
## Initialize result and trace variables
# $ResultStatus provides basic success/failed indicator
# $ErrorMessage captures any error text generated by script
# $Trace is used to record a running log of actions
$ResultStatus = ""
$ErrorMessage = ""
$Trace = (Get-Date).ToString() + "`t" + "Runbook activity script started" + " `r`n"
# Create argument array for passing data bus inputs to the external script session
$argsArray = @()
$argsArray += $ReportPath
$argsArray += $Username
$argsArray += $Password
$argsArray += $Database
$argsArray += $SQLServer
# Establish an external session (to localhost) to ensure 64bit PowerShell runtime using the latest version of PowerShell installed on the runbook server
# Use this session to perform all work to ensure latest PowerShell features and behavior available
$Session = New-PSSession -ComputerName localhost
# Invoke-Command used to start the script in the external session. Variables returned by script are then stored in the $ReturnArray variable
$ReturnArray = Invoke-Command -Session $Session -Argumentlist $argsArray -ScriptBlock {
# Define a parameter to accept each data bus input value. Recommend matching names of parameters and data bus input variables above
Param(
[ValidateNotNullOrEmpty()]
[string]$ReportPath,
[ValidateNotNullOrEmpty()]
[string]$Username,
[ValidateNotNullOrEmpty()]
[string]$Password,
[ValidateNotNullOrEmpty()]
[string]$Database,
[ValidateNotNullOrEmpty()]
[string]$SQLServer
)
# Define function to add entry to trace log variable
function AppendLog ([string]$Message)
{
$script:CurrentAction = $Message
$script:TraceLog += ((Get-Date).ToString() + "`t" + $Message + " `r`n")
}
# Set external session trace and status variables to defaults
$ResultStatus = ""
$ErrorMessage = ""
$script:CurrentAction = ""
$script:TraceLog = ""
try
{
# Add startup details to trace log
AppendLog "Script now executing in external PowerShell version [$($PSVersionTable.PSVersion.ToString())] session in a [$([IntPtr]::Size * 8)] bit process"
AppendLog "Running as user [$([Environment]::UserDomainName)\$([Environment]::UserName)] on host [$($env:COMPUTERNAME)]"
AppendLog "Parameter values received:ReportPath=[$ReportPath] Database=[$Database] SQLServer=[$SQLServer]"
# The actual work the script does goes here
AppendLog "SQL Query to retrieve"
$Array = @(Invoke-SQLCmd -query "SELECT InstalledApplications AS [Software] FROM dbo.reportapplications UNION SELECT InstalledSoftware AS [Software] FROM dbo.reportsoftware" -Server $SQLServer -Username $Username -Password $Password -Database $Database)
AppendLog "Writing to CSV File"
$Array | ConvertTo-Csv -NoTypeInformation |
% {$_.Replace('"','')} |
Out-File $ReportPath -Encoding ascii
# Validate results and set return status
AppendLog "Finished work, determining result"
$EverythingWorked = $true
if($EverythingWorked -eq $true)
{
$ResultStatus = "Success"
}
else
{
$ResultStatus = "Failed"
}
}
catch
{
# Catch any errors thrown above here, setting the result status and recording the error message to return to the activity for data bus publishing
$ResultStatus = "Failed"
$ErrorMessage = $error[0].Exception.Message
AppendLog "Exception caught during action [$script:CurrentAction]: $ErrorMessage"
}
finally
{
# Always do whatever is in the finally block. In this case, adding some additional detail about the outcome to the trace log for return
if($ErrorMessage.Length -gt 0)
{
AppendLog "Exiting external session with result [$ResultStatus] and error message [$ErrorMessage]"
}
else
{
AppendLog "Exiting external session with result [$ResultStatus]"
}
}
# Return an array of the results. Additional variables like "myCustomVariable" can be returned by adding them onto the array
$resultArray = @()
$resultArray += $ResultStatus
$resultArray += $ErrorMessage
$resultArray += $script:TraceLog
$resultArray += $myCustomVariable
return $resultArray
}#End Invoke-Command
# Get the values returned from script session for publishing to data bus
$ResultStatus = $ReturnArray[0]
$ErrorMessage = $ReturnArray[1]
$Trace += $ReturnArray[2]
$MyCustomVariable = $ReturnArray[3]
# Record end of activity script process
$Trace += (Get-Date).ToString() + "`t" + "Script finished" + " `r`n"
# Close the external session
Remove-PSSession $Session
0 comments:
Post a Comment