Filter data returned by Microsoft Operations Management Search
Summary: Ed Wilson talks about filtering data returned by Microsoft Operations Management Search.
Good morning everyone. Ed Wilson here, and today I want to talk about filtering data that is returned by Microsoft Operations Management Search.
Note This post is part of a seven part series about using MS OMS Search. The series includes:
- Easy Microsoft Operations Management Suite Search queries
- Accessing different data types in Microsoft OMS Search
- Filter data returned by Microsoft Operations Management Search
- Filter more data with Microsoft Operations Management Suite Search
- Query event log data with Operations Management Suite Search
- Use Microsoft Operations Management Suite search to track shutdown events
- Using Microsoft Operations Management Suite Search Strings
It is almost always a good idea to include some sort of filter when performing a data query, regardless of the database that contains the data. The exceptions are cases where a quick table scan is more performant than a detailed filter.
Typically, these are cases where there are only a few records in the database. Knowing your data is extremely important. This is especially true when you must be concerned about impact to network resources.
Note For more information about OMS search syntax, see the OMS Search reference on TechNet.
Filtering SecurityEvents by EventID
If I take a basic query where I have a Type=SecurityEvent such as I created in Searching for different types of data with MS OMS, I can look at the expanded view of one of the events. I find that I have an Activity 4625 that says an account failed to log on. It also says the account was \administrator, which can seldom be good. I think I want to dive into this a bit.
The generic query, Type=SecurityEvent, appears at the top of my Search page. I can see the Account that failed to log on, and even the EventID from the Security log. In this case, it appears that a failed account logon is event 4625. This information is shown here:
To filter by EventID 4625, all I need to do is add that following my Type=SecurityEvent query:
Type=SecurityEvent EventID=4625
The query results return and tell me that I have 33,120 results, but that is only during the last 24 hours, which is a default in this type of query. I can look over the grouping pane on the left, and scroll up and down to get a view into my data. For instance, in the last 24 hours, 94 accounts have had failed logons:
I can choose any property from the SecurityEvent that I want to examine. So I am going to add another command: Measure. When I use Measure, I can tell it how I want to measure something. My options are Count, Max, Min, Sum, Avg, and Stddev. In this example, I want to use Count.
The next question is, “What do I want to count?” In this particular example, I want to count individual accounts that are failing to log on. Here is my revised command:
Type=SecurityEvent EventID=4625 | measure count() by Account
When I run the search query, I am greeted by a bar chart that shows the aggregated values of the logon failures:
The cool thing is that even though I am using a custom search query, I can still export the data and open it in an Excel spreadsheet, or do other things with it. So I click the green Export button at the top of the screen, open my spreadsheet, and I am looking at the following:
The exported data is in CSV format, and I could further parse the data with Windows PowerShell if I wanted to. I talk about this technique in Use PowerShell to Work with Data from MS OMS.
That is all I have for you today. Join me tomorrow when I’ll talk about further limiting our #MSOMS search queries.
I invite you to follow me on Twitter and the Microsoft OMS Facebook site. If you want to learn more about Windows PowerShell, visit the Hey, Scripting Guy! Blog. If you have any questions, send email to me at scripter@microsoft.com. I wish you a wonderful day, and I’ll see you tomorrow.
Ed Wilson
Microsoft Operations Management Team