Compartilhar via

Operations Management Suite 101: Log Analytics Queries 101

  • Artigo

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 * 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:

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