Common Splunk queries

All 500 errors from a connector:

sourcetype="*jira*" AND Properties.APICallDetails.ResponseStatusCode=50*

Connector per hour throughput by endpoint:

MessageTemplate="APICall"
| eval Properties.APIRequestUrl=case('Properties.Customer' like "XYZ%", urldecode('Properties.APIRequestUrl'), 1=1, 'Properties.APIRequestUrl')
| rex field=Properties.APIRequestUrl "(?=/rest/api/latest//rest|/rest)(?:/rest/api/latest/)?(?<requesturl>.*)"
| rex field=requesturl "(?:/rest/(api|agile|greenhopper|auth)/([\d\.]*|latest)/)(?<endpoint>[^?/]*)"
| search endpoint=*
| timechart limit=0 span=1h count(eval('Properties.APIRequestMethod'="POST")) as POST 
count(eval('Properties.APIRequestMethod'="GET")) as GET 
count(eval('Properties.APIRequestMethod'="PUT")) as PUT 
count(eval('Properties.APIRequestMethod'="DELETE")) as DELETE by endpoint

An anomaly detection macro can be set up in a generalized manner to be used for detecting spikes for any error.

Anomaly Detection Macro Definition:
Outliers_Stdev(groupby (optional), errorcount_twindow, rollingavg_window, multiplier, minimum_errors)

Example Search:

"SocketException" 
`Outliers_Stdev(Properties.ServiceName, "span=1h", "window=72", 3, 50)`

Anomaly Detection Macro Example Setup (set up 2 macros, one with 4 arguments and one with 5)

Outliers_Stdev(4) 
Outliers_Stdev(errorcount_twindow, rollingavg_window, multiplier, minimum_errors) 

earliest=-14d latest=now 
| timechart limit=0 $errorcount_twindow$ count as errorCount
| streamstats current=t global=f $rollingavg_window$ avg(errorCount) as average stdev(errorCount) as standarddeviation
| eval multiplier=$multiplier$
| eval upperBound = average + (multiplier * standarddeviation)
| eval isOutlier=if(errorCount > upperBound AND errorCount >= $minimum_errors$ AND standarddeviation!=0 , 1 ,0)
| sort -_time

Outliers_Stdev(5)
Outliers_Stdev(groupby, errorcount_twindow, rollingavg_window, multiplier, minimum_errors)

earliest=-14d latest=now 
| timechart limit=0 $errorcount_twindow$ count as errorCount by $groupby$
| fillnull errorCount
| untable _time, $groupby$, errorCount
| search NOT $groupby$="errorCount"
| streamstats current=t global=f $rollingavg_window$ avg(errorCount) as average stdev(errorCount) as standarddeviation by $groupby$
| eval multiplier=$multiplier$
| eval upperBound = average + (multiplier * standarddeviation)
| eval isOutlier=if(errorCount > upperBound AND errorCount >= $minimum_errors$ AND standarddeviation!=0 , 1 ,0)
| sort -_time

Example Using the Macro:
Login Fails Spike: Remote Jira

Properties.APIRequestUrl="/wss/proxyac?ENV=AgileCraft&TARGET=/%2Frest%2Fauth%2Flatest%2Fsession" NOT Properties.APIResponseStatusCode="OK"
`Outliers_Stdev(Properties.Customer,"span=1h", "window=72", 3, 10)`
| where _time > relative_time(now(), "-3h") AND isOutlier=1

Notable Exceptions

SqlExceptions

"System.Data.SqlClient.SqlException"
earliest=-4w latest=now
| bucket _time span=1h
| stats count as exceptioncount by _time
| makecontinuous 
| fillnull
| streamstats time_window=2w avg(exceptioncount) as avg_exceptions stdev(exceptioncount) as stdev_exceptions
| sort -_time
| where exceptioncount > 50 AND stdev_exceptions!=0
| eval multiplier = 3
| eval upperBound=(avg_exceptions + stdev_exceptions * multiplier) 
| eval isOutlier=if(exceptioncount > upperBound, 1, 0)
| table _time, isOutlier, exceptioncount
| where _time > relative_time(now(), "-1h") AND isOutlier>0

Connector SocketExceptions

"System.Net.Sockets.SocketException" 
`Outliers_Stdev(Properties.ServiceName, "span=1h", "window=72", 3, 50)`
| where _time > relative_time(now(), "-1h") AND isOutlier=1

Java.Lang Exception (No Class Def Found error)

Exception="java.lang.NoClassDefFoundError:*" earliest=-14d latest=now 
| stats earliest(_time) as _time count as count by host Exception
| table _time host count Exception
| where  _time > relative_time(now(), "-1h")

Other Notable Errors

Captcha Fails

CAPTCHA_CHALLENGE AND Properties.Customer="*" AND "Properties.APICallDetails.ResponseHeader_X-Authentication-Denied-Reason"="*"
| eval Customer='Properties.Customer'
| stats count as total_fails 
latest(_time) as latest_time
values("Properties.APICallDetails.ResponseHeader_X-Authentication-Denied-Reason") as login_urls 
distinct_count(Properties.ThreadId) as thread_count  
by Customer, Properties.MachineName, Properties.ConnectorId
| eval latest_fail_time=strftime(latest_time,"%Y/%m/%d %H:%M:%S")
| table Customer, latest_fail_time, login_urls, total_fails,  thread_count

Invalid Connection String Attribute

