PENDING Retrieve "All Console Actions" messages with PowerShell

[rhughes]

Hello!

I was wondering if anybody here could help me with something? I am looking to find a way of using PowerShell to retrieve the information that is displayed when you run the "All Console Actions" message query via Configuration Manager and export it to a CSV. I have gotten as far as obtaining the SQL query from SMSProv.log, but this query does not display the data that is presented in the "Description" field when you run the query via Config Manager.

I came across "Get-CMStatusMessages" from a 2015 post via a Google search... but I think this is an old command that no longer exists.

Cheers!
Richard

 

[Garth]

Hello!

I was wondering if anybody here could help me with something? I am looking to find a way of using PowerShell to retrieve the information that is displayed when you run the "All Console Actions" message query via Configuration Manager and export it to a CSV. I have gotten as far as obtaining the SQL query from SMSProv.log, but this query does not display the data that is presented in the "Description" field when you run the query via Config Manager.

I came across "Get-CMStatusMessages" from a 2015 post via a Google search... but I think this is an old command that no longer exists.

Cheers!
Richard

Most SQL queries within the SMSProv.log are not supported to be used for reporting.
IMO If you are going to create a CSV, I would keep within SQL and/or create a report for this, if this is going to be used more than once or in another system.
What do you have for a query now? Which description field are you referring too?

 

[rhughes]

Most SQL queries within the SMSProv.log are not supported to be used for reporting.
IMO If you are going to create a CSV, I would keep within SQL and/or create a report for this, if this is going to be used more than once or in another system.
What do you have for a query now? Which description field are you referring too?

Hi Garth,

Thank you for getting back to me. The SQL query that I have at the moment is

select  all SMS_StatusMessage.Component,SMS_StatusMessage.MachineName,SMS_StatusMessage.MessageID,SMS_StatusMessage.MessageType,SMS_StatusMessage.ModuleName,SMS_StatusMessage.PerClient,SMS_StatusMessage.ProcessID,SMS_StatusMessage.RecordID,SMS_StatusMessage.ReportFunction,SMS_StatusMessage.Severity,SMS_StatusMessage.SiteCode,SMS_StatusMessage.SuccessfulTransaction,SMS_StatusMessage.ThreadID,SMS_StatusMessage.Time,SMS_StatusMessage.TopLevelSiteCode,SMS_StatusMessage.PartOfTransaction,SMS_StatusMessage.Win32Error,SMS_StatMsgInsStrings.InsStrIndex,SMS_StatMsgInsStrings.InsStrValue,SMS_StatMsgInsStrings.RecordID,SMS_StatMsgAttributes.AttributeID,SMS_StatMsgAttributes.AttributeTime,SMS_StatMsgAttributes.AttributeValue,SMS_StatMsgAttributes.RecordID from vStatusMessages AS SMS_StatusMessage LEFT OUTER JOIN vStatusMessageInsStrs AS SMS_StatMsgInsStrings ON SMS_StatMsgInsStrings.RecordID = SMS_StatusMessage.RecordID  LEFT OUTER JOIN vStatusMessageAttributes AS SMS_StatMsgAttributes ON SMS_StatMsgAttributes.RecordID = SMS_StatusMessage.RecordID   where (SMS_StatusMessage.Component = N'Microsoft.ConfigurationManagement.exe' AND SMS_StatusMessage.Time >= '2022/05/19 07:57:54.000') order by SMS_StatusMessage.Time desc

This returns the values in which I get when I turn the "All Console Actions" message query inside the Config Manager console - minus the description column which tells you what actions took place, i.e. user updated the membership of Device Collection A, etc. The above query does not return the information that is found in the Description column but instead, returns the RecordID.

I am looking to automate the export of this information for logging purposes, where it would run on a schedule.

Cheers,
Richard

 

[Garth]

So I have had time to look at this today and I don't see an All Console Actions; Is it within the Monitoring | overview | System Status | Status Messages Queries node? What does the query look like?

 

[Garth]

As an aside this query will show you the same as above but using supported views.

SELECT
    SM.component,
    SM.machinename,
    SM.messageid,
    SM.messagetype,
    SM.modulename,
    SM.perclient,
    SM.processid,
    SM.recordid,
    SM.reportfunction,
    SM.severity,
    SM.sitecode,
    SM.successfultransaction,
    SM.threadid,
    SM.time,
    SM.toplevelsitecode,
    SM.partoftransaction,
    SM.win32error,
    SMIS.insstrindex,
    SMIS.insstrvalue,
    SMIS.recordid,
    SMA.attributeid,
    SMA.attributetime,
    SMA.attributevalue,
    SMA.recordid
FROM
    dbo.v_StatusMessage AS SM
    LEFT OUTER JOIN dbo.v_StatMsgInsStrings AS SMIS ON SMIS.recordid = SM.recordid
    LEFT OUTER JOIN dbo.v_StatMsgAttributes AS SMA ON SMA.recordid = SM.recordid
WHERE 
    ( SM.component = N'Microsoft.ConfigurationManagement.exe'
         AND SM.time >= '2022/05/19 07:57:54.000'
        )
ORDER BY
    SM.time DESC