Operations Management Suite 101: Log Analytics Queries 101


This post is intended to get you started with querying data that is stored within your Operations Management Suite (OMS) / Azure Log Analytics Workspace.  The first section has some basics tips.  The body of this post is a long list of example queries, together with explanation for the syntax and example results.  Finally, there is a list of some of the tables you may wish to query inside OMS / Azure Log Analytics to retrieve certain data, and the OMS solution that collects and ingests data into the particular table.

NOTE: Any comments and feedback greatly appreciated if you found this post helpful or have any corrections / suggestions. Cheers, Antoni

General Getting Started Tips

TIP 1: Operators / functions (in the query language command) like search, summarize, count(), bin() etc ARE case-sensitive so Summarize, Count(), Search, Bin will not work!

TIP 2: Shift + Enter is your friend, as it is a keyboard shortcut that will run the query

TIP 3: The Analytics / Advanced Analytics portal can be used to compile more advanced queries- access by clicking either the 'Analytics' or the 'Advanced Analytics' buttons in the log search page.

TIP 4: SecurityEvents are stored in the SecurityEvents table. Collection is enabled by enabling and configuring the Security and Audit solution

Other Events from other Windows Event logs are stored in the Events table (collection is enabled via the Advanced Settings>Data>Events. See here for more on adding Windows Event data Sources

TIP 5: In addition to the rest of this blog, check out these links to help get started:

Query Explanatory and reference Notes Example Results
1) search * search
All Collected data (will return total number of data entries in OMS.
NOTE: Can then narrow down on type of data, as shown in next query
MICROSOFT DOCS: search

 
2) search * | summarize count() by Type summarize count()
All collected data Summarized by Type of data, providing the count for each type of data
MICROSOFT DOCS: search
MICROSOFT DOCS: summarize

 
3) search "error" Search for Keyword
Search all collected data for the word "error".
NOTE: By default, search is NOT Case Sensitive, so will also fine ERROR and Error
Use the Kind parameter to make case-sensitive:
search kind=case_sensitive "Error"
MICROSOFT DOCS: search


 
4) search in (SecurityEvent) "*Admin*" In
Search in the SecurityEvent Table for the substring "Admin"
NOTE: The SecurityEvents Table contains events from the Windows Security Event Log – requires Security and Audit Solution)
MICROSOFT DOCS: String Operators (including in)

 
5) Event
| where TimeGenerated > ago(1d)
| summarize count()
Event
| where TimeGenerated > now(-1d)
| summarize count()
ago / now
Both of these queries produce the same result – The total count of Events generated within the last day
NOTE: m can be used for minutes, h can be used for hours
MICROSOFT DOCS: ago()
MICROSOFT DOCS: now()
MICROSOFT DOCS: Date Time Timespan Arithmetic
MICROSOFT DOCS: DATE TIME TUTORIAL




 
6) search *
| where TimeGenerated > ago(1d)
| summarize count() by Type
search, ago and summarize
Combination of above
MICROSOFT DOCS: search
MICROSOFT DOCS: ago()
MICROSOFT DOCS: summarize

 
7) Event All Event Data (stored in 'Event' Table
NOTE: This does not include SecurityEvents (see below)
MICROSOFT DOCS - Getting Started With Queries (includes table-based queries and search-based queries)


 
8) SecurityEvent All Security Events
NOTE: Requires Security and Audit solution which collects security events
NOTE: Check out the Computer Security button that shows up when you query on these events
MICROSOFT DOCS - Getting Started With Queries (includes table-based queries and search-based queries)

 

Note the 'Computer Security' panel lights up when you start querying on Security Events!!!

 
9) Perf All collected Performance Data (stored in 'Perf' Table)
NOTE: Collection of performance counters is specified in the Data>Windows Performance Counters / Linux Performance Counters Sections of the Settings
MICROSOFT DOCS - Getting Started With Queries (includes table-based queries and search-based queries)


 
10) Alert All Alert Data (stored in 'Alert' Table)
NOTE: This is both OMS alerts and System Center Operations Manager (SCOM) alerts . OMS alerts have a SourceSystem of OMS. Alerts from OpsManager have a SourceSystem of OpsManager

 
11) Alert
| where TimeGenerated > ago (4h)
Alerts created in the last 4 hours
MICROSOFT DOCS: ago()

12) Event | take 10
 
take
Returns 10 Arbitrary records from the Event Table.
MICROSOFT DOCS: take
 
13) Perf | take 10 take
Returns 10 Arbitrary records from the Perf Table.
MICROSOFT DOCS: take

 
14) Alert | take 10 take
Returns 10 Arbitrary records from the Alert Table.
MICROSOFT DOCS: take

 
15) Event |
where EventID == 6005
Event
Events where the Event ID is 6005
NOTE: This is often used for determining System Reboots as it occurs in the Windows System Event log on System Startup
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators
==
 
16) Event |
where Computer == "win-scomms-01.contoso.com"
 
