Kevin Holman's System Center Blog

Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of UseAre you interested in having a dedicated engineer that will be your Mic

Using OpsMgr to see which servers have not been logged on to via RDP

Using OpsMgr to see which servers have not been logged on to via RDP

  • Comments 7
  • Likes

This came up in a discussion group.... and while it maybe not be all that interesting of a topic.... it is interesting to see the kinds of reports you can write with OpsMgr and look for unique scenarios.

 

The question was - "How can I run a report - and see all my servers that have NOT been logged onto via RDP in the last 30 days?"

 

First - we need to find out what events are logged, so we can capture which servers ARE being logged on to via RDP.  If you have an audit policy that logs successfull logons, we will log an event 528 in the security event log, and the "LogonType" for a RDP logon is "10". 

Here is a link which describes these logon types and what they mean:  http://www.windowsecurity.com/articles/Logon-Types.html

 

So - we want to collect event 528.... but ONLY those events that are RDP... or logon type 10.... so we need to know which Event Parameter that logontype corresponds to, so we can write a rule which will ONLY collect these events.  You can use the logparser tool I mentioned here:  http://blogs.technet.com/kevinholman/archive/2008/04/22/using-event-description-as-criteria-for-a-rule.aspx

As an alternative - if you don't want to use LogParser for some crazy reason... you can create an alert generating event rule for all event 528's, and then in the alert description, simply paste the following - to show you what each parameter in an event equals:

Parameter 1:  $Data/Params/Param[1]$ <BR />

Parameter 2:  $Data/Params/Param[2]$ <BR />

Parameter 3:  $Data/Params/Param[3]$ <BR />

Parameter 4:  $Data/Params/Param[4]$ <BR />

Parameter 5:  $Data/Params/Param[5]$ <BR />

Parameter 6:  $Data/Params/Param[6]$ <BR />

 

Ok.... so now.... we need to write our event collection rule.  It should look something like this:

 

image

 

At this point.... we can run a simple generic "Event Analysis" Report and look for all event 528's in the Data Warehouse in a given time period.  Any servers that DONT show up.... haven't logged this event.

 

image

 

 

However, what we REALLY want is something that just shows me the list of all machines in the data warehouse that HAVE NOT generated this event.... for that, we can use a custom query:

 

select distinct elc.computername from Event.vEvent ev
inner join vEventLoggingComputer elc on elc.eventloggingcomputerrowid = ev.loggingcomputerrowid
where NOT eventdisplaynumber = '528'
order by elc.computername

 

Just paste this into a simple visual studio report, add some time/data parameters, and there you have it.  In fact - lets walk through that.  You can use the copy if Visual Studio that gets installed with the SQL 2005 client tools.

1.  Start up visual studio.

2.  File, New Project.  Choose the Report Server Project Wizard:

image

 

3.  Click Next to start the Report Wizard.

4.  Create a new Data Source (we will swithc our report to use an existing one later)

image

 

5.  Click Edit, and put in the server\instance, and then choose the data warehouse database.  Choose Test Connection to make sure it's all good:

 

  image

 

6.  Click OK, then Next.  Paste in your query that gives you the output you want... from the query I listed above:

 

image

 

7.  Click Next, and Finish on Report type.

8.  Type a name for the report, such as "Inactive RDP Servers"  Check the box to Preview the report.

 

image

 

9.  Click Finish.  The preview comes up.  This is a pretty inefficient query, so it might take some time to run in a big datawarehouse.  We will need to adjust some of the column widths using a drag and drop function on the layout tab.... the initial formatting is bad.  Click the Layout tab, and you can drag the column headers to make the report look good:

 

image

 

10.  Now my report looks good.... but I need to add some date/time parameters....  Click the "Data" tab now.  From the top menu, choose Report, Report Parameters.  Click Add.  Add a StartDate and EndDate paramter, and choose DateTime as the data type, exactly as shown:

 

image

 

11.  Choose OK, and now when you preview your report, you will have some nice date pickers.

12.  From the top menu, choose build, build report project.  Then browse to your user profile, and find the RDL file we created under \Documents\VisualStudio.

13.  You can take this RDL and import it into your reporting website.  http://servername/Reports  You should create a custom folder there for custom reports.  They will automatically show up in the SCOM console.

14.  Once you import this file, we need to select it in the reporting website, and update the Data Source. 

 

image

 

15.  Browse the shared data sources, and select the Data Warehouse Main.  Click OK, and make sure you click APPLY on the next screen.

16.  Now run your report.  Pick a start and end time, and run it - here is mine:

 

image

 

 

You can repeat the above process using this wizard for any query that you want to turn into a report.

Comments
  • Kevin, great post!  I'm looking forward to creating this report tomorrow.  Something else that'd be useful is a report on which servers have users still logged on via RDP.  Sys Admins (including myself) have the tendency of taking up RDP connections on servers, inadvertently preventing others from logging on via RDP.  :)

  • Hi Kevin,

    Thank you for this great post. I am going to test this on our environment today.

    Santosh

  • Hi Kevin, wouldn't it be better to use the ACS feature for collcting security logs?

    If you have ACS installed you are effectively already collecting those events in a database that has a better schema for them... no need to collect them in the operations DB like you would have done in MOM2000/2005...

  • Hi Kevin,

    I followed the step by step option you have provided here but I am getting all servers with agents on my result.

    Am I missing something? Looks like it is not filtering the option. Thanks.

  • You'd need to run the SQL query manually - if it returns all servers, then all servers have NOT collected that event.

    So:

    1.  Either your audit policy isnt configured (see if you actually create an event 528 on RDP login into a security log)

    2.  Your event collection rule is misconfigured

    3.  You havent waited long enough.

    Simply run a SQL query against the event table... look look FOR the 528 event... or create a view in My Workspace to show these events.  If you dont have any - there is the first part of your answer.

  • Hi Kevin,

    1. Audit policy is configured right. I can see the event on the security log.

    2. I have a schedule report to run every with this collection rule for information about RDP session to server and I can see different users logging into different servers so I think the collection rule is configured correctly.

    3. It's been about a month since I configured this.

    4. I even created a view under my workspace using this collection rule and I can see 528 with logon type 10 on that view.

    Any help would be appreciated. Thank you.

  • So…. with the introduction of Server 2008 into OpsMgr… as a monitored agent, you might need to re-evaluate

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
Search Blogs