AppLogs Query Language

Using Count at the end of a query will give you the total number of log messages that match the constraints in the query.

logtype="Windows Event Logs" and source CONTAINS "Microsoft-Windows-Security-Auditing" count

The result fetches the number of log messages that match the above conditions.

The search below generates a groupby table, with additional columns for aggregation values.  

For example, when you want aggregate results for the values grouped in a groupby table, this combination can be helpful.

logtype="IIS Access Logs" AVG(timetaken) MIN(timetaken) MAX(timetaken) groupby stemuri

In this query, Min, Max, Avg, and Count of unique stemuri are displayed.

•  Combining timeslice and aggregation together

The search below generates a timeslice table, with additional columns for aggregation values.  

For example, when you want aggregate results on a timely basis, you can use this combination.

logtype="IIS Access Logs" AVG(timetaken) MIN(timetaken) MAX(timetaken) timeslice 1h

In this query, Min, Max, Avg, and Count timesliced by one hour is displayed.

• Combining groupby and timeslice together

The search below generates a timeslice table, with an additional column for groupby values. Going from left to right, the columns read: timeslice, count, and value(s).

For example, when you want your results timesliced, and at the same time grouped based on the given constraint, you can use both groupby and timeslice together in a query.

logtype="SysLogs" groupby application timeslice 1d

In this query, the number of log entries that are timesliced by a day and have the same values for the field "application" are displayed.

• Multiple groupby

The search below generates a table with multiple groupby options.

For example, when you have multiple options for groupby, you can use groupby separated by commas so that you can consolidate and view multilevel groupbys in a single table.

logtype="ServerLogs" groupby sourcefilename,loglevel,threadname

In this query, multiple groupby conditions are grouped together.

• Include

Using Include in a search query fetches results with the specified fields diaplayed.

logtype="SysLogs" | include(application,message)

In this query, the search output will be displayed along with application, message.

• Exclude

Using Exclude in a search query fetches results with the specified fields omitted.

logtype="SysLogs" | exclude(pid)

In this query, the search output will be displayed without the message pid.

• Before

Using this operator you can view the result of the query in the same time, an hour, a day or a week ago.

For example, when you want the result for the given query that was available before some time, use before.

logtype="Apache Access Logs" avg(responsesize) | before 7d

This query will fetch the avg(responsesize) that was at the same time a week ago.

• Timeslice with before

Using this in the query, you can compare the result of the query in the same time, an hour, a day or a week ago, at frequent intervals.

For example, when you want to compare the trend of the status code for the same day last week at a specified frequency, you can use timeslice with before.

logtype="IIS Access Logs" and statuscode>400 timeslice 1h | before 7d

This query will fetch the statuscodes > 400 that was at the same time a week ago, at one hour intervals.

• Count with before

Using this in the query, you can compare the count of the result of the query in the same time, an hour, a day or a week ago.

For example, when you want to compare the number of failure status codes for the same day last week, you can use count with before.

logtype="IIS Access Logs" and statuscode>200 count | before 7d

This query will fetch the failure statuscodes that was at the same time a week ago.

• Having

Using this operator you can apply some condition over the groupby query and get the required output.

logtype="Apache Access Logs" groupby requesturi having avg(responsesize)>10240

This query fetches the output for requesturi which has avg(responsesize)>10240.

• Sort

This sorts the requests based on some fields.

For example, when you want to organize or categorize your results based on some values, you can use sort.

logtype="Apache Access Logs" groupby requesturi sort avg(responsesize)

This query fetches the output for requesturi sorted by the avg(responsesize) value.

• STARTSWITH

Gives the values that start with the given search criteria. This fetches results similar to using LIKE with a * at the end. i.e., searching STARTSWITH "Log" will be similar to searching LIKE "Log*"

logtype="Windows Event Logs" and source STARTSWITH "Micro"

This fetches results for all the sources that start with Micro.

• LIKE

This is case sensitive and allows to use search criteria with asterisk in values.
For example, LIKE "Log*n" will match both Logon and Login.

logtype="Windows Event Logs" and source LIKE "Microsoft*Auditing"

This fetches results for any source with Microsoft Security Auditing, Microsoft Windows Auditing, etc

• in

Similar to using multiple 'or' with '='. All values in a set will be compared.
For example, Status in("200","404","500") will be the same as (status="200" or status="404" or status="500").

logtype="Apache Access Logs" and referer in("Refer 2, "Refer 1")

This fetches results similar to (referer="Refer 2" or referer="Refer 1")

• notin

Similar to using multiple 'and' with '!='. All values in a set will be compared.
For example, status notin("200","404","500") will be the same as (status!="200" and status!="404" and status!="500").

logtype="Apache Access Logs" and referer notin("Refer 2, "Refer 1")

This fetches results similar to (referer!="Refer 2" or referer!="Refer 1")

• histo

Gives histogram for a numerical field. This is similar to groupby, but instead of a common value, a range of values is returned.

logtype="Apache Access Logs" histo responsesize 

This fetches a histogram based on the response size.

Note:

You can also use histogram along with groupby to further partition and analyze the log data.

For example,

logtype="Apache Access Logs" histo responsesize groupby requesturi

This query groups the requesturi by their count and histo categorizes based on their response size.

logtype="Apache Access Logs" groupby requesturi histo responsesize

This query groups the request by histo of response size and groups the url based on their count. 

• range

This is used to fetch a custom range from a histogram. Range should be used only after histo.

For example, status notin("200","404","500") will be the same as (status!="200" and status!="404" and status!="500").

logtype="Apache Access Logs" histo responsesize range(3800 to 4000,4200 to 4400)

This fetches a histogram for the custom range values mentioned.

• range interval

This is used to fetch results with custom time intreval from a histogram. Range interval should be used only after histo.

For example, when a range interval is specified after a histogram field, it fetches the histogram for the custom time interval.

logtype="Apache Access Logs" histo timetaken 1s

This fetches a histogram for the custom time interval of 1s.

• tophits

Use tophits with groupby query to extract the most recent value from the grouped elements. Using the tophits query, you may retrieve important metrics from logs.

logtype="Kafka-Topic-Status" tophits(topicname,offset,lag) groupby topicname

You can get the most current values on various topic statuses in the above query, such as topic offset and lag count.

• percentage

Using percentage, derive general statistical information from logs. You may find the percentage of failed requests with the query below.

logtype="IIS Access Logs" and statuscode>=400 percentage

• percentile and percents

Use percentile and percent to interpret performance data more accurately.

logtype="IIS Access Logs" percentile(TimeTaken) percents(95)

You may find the 95th percentile of a request's processing time with the query above.

Recent searches:

You can reuse the recent search from our Recent Search History instead of typing it every time. You can view them by following the steps below:

1. 1. Click the  icon in the search box.
   2. You can view your recent search queries below the saved searches. You can also save your search queries for future use.
      
      

You can always contact support@site24x7.com if you want to build specific queries for analyzing logs. There's no charge for executing queries or scanning indexed logs.

Related articles:

• Supported log types
• Saving search queries
• Cluster messages based on pattern similarity using Log Templates
• AppLogs alerts

Further reading

Blog: 10 filter patterns that are helpful for managing your logs