taylor-coppock
Joined 2 years ago
User Profile
User Widgets
Contributions
Getting started with Log analysis - useful queries
We at LogicMonitor want to make taking control of your log data easy for analysis, troubleshooting, and identifying trends. In this post, we will share a few helpful queries to get started with LM Logs - what devices are generating log data and easy ways to track overall usage. In future posts, we’ll share queries to dive deeper into specific log data types. What type of queries do you want to see? Reply to this post with areas of log analysis or best practices you want. Not up to date with LM Logs? Check out this blog post highlighting recent improvements and customer stories: A lookback at LM Logs NOTE: Some assumptions for these queries: Each queries results are bound to the time picker value, adjust according to your needs * is a wildcard value meaning ALL which can be replaced by a Resource, Resource Group, Sub-Group, Device by Type or Meta Data value You may need to modify specific queries to match your LM portal Devices Sending Logs- use this query to easily see which LM monitored devices are currently ingesting log data into your portal * | count by _resource.name | sort by _count desc Total Number of Devices Sending Logs-the previousquery showed which devices are generated logs, while this query identifies the overall number of devices * | count by _resource.name | count Total Volume by Resource Name -this query shows the total volume of log ingestion (as GB) by resource name, with the average, min, max size per message. The results are sorted by GB descending but you can modify the operators to identify your own trends. * | count(_size), sum(_size), max(_size), min(_size) by _resource.name | num(_sum/1000000000) as GB | num(_sum/_count) as avg_size | sort by GB desc Total Log Usage -This is a helpful query to run to see your overall log usage for the entire portal * | sum(_size) | num(_sum/1000000000) as GB | sort by GB desc And finally,Daily Usage in Buckets -run this query to see an aggregated view of your daily log usage * | beta:bucket(span=24h) | sum (_size) as size_in_bytes by_bucket | num(size_in_bytes/1000000000) as GB | sort by _bucket asc We hope these help you get started!86Views11likes0CommentsRe: Have an idea for Community content? Post your ideas here!
We’d like to publish this helpful list of queries of Log customers Table of Content Assumptions 2 Volume/Usage Based Queries 3 Anomaly Queries 6 Windows Queries 7 Login / Sudo / Privileged Command Queries 10 IIS + SQL Queries 12 Network Device Queries 13 Deviceless Log Queries 13 Assumptions Each queries results are bound to the time picker value, adjust according to your needs * is a wildcard value meaning ALL which can be replaced by a Resource, Resource Group, Sub-Group, Device by Type or Meta Data value Volume/Usage Based Queries Devices Sending Logs * | count by _resource.name | sort by _count desc Total Number of Devices Sending Logs * | count by _resource.name | count Total Volume by Resource Name as GB and average, min, max size per message * | count(_size), sum(_size), max(_size), min(_size) by _resource.name | num(_sum/1000000000) as GB | num(_sum/_count) as avg_size | sort by GB desc Total Usage in GB * | sum(_size) | num(_sum/1000000000) as GB | sort by GB desc Daily Usage in Buckets * | beta:bucket(span=24h) | sum (_size) as size_in_bytes by_bucket | num(size_in_bytes/1000000000) as GB | sort by _bucket asc Anomaly Queries Bad Anomalies (remove anomaly.type for error conditions only) _anomaly.type="never_before_seen" and (error or fail or critical or fatal or terminate or kill) Good Anomalies (remove anomaly.type for only good conditions) _anomaly.type="never_before_seen" and (success or complete or finish) Windows Queries Windows Events by Level, Channel and EventID _resource.group.name="Windows Servers" | count by Level, Channel, EventID | sort by _count desc Windows Security Account Name, Domain and Security ID _resource.group.name="Windows Servers" AND "Logon ID" | parse /Security ID:\t{1,}(.*[^\r\n])/ as security_id | parse /Account Name:\t{1,}(.*[^\r\n])/ as account_name | parse /Account Domain:\t{1,}(.*[^\r\n])/ as account_domain | parse /Logon ID:\t{1,}(.*[^\r\n])/ as logon_id | count by security_id, account_name, logon_id, account_domain | sort by _count desc Windows Log Volume by Channel in GB (Channel="System" OR Channel="Application" OR Channel="Security") AND _resource.group.name="Windows Servers" | count(_size), sum(_size), max(_size), min(_size) by Channel | num(_sum/1000000000) as GB | num(_sum/_count) as avg_size | sort by GB desc Number of failed Windows Logins _resource.group.name="Windows Servers" and "failed to logon" or "login fail" | count(_size) by _resource.name |sort by _count desc Successful Logins _resource.group.name="Windows Servers" AND "Login succeeded" | parse /Login succeeded for user (.*[A-Za-z0-9-_].*')/ as username | count by username | sort by _count desc Login / Sudo / Privileged Command Queries Sudo Commands run "sudo" AND "COMMAND" |parse /sudo: (\S+ ):/ as sudouser | parse /sudo\[[0-9]+\]: (\S+)/ as sudouser | parse /COMMAND=(\S+.*$)/ as sudocommand | count by sudouser, sudocommand | sort by _count desc Privileged Commands By User Name and Exclusion of servicenow "sudo" AND "COMMAND" AND NOT servicenow |parse /<\d+>(\w+\s+\d+ \d+:\d+:\d+)/ as Time_of_Execution |parse /sudo: (\S+ ):/ as Primary_User | parse /sudo\[[0-9]+\]: (\S+)/ as Primary_User | parse /USER=(\S+ )/ as Privileged_Account | parse /COMMAND=(\S+.*$)/ as Privileged_Command_Run | count by Time_of_Execution,Primary_User,Privileged_Command_Run, Privileged_Account, _resource.name IIS + SQL Queries SQL Login Failures /(?i)login failed/ | parse /for user '(.*?)'/ as user | parse /CLIENT: (.*?)]/ as ip | count by user, ip | sort by _count desc | limit 10 Cluster Join Failures "Cluster" AND "fail" AND "denied" | parse /.*Cluster Network name:(.*\S+)/ as cluster | parse /.*DNS Zone: (\S+)/ as zone | count by zone, cluster Network Device Queries Network Device - Duplex Mismatch "duplex" or "mismatch" | count by _resource.name | sort by _count desc Deviceless Log Queries Deviceless Logs (_resource.id=”0”) With a Hostname as the First Value After Timestamp _resource.id="0" | parse /[0-9]{2}:[0-9]{2}:[0-9]{2} (\S+)/ as unmap_host | count by unmap_host | sort by _count desc0Views1like0Comments
Groups
Inner Circle
Welcome to the Customer Showcase, sponsored by “The Inner Circle “. Feel free to peruse this Customer Story Showcase to read more about what our customers are doing out in the world and how LogicMonitor is supporting their journeys.11 Posts
