Query for alert actions that have a credential assigned

I am trying to get a SQL or SWQL query to retrieve alert actions that have a credential assigned to it and what that credential is.

I have not be successful so far, does any have this type of query already?

Find more posts tagged with

Alert

alert actions

SWQL

credentials

Accepted answers

Lofstrand

Hi, how about below query:

SELECT
    A.Title
    ,A.ActionTypeID
    ,C.Name
FROM Orion.Actions AS A
LEFT OUTER JOIN Orion.Credential AS C ON A.Properties.PropertyValue=C.ID AND A.Properties.PropertyName='Credentials'
WHERE
    A.Properties.PropertyName='Credentials'
    AND A.Properties.PropertyValue IS NOT NULL
    AND A.Properties.PropertyValue!=''

Lofstrand

@jjroncag , here is an updated query.

SELECT
    A.Title AS [Action Title]
    ,A.ActionTypeID AS [Action Type]
    ,C.Name as [Credential name]
    ,CASE  
        WHEN AP.PropertyValue=2 THEN 'Basic'
        WHEN AP.PropertyValue=3 THEN 'NTLM'
        WHEN AP.PropertyValue=4 THEN 'Token'
        ELSE '-'
    END AS [HTTPAuthType]
FROM Orion.Actions AS A
LEFT OUTER JOIN Orion.Credential AS C ON A.Properties.PropertyValue=C.ID AND (A.Properties.PropertyName='Credentials' OR A.Properties.PropertyName='HttpRequestCredId')
LEFT OUTER JOIN Orion.ActionsProperties AS AP ON A.ActionID=AP.ActionID AND AP.PropertyName='HttpRequestAuthType'
WHERE
    (A.Properties.PropertyName='Credentials' OR A.Properties.PropertyName='HttpRequestCredId')
    AND A.Properties.PropertyValue IS NOT NULL
    AND A.Properties.PropertyValue!=''

All comments

Lofstrand

Hi, how about below query:

SELECT
    A.Title
    ,A.ActionTypeID
    ,C.Name
FROM Orion.Actions AS A
LEFT OUTER JOIN Orion.Credential AS C ON A.Properties.PropertyValue=C.ID AND A.Properties.PropertyName='Credentials'
WHERE
    A.Properties.PropertyName='Credentials'
    AND A.Properties.PropertyValue IS NOT NULL
    AND A.Properties.PropertyValue!=''

jjroncag

@Lofstrand We also have some SendHttpRequest alert action types. Their credentials are saved differently in the alert actions and I don't see the field with the credential value. Any suggestions for these action types?

Lofstrand

@jjroncag , here is an updated query.

SELECT
    A.Title AS [Action Title]
    ,A.ActionTypeID AS [Action Type]
    ,C.Name as [Credential name]
    ,CASE  
        WHEN AP.PropertyValue=2 THEN 'Basic'
        WHEN AP.PropertyValue=3 THEN 'NTLM'
        WHEN AP.PropertyValue=4 THEN 'Token'
        ELSE '-'
    END AS [HTTPAuthType]
FROM Orion.Actions AS A
LEFT OUTER JOIN Orion.Credential AS C ON A.Properties.PropertyValue=C.ID AND (A.Properties.PropertyName='Credentials' OR A.Properties.PropertyName='HttpRequestCredId')
LEFT OUTER JOIN Orion.ActionsProperties AS AP ON A.ActionID=AP.ActionID AND AP.PropertyName='HttpRequestAuthType'
WHERE
    (A.Properties.PropertyName='Credentials' OR A.Properties.PropertyName='HttpRequestCredId')
    AND A.Properties.PropertyValue IS NOT NULL
    AND A.Properties.PropertyValue!=''