With system Center Configuration manager Build Update 1602 (Current branch ) ,we can now monitor the Configuration manager console usage data .
In previous versions of configmgr ,there is no records in database to see who are the users connecting to SCCM console ,though this information is tracked in SMSProv.log, but no stored information for reporting.
Microsoft have made some improvements with Configmgr current branch build 1602 ,that store the information in database,WMI class about users who try to make successful connection .
After you install the Configmgr console update 1602 and try to connect to Site server (CAS or Primary Site) , if the user is having least permissions( RBA) , SMS provider start tracking the information by executing several queries against the permissions defined in RBA (like OS ,software updates,packages ,applications ,collections and many more ) ,all these information can be monitor in smsprov.log .
This process is same in earlier versions of Configuration manager but the change that was added to Build Update 1602 is ,it creates additional stored procedures,tables,views to store machine Name that is trying to connect to console ,User name ,connected site code , admin console Version (all this info appear in SMSProv.log) and other important information and insert into the database by executing stored procedure (spCMUpsertConsoleUsageData )that Update Console Telemetry table with admin console machine telemetry information.
I have installed Console (CB Configmgr 1602) in PC001 and tried to connect to my Site using APAC\Eswar who has permissions defined using RBA,monitor the log (smsprov.log) for more information:
couple of screenshots from SMSprov.log:
All the information coming from client and connecting to Site server will be tracked in the database.
So,where does the information stored in database and WMI about the console usage ?
In Database ,there is view called : v_CMConsoleUsageData ,which stores information about PC ,User who connect to console ,Connected Site ,Site number,What is OS Build of the Connected PC,Console Version Installed,Client Version installed, Memory,.net framework installed version ,Console connect time and other information.
SQL Query : select * from v_CMConsoleUsageData
After executing the Query ,I have noticed that ,the console connected time is showing in UTC instead of client connected time (Local time) .
This leads me to review the code used in stored procedure and check if any conversion syntax used and Yes ,it is using syntax to convert : (ConsoleConnectTime = GETUTCDATE()) .
So,if you want to have the console connected time but not UTC,you can either change the stored procedure (HIGHLY NOT RECOMENDED) or use the SQL CAST DATE syntax function ,something like below:
select MachineName,UserName,ConsoleConnectTime [ConsoleConnectTime (UTC)],
CAST(GetDate() - GetUtcDate() + ConsoleConnectTime AS datetime) [ConsoleConnectTime]
Once you know,there is view exist that store the information ,you can start working on your customized report by joining other views and create Nice SSRS Report.
For more information about SQL views in Configmgr update 1602,refer this post
Report is okay but can I create collection (user device collection probably to see who are all users connecting to site ) ? if you are interested to play with this .
WMI class (for Collection) called: SMS_ConsoleUsageData (Instance: ROOT\SMS\site_sitecode)
For some reason, I had SMS_ConsoleUsageData class with empty information,though I can generate report for console usage data as you see in above snippet.
Run wbemtest from RUN command, connect to ROOT\SMS\Site_PS1 name space. Click on Query and run the following command to see if it returns any value:
select * from SMS_ConsoleUsageData
So ,How to get this corrected ? am still trying to figure out using the stored procedures ,which might help to update the information into WMI ,but no fix yet for me. Am not sure if this is happening to all or Only for me.
So 2 things are pending (at least for me) for confirmation from this post 1) UTC time for console connected time 2)WMI results empty .
I will update this post when I find something on this UTC and WMI class.