Event ==
Events where Computer is
"win-scomms-01.contoso.com"
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators

 
17) Event |
where Computer == "win-scomms-01.Contoso.com"
==
NOTE: '==' is case sensitive so this is where this returns zero results
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators

 
18) Event |
where Computer =~ "win-scomms-01.Contoso.com"
=~
Search all Results in the Event table where Computer = "win-scomms-01.Contoso.com" but NOT case sensitive
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators
 

 
19) Event |
where Computer != "win-scomms-01.contoso.com"
| summarize count() by Computer
!=
Event where Computer is NOT "win-scomms-01.contoso.com"
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators
20) Perf |
where ObjectName == "LogicalDisk"
Performance Data where the Object name is Logical Disk
NOTE: remember case-sensitive with == but case insensitive with =~
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators

 
21) Event | sort by TimeGenerated desc sort by … desc
sort by … asc
Events sorted by most recent first
NOTE: Desc can be substituted for asc
NOTE: The time dropdown control on the top left will affect the timeframe of results returned
MICROSOFT DOCS: sort


NOTE: This will affect your query

 
22) Event |
where EventLevelName == "Error" or EventLevelName == "Warning"
or
Events with an Error OR Warning Level
NOTE: == is case-sensitive
NOTE: OR / Or are not accepted – has to be all lower-case
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators
23) Event |
where EventLevelName == "Error" and EventLog == "System"
and
Events that are from the event system log And of an 'error' level
NOTE: == is case-sensitive
NOTE: AND / And are not accepted – has to be all lower-case
MICROSOFT DOCS: where
MICROSOFT DOCS: String Operators
24) Event |
where EventLevelName == "Error" and EventLog == "System"
| project TimeGenerated, Computer, EventID, EventLevelName, EventLog
project
Select Columns
Events that are from the event system log And of an 'error' level. Uses project to narrow results to fields shown

 
25) Event |
where EventLevelName == "Error" and EventLog == "System"
| project Created_Time=TimeGenerated, Computer, EventID, EventLevelName, EventLog
project
Rename Columns
Events that are from the event system log And of an 'error' level. Uses project to narrow results to fields shown and also rename the TimeGenerated field to Created_Time
MICROSOFT DOCS: project

 
26) Event |
where EventLevelName == "Error" and EventLog == "System"
| project Created_Time=TimeGenerated, Computer, EventID, EventLevelName, EventLog, Month_Of_The_Year=getmonth(TimeGenerated)
project
Add a calculated column
Events that are from the event system log And of an 'error' level. Uses project to narrow results to fields shown and also rename the TimeGenerated field to Created_Time and add an extra calculated field which gets the month of the year from the TimeGenerated
MICROSOFT DOCS: project
27) Event |
where EventLevelName == "Error" and EventLog == "System"
| project Created_Time=TimeGenerated, Computer, EventID, EventLevelName, EventLog, Month_Of_The_Year=getmonth(TimeGenerated)
| extend PST_TIME = Created_Time-8h
extend
Add a Calculated Column
Adds to all columns.
Events that are from the event system log and of an 'error' level. Uses project to narrow results to fields shown and also rename the TimeGenerated field to Created_Time and add an extra calculated field which gets the month of the year from the TimeGenerated
NOTE: Have to use the renamed name of the field Created_Time rather than TimeGenerated here.
MS DOCS: extend


28) Event
| where EventID == 7036 | distinct Computer
 
distinct
Every computer that has a 7036 event, showing the computer once
MICROSOFT DOCS: distinct

 
29) Perf
| summarize count() by ObjectName
Perf
All Performance data summarized by Object Name
MICROSOFT DOCS: summarize

 
30) Perf
| summarize count() by CounterName
Perf
All Performance data summarized by Counter Name
MICROSOFT DOCS: summarize


 
31) Perf
| summarize count() by Computer
Perf
All Performance data summarized by Computer
MICROSOFT DOCS: summarize


 
32) Perf
| where TimeGenerated > ago(30m)
Perf
Performance data created in the last 30 minutes
MICROSOFT DOCS: ago()

 
33) Perf
| summarize count() by bin(TimeGenerated,30m), Computer
bin
Performance data summarized based on TimeGenerated rounded to the closest 30 minutes
MICROSOFT DOCS: bin()

 
34) Perf
| summarize Total_Number= count() by bin(TimeGenerated,1d)
| where Total_Number > 400000
Calculated field
Total number is the number of performance samples per day, only showing where the number was over 400000
MICROSOFT DOCS: bin()
MICROSOFT DOCS: Count
MICROSOFT DOCS: summarize

 
35) Perf
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize avg(CounterValue) by Computer, bin(TimeGenerated, 5min)
| render timechart
render timechart
Processor>% Processor Time Data by Computer, rounded to the closest 5 minutes, rendered into a timechart
MICROSOFT DOCS: Tutorial - Dashboards
MICROSOFT DOCS - Tutorial: Charts

 
36) SecurityEvent
| where EventID == 4624
| project Computer, Account, TargetLogonId, LogonTime=TimeGenerated
| join kind= inner (
SecurityEvent
| where EventID == 4634
| project TargetLogonId, LogoffTime=TimeGenerated

) on TargetLogonId

