SAM: Powershell impersonation queries

SteveK

I know a handful of us have ran into an issue with the legacy mode settings and powershell.  From what support has informed me, legacy mode is utilzing the account "nt authority\system" when the powershell monitor is set to local host mode.  I haven't had a chance to fully confirm the setting against local or remote host execution, but I wanted to see how my environment was configured on a component basis.  So I ended up writing 2 different queries to determine this.

The first query is based off of the component template that lives within the application template.  It filters down off of the component type 45 which is powershell.  It builds similar links ot the TemplateSettings table to grab the execution mode and impersonate settings, but is able to present them on the same row.

SELECT [C].Name as Component
    , [C].ID
    , [C].IsDisabled
    , [A].Name as Application
    , [SM].Value as ExecutionMode
    , [SI].Value as ImpersonateForLocalMode
FROM Orion.APM.ComponentTemplate [C]
    --Links to the template settings for Execution Mode
    Left Outer Join Orion.APM.ComponentTemplateSetting [SM] on [C].ID = [SM].ComponentTemplateID
        AND [SM].Key = 'ExecutionMode'
    --Links to the template settings for Execution Mode
    Left OUter Join Orion.APM.ComponentTemplateSetting [SI] on [C].ID = [SI].ComponentTemplateID
        AND [SI].Key = 'ImpersonateForLocalMode'
    Left OUter Join Orion.APM.ApplicationTemplate [A] on [C].ApplicationTemplateID = [A].ApplicationTemplateID
Where [C].ComponentType = 45  --Component Type 45 is Powershell
    And [SM].Value = 'LocalHost'

Since overrides is a concern, I built out the following query, but still sorting through the logic.  So if anyone has input, I would greatly appreciate it.  It appears that component overrides are written to the ComponentSetting table.  So the query below will show if an override has been put in place for either ExecutionMode or ImpersonateForLocalMode.  I haven't dove down the rabbit hole enough to know if these are active overrides or if an override was in place at one point.

Select [C].Name as Component
    , [C].ComponentID
    , [C].Application.Name as Application
    , [C].Application.Template.Name as ApplicationTemplate
    , [C].Application.Node.Caption as Node
    , [SM].Value as ExecutionMode
    , [SI].Value as ImpersonateForLocalMode
From Orion.APM.Component [C]
    --Links to the template settings for Execution Mode
    Left Outer Join Orion.APM.ComponentSetting [SM] on [C].ComponentID = [SM].ComponentID
        And [SM].Key = 'ExecutionMode'
    --Links to the template settings for Execution Mode
    Left Outer Join Orion.APM.ComponentSetting [SI] on [C].ComponentID = [SI].ComponentID
        AND [SI].Key = 'ImpersonateForLocalMode'
Where [C].ComponentType = 45
    --Values only show if they are overriden from default template
    And ([SM].Value is not Null or [SI].Value is not Null)

mesverrum

I have been down this road before with other SAM templates and IIRC there is an enabled column in the table where the over rides live.  So if you over ride a template it creates the relevant settings, but you always have the option to flip back to using the template which disabled the setting but it still exists in the DB.  

Granted this is from memory, so maybe double check in your env.