Quick Tip – SCCM SQL Secret

Have you ever wanted some information from the SCCM SQL DB, which you can access in the console but can’t find the data easily in the DB? Helpfully, all the data you access in the console is retrieved from the site server – which keeps a record of the queries.

Open the SCCM Console and access the data in the console, this can be loading a list of devices or even a status message query.

Look at the log file “SMSProv.log” located in %Program Files%\Microsoft Configuration Manager\Logs. You should be able to find the query that the site server ran to get the data.

\\SCCMSITESERVER\drive$\%Program Files%\Microsoft Configuration Manager\Logs\SMSProv.log

Advertisements

Status Messages For an OSD Deployment

Troubleshooting OSD remotely can be difficult if the person initiating the build does not know how to get the logs from a failed machine.

This is a useful status message query to show all messages from the OSD process. It requires a Deployment ID, which is from the deployment of the task sequence you have a problem with.

select
SMS_StatusMessage.*,
SMS_StatMsgInsStrings.*,
SMS_StatMsgAttributes.*,
SMS_StatMsgAttributes.AttributeTime
from SMS_StatusMessage
left join SMS_StatMsgInsStrings
on SMS_StatMsgInsStrings.RecordID = SMS_StatusMessage.RecordID
left join SMS_StatMsgAttributes
on SMS_StatMsgAttributes.RecordID = SMS_StatusMessage.RecordID
where SMS_StatMsgAttributes.AttributeID = 401 and SMS_StatMsgAttributes.AttributeValue = "!!DEPLOYMENTID!!"
and SMS_StatMsgAttributes.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## order by SMS_StatMsgAttributes.AttributeTime DESC

Replace !!DEPLOYMENTID!! with the deployment ID of the TS.