source="*AgileCraft-ASP-*" AND (error="Invalid connection string attribute" OR error="*TCP*") AND log_type="exception"
| stats count list(url) by db_source 
| sort-count

Database doesn't exist or can't query database

MessageTemplate="Database doesn't exist or can't query database" 
| rex field=Exception "(?:System.Data.SqlClient.SqlException:?\s(\(0x80131904\))?:?\s?)(?<sqlerror>.+?)(?=\r)"
| rex field=Exception "(?<location>[A-Z]:\\\\[.\S\D]*)(?=ClientConnection)"
| eval ServerDB='Properties.ServerDB', Time=strftime(_time,"%a %x %I:%M:%S.%3N %p %Z")
| stats latest(Time) as last_occurrence earliest(Time) as first_occurrence count as total_count by ServerDB, Properties.MachineName, sqlerror,location
| table ServerDB, Properties.MachineName, total_count, first_occurrence, last_occurrence, sqlerror, location

Create Failures to Create Jira Issue from feature

"CreateFeatureInJira" AND MessageTemplate="CreateFeatureInJira:Failed*"
| stats dc(MessageTemplate) as count_ values(MessageTemplate) as failure_list by Properties.Customer
| search count_>=10

How to detect a statistically significantly increase in connector sync times (lagging sync) 

When dealing with large amounts of data, one way to deal with query performance issues is to calculate and cache historical results in a scheduled report. You can then query against the calculated values by referencing the saved scheduled search in another alert/report.

For example, the following scheduled job, titled “Customer Timer Sync Times”,  runs once on a weekly cadence to calculate a running average of historical sync times for multiple different sync engines per each customer connector (excluding weekdays and non-business hours):

RenderedMessage=Complete* AND Properties.EventTimeInMilliseconds>=0 AND date_hour>=6 AND date_hour<=18 AND date_wday!="sunday" AND date_wday!="saturday" 
| bucket _time span=7d
| stats avg(Properties.EventTimeInMilliseconds) as prior_avg stdev(Properties.EventTimeInMilliseconds) as prior_stdev by _time, Properties.Customer, Properties.Event 

This job is then used as an input in the “Customer Sync Times Lag” alert, which runs every hour:

| loadjob savedsearch=“somebody@atlassian.com:search:Customer Timer Sync Times"
| eventstats avg(prior_avg) as mean stdev(prior_avg) as stdev by Properties.Customer, Properties.Event
| eval upperBound=mean + 3*stdev
| eval JoinField='Properties.Customer'.'Properties.Event'
| join JoinField 
    [ search source="*jira*" AND RenderedMessage=Complete* AND Properties.EventTimeInMilliseconds>=0 earliest=-1w@w latest=now
    | stats avg(Properties.EventTimeInMilliseconds) as curr_avg by Properties.Customer, Properties.Event 
    | eval JoinField='Properties.Customer'.'Properties.Event'
    | fields JoinField, curr_avg]
| eval isOutlier=if(curr_avg > upperBound, 1, 0)
, "prior avg dur HH:MM:SS"=tostring(floor(mean/1000),"duration") 
, "curr avg dur HH:MM:SS"=tostring(floor(curr_avg/1000),"duration") 
, "mean diff dur HH:MM:SS"=tostring(floor((curr_avg-mean)/1000),"duration")
, mean_diff_in_seconds=(curr_avg-mean)/1000
| search mean_diff_in_seconds > 3600 AND isOutlier=1
| table Properties.Customer, Properties.Event, "prior avg dur HH:MM:SS", "curr avg dur HH:MM:SS", "mean diff dur HH:MM:SS", isOutlier, mean_diff_in_seconds
| sort-mean_diff_in_seconds

Correlation property data

Some of the data will include a correlation ID property. This property’s value will be unique to the individual event/action/process/request that ran and generated the log data. This correlation property can be used to find all the related log data for that event/action/process/request.

For web site log data, this correlation data can be used to find all log data that was part of an individual request. This web site log data has a property named Properties.HttpRequestId, which is generated and managed by IIS, for the .Net log data and named correlationid for the ASP log data.

For the Jira connector Windows Service, this data can be used to find all the log data that was part of an individual execution of a sync process. This Jira log data has a property named Properties.CorrelationId and there are 4 sync processes, 1. Issue sync, 2. Board sync, 3. Continuous Issue Push sync, and 4. UI task sync. Each time one of these sync processes is executed, there will be a unique value generated and logged so all data with the same correlation value will be related to that specific execution of the sync process.

Jira Connector Windows Service - Jira REST API request calls

Each time Jira Align’s Jira Connector makes a web request to the Jira REST API, it will log data with MessageTemlate=APICall. If the data has a Level=Information then the response is expected to be a successful response and any other value for Level is not a successful response. Since the request path values are dynamic, the log data includes Properties.APIResource data that can be used to group that data in order to identify all the calls to a Jira REST API end point.

One of the intended uses of this APIResource data is to determine various metrics for the call volume and response time for Jira RESET API calls. Here are a few use cases for this data in splunk queries.

Jira REST API resource average response time by APIResource and ServiceName:

MessageTemplate=APICall | stats AVG(Properties.APIResponseTimeMilliseconds) as Avg_ResponseTimeMilliseconds by Properties.APIResource, Properties.ServiceName | sort Avg_ResponseTimeMilliseconds desc

Jira API resource counts:

*| spath MessageTemplate | search MessageTemplate=APICall | stats count by Level, Properties.APIResource | sort Properties.APIResource, Level