| extend Duration = LogoffTime-LogonTime

| project-away TargetLogonId1

| top 10 by Duration desc

join
All Security Events where logon joined to logoff time, based on user ID, adding a calculated duration (of logged on time) field, reoving the targetlogonid1 field, narrowed to the top 10 based on duration
Reference: MICROSOFT DOCS: Joins

 
37) Event
| summarize makelist(EventID) by Computer
makelist
show all Events in the form of a list, summarized by computer
NOTE: Does not include Security Events are those are stored in the SecurityEvent table – not the Event table
MICROSOFT DOCS: makelist()
 
38) Event
| summarize makeset(EventID) by Computer
makeset
show all Events in the form of a list, summarized by computer
NOTE: Does not include Security Events are those are stored in the SecurityEvent table – not the Event table.
NOTE: Differs from Makelist as shows unique distint IDs in the set, not duplicates as makelist shows.
MICROSOFT DOCS: makeset()
 
39) Event | where Computer == "ComputerName"
| make-series count() on TimeGenerated in range(datetime(01-15-2018),datetime(01-19-2018), 1d)
make-series
Combines results (multiple days in this case) into a single value
MICROSOFT DOCS: make-series


 
40) Event
| evaluate autocluster_v2()
Evaluate autocluster_v2()
Looks for patterns in data specified
Reference:
MICROSOFT DOCS: autocluster

 
41)  Event
| evaluate basket()
evaluate basket (finding frequent patterns)
Reference:
MICROSOFT DOCS: basket

 
42) Event
| evaluate diffpatterns(Computer,"Computer1Name","Computer2Name")
And then to verify….
Event
| where EventID == "7036"
| summarize count() by Computer
evaluate diffpatterns()
Above shows Computer1 (CountA and PercentA) has a lot more 7036 events than Computer2 (CountB and PercentB). We can prove that by running our count:
DIFFPATTERNS (comparing patterns)
MICROSOFT DOCS: diffpatterns


 
43) Event
| where Computer == "ComputerName"
| make-series y = count() on TimeGenerated in range(datetime(01-15-2018),datetime(01-19-2018), 1d)
| extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(y)
| render timechart
 
series_fit_line
find the trend of how many events are created per day
NOTE: run in the advanced analytics portal to see the timechart
--LINEAR REGRESSION (finding trend)
Linear Regression (line of best fit)
MICROSOFT DOCS: Linear Regression

 
44) Event | where Computer == "ComputerName"
| make-series count() on TimeGenerated in range(datetime(01-15-2018),datetime(01-19-2018), 1d) | extend outliers = series_outliers(count_)
 
outlier
Detects anomalies so in the makeseries list that was produced, the first value (0) is an anomaly to the rest of the data (650, 756, 742, 659).
The anomaly of 0 is represented by -7.4. All the other values are represented by 0.x numbers
MICROSOFT DOCS: outlier

 

Here is a list (not finite) of Tables that can be queried, together with the solution that brings that type of data in:

Table / Data Type Name (can search using just this in a query to return all records Solution
ADAssessmentRecommendation AD Health Check (Previously called AD Assessment)
ADReplicationResult AD Replication Status
Alert Alert Management
AlertHistory Alert Management
AzureActivity Activity Log Analytics
ConfigurationChange Change Tracking
ConfigurationData Change Tracking
Event Settings>Data>Windows Event Logs
Heartbeat Agent Health
NetworkMonitoring Network Performance Monitor
Operation OMS Operations – Computer being assessed by a solution pack etc
Perf Settings>Data>Windows Performance Counters
ProtectionStatus Malware Assessment
SecurityAlert Security And Audit (Can be viewed in Azure Security Center)
SecurityBaseline Security And Audit
SecurityBaselineSummary Security And Audit
SecurityDetection  Security And Audit
SecurityEvent Security And Audit
ServiceMapComputer_CL Service Map
ServiceMapProcess_CL Service Map
SQLAssessmentRecommendation SQL Healthcheck (Was previously called SQL Assessment)
Syslog Settings>Data>Syslog
UAApp Upgrade Readiness
UAComputer Upgrade Readiness
UAComputerRank Upgrade Readiness
UADriver Upgrade Readiness
UADriverProblemCodes Upgrade Readiness
UAFeedback Upgrade Readiness
UAIESiteDiscovery Upgrade Readiness
UAOfficeAddIn Upgrade Readiness
UAProposedActionPlan Upgrade Readiness
UASysReqIssue Upgrade Readiness
UAUpgradedComputer Upgrade Readiness
Update Update Management
UpdateSummary Update Management
Usage Builtin Usage area of OMS
WaaSDeploymentStatus Update Compliance
WaaSUpdateStatus Update Compliance
WireData WireData

 


Comments (0)

Skip to main content