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:

• List of OMS Solutions including Overview (Licensing / Communication Notes) - Add Solutions
• Query Best Practices - Log Analytics Query Best Practices
• Playgrounds' which have lots of demo data to go out and try your queries out on: Log Analytics Demo and Playground And Application Insights Demo and Playground
• More language refence here: https://docs.loganalytics.io/docs/Language-Reference
• Getting Started with Queries: https://docs-analytics-eus.azurewebsites.net/learn/tutorials/getting_started_with_queries.html
• SQL to Log analytics cheat sheet: https://docs-analytics-eus.azurewebsites.net/learn/cheatsheets/sql_to_azure_log_analytics.html
• Useful Operations Management Suite / Azure Log Analytics Links and Resources page: https://aka.ms/omslinks

	Query	Explanatory and reference Notes	Example Results
1)	search *	searchAll Collected data (will return total number of data entries in OMS.NOTE: Can then narrow down on type of data, as shown in next queryMICROSOFT 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 dataMICROSOFT DOCS: search MICROSOFT DOCS: summarize
3)	search "error"	Search for KeywordSearch all collected data for the word "error".NOTE: By default, search is NOT Case Sensitive, so will also fine ERROR and ErrorUse the Kind parameter to make case-sensitive:search kind=case_sensitive "Error"MICROSOFT DOCS: search
4)	search in (SecurityEvent) "*Admin*"	InSearch 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 / nowBoth of these queries produce the same result – The total count of Events generated within the last dayNOTE: m can be used for minutes, h can be used for hoursMICROSOFT 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 summarizeCombination of aboveMICROSOFT DOCS: search MICROSOFT DOCS: ago() MICROSOFT DOCS: summarize
7)	Event	All Event Data (stored in 'Event' TableNOTE: 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 EventsNOTE: Requires Security and Audit solution which collects security eventsNOTE: Check out the Computer Security button that shows up when you query on these eventsMICROSOFT 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 SettingsMICROSOFT 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 hoursMICROSOFT DOCS: ago()
12)	Event | take 10	takeReturns 10 Arbitrary records from the Event Table.MICROSOFT DOCS: take
13)	Perf | take 10	takeReturns 10 Arbitrary records from the Perf Table.MICROSOFT DOCS: take
14)	Alert | take 10	takeReturns 10 Arbitrary records from the Alert Table.MICROSOFT DOCS: take
15)	Event |where EventID == 6005	EventEvents where the Event ID is 6005NOTE: This is often used for determining System Reboots as it occurs in the Windows System Event log on System StartupMICROSOFT 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 resultsMICROSOFT 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 sensitiveMICROSOFT 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 DiskNOTE: remember case-sensitive with == but case insensitive with =~MICROSOFT DOCS: where MICROSOFT DOCS: String Operators
21)	Event | sort by TimeGenerated desc	sort by … descsort by … ascEvents sorted by most recent firstNOTE: Desc can be substituted for ascNOTE: The time dropdown control on the top left will affect the timeframe of results returnedMICROSOFT DOCS: sort	NOTE: This will affect your query
22)	Event |where EventLevelName == "Error" or EventLevelName == "Warning"	orEvents with an Error OR Warning LevelNOTE: == is case-sensitiveNOTE: OR / Or are not accepted – has to be all lower-caseMICROSOFT DOCS: where MICROSOFT DOCS: String Operators
23)	Event |where EventLevelName == "Error" and EventLog == "System"	andEvents that are from the event system log And of an 'error' levelNOTE: == is case-sensitiveNOTE: AND / And are not accepted – has to be all lower-caseMICROSOFT DOCS: where MICROSOFT DOCS: String Operators
24)	Event |where EventLevelName == "Error" and EventLog == "System"| project TimeGenerated, Computer, EventID, EventLevelName, EventLog	projectSelect ColumnsEvents 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	projectRename ColumnsEvents 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_TimeMICROSOFT DOCS: project
26)	Event |where EventLevelName == "Error" and EventLog == "System"| project Created_Time=TimeGenerated, Computer, EventID, EventLevelName, EventLog, Month_Of_The_Year=getmonth(TimeGenerated)	projectAdd a calculated columnEvents 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 TimeGeneratedMICROSOFT 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	extendAdd a Calculated ColumnAdds 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 TimeGeneratedNOTE: 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	distinctEvery computer that has a 7036 event, showing the computer onceMICROSOFT DOCS: distinct
29)	Perf| summarize count() by ObjectName	PerfAll Performance data summarized by Object NameMICROSOFT DOCS: summarize
30)	Perf| summarize count() by CounterName	PerfAll Performance data summarized by Counter NameMICROSOFT DOCS: summarize
31)	Perf| summarize count() by Computer	PerfAll Performance data summarized by ComputerMICROSOFT DOCS: summarize
32)	Perf| where TimeGenerated > ago(30m)	PerfPerformance data created in the last 30 minutesMICROSOFT DOCS: ago()
33)	Perf| summarize count() by bin(TimeGenerated,30m) , Computer	binPerformance data summarized based on TimeGenerated rounded to the closest 30 minutesMICROSOFT DOCS: bin()
34)	Perf| summarize Total_Number= count() by bin(TimeGenerated,1d)| where Total_Number > 400000	Calculated fieldTotal number is the number of performance samples per day, only showing where the number was over 400000MICROSOFT 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 timechartProcessor>% Processor Time Data by Computer, rounded to the closest 5 minutes, rendered into a timechartMICROSOFT 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	joinAll 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 durationReference: MICROSOFT DOCS: Joins
37)	Event| summarize makelist(EventID) by Computer	makelistshow all Events in the form of a list, summarized by computerNOTE: Does not include Security Events are those are stored in the SecurityEvent table – not the Event tableMICROSOFT DOCS: makelist()
38)	Event| summarize makeset(EventID) by Computer	makesetshow all Events in the form of a list, summarized by computerNOTE: 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-seriesCombines results (multiple days in this case) into a single valueMICROSOFT DOCS: make-series
40)	Event| evaluate autocluster_v2()	Evaluate autocluster_v2() Looks for patterns in data specifiedReference: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_linefind the trend of how many events are created per dayNOTE: 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_)	outlierDetects 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 numbersMICROSOFT 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: