In this section
Type Conversion and Coercion
You know every data type in KQL security tables and why type mismatches cause silent failures. You have seen that ResultType is a string requiring quoted comparison, and that dynamic columns need tostring() casts before comparison. The full set of conversion functions, how they behave when conversion fails, and the practical patterns for transforming security data between types safely are the foundation for every query you write from this point forward.
Scenario
Priya extracts authentication methods from SigninLogs to build a report on MFA adoption. She writes: SigninLogs | where TimeGenerated > ago(7d) | extend Method = AuthenticationDetails[0].authenticationMethod | summarize count() by Method. The query runs, but every row in the Method column shows a JSON fragment instead of a clean string. The summarize groups cannot aggregate properly because the extracted value is still dynamic type. Priya needs to understand why dynamic field extraction requires explicit type casting, and what happens when the cast fails.
The conversion functions
KQL provides a conversion function for every target type. Each function accepts a single value and returns the converted result, or null if the conversion fails. This null-on-failure behavior is the critical design point: conversions never produce errors. They either succeed and return the converted value, or they fail silently and return null.
The functions you will use most frequently in security queries are tostring() (every dynamic field extraction), todatetime() (parsing string timestamps from audit logs), toint() and tolong() (converting numeric strings for arithmetic), toreal() (extracting coordinates and computing percentages), and parse_json() (converting string-encoded JSON into queryable dynamic objects).
Figure 1.4: Type conversion produces either a valid value or null. Null values are not errors and do not raise warnings. They propagate through the pipeline, silently excluded from every comparison.
Conversion failure is silent
Every to* function returns null when conversion fails. This is the single most important behavior to internalize about type conversion in KQL.
The danger: if you convert a column where some values cannot be converted, those rows silently receive null. Any subsequent where filter on the converted column excludes null rows without warning, because null fails all comparisons. null == 0 is false. null > 10 is false. null != 5 is false. The rows are not deleted. They are silently invisible to every downstream filter.
Consider ResultType in SigninLogs. Most values are numeric strings like "0", "50126", "500121". But some rows contain non-numeric values like "Interrupted" or "CACompliant". If you convert with toint(ResultType), the non-numeric values become null. If you then filter with where ResultCode == 0, the null rows are excluded alongside the non-zero rows. You lose data without any warning.
The null chain extends further than the immediate filter. If you use the converted value in an extend calculation, any row where the conversion produced null generates null in the calculation output as well. extend Ratio = 100.0 * ConvertedResult / TotalCount produces null for any row where ConvertedResult is null, regardless of what TotalCount contains. That null then propagates to any where clause on Ratio, any summarize that averages Ratio, and any project that includes Ratio. One failed conversion at the top of the pipeline creates invisible gaps throughout every downstream operation that touches the converted column.
This is why the tracking pattern below is not optional for production queries. It takes 10 seconds to run and tells you exactly how much data a conversion would silently discard.
Tracking conversion failures
For production queries and detection rules, verify your conversions before relying on them. This pattern shows how many rows a conversion would silently drop:
If the failure rate is above zero, investigate which values failed. Add | where isnull(ConvertedResult) | distinct ResultType to see the non-convertible values. Then decide: handle them separately with case statements, or filter them out intentionally with documented reasoning.
Practical conversion patterns
Dynamic field to string for comparison. This is the conversion you will perform most often. Every dynamic sub-field extraction needs a tostring() cast before the value can be used in string comparison, aggregation, or output:
parse_json for string-encoded JSON. Some columns store JSON as a plain string rather than as a native dynamic type. Dot notation fails on these because the engine sees a string, not a structured object. The fix is parse_json(), which parses the string into a dynamic object that supports dot notation:
todynamic() is an alias for parse_json(). They are interchangeable. The convention in this course is parse_json() because the name explicitly describes what the function does.
You will encounter the string-stored-as-JSON pattern most frequently in custom log tables ingested through the Data Collection API, in the AdditionalDetails column of some Entra ID tables, and in ExtendedProperties columns across several Defender tables. The diagnostic is simple: if a column looks like JSON in the results pane but dot notation returns empty, the column is a string. Parse it first, then extract.
Timestamp conversion for string dates. Audit logs and some custom log sources store timestamps as strings. Convert them with todatetime() before using time comparison operators:
All timestamps in KQL are UTC. If a user reports suspicious activity "at 3pm" London time during British Summer Time (UTC+1), search for 14:00 UTC. Always work in UTC for query logic and convert to local time only in the final project for human-readable output.
The coalesce function for fallback values
coalesce() returns the first non-null value from a list of arguments. It is the primary tool for handling conversion failures and missing data gracefully:
The multi-column pattern is valuable when the same data point appears in different fields depending on the sign-in type. Interactive sign-ins populate IPAddress. Some non-interactive flows populate a different field. Service principal authentications may store the IP in yet another location within the DeviceDetail dynamic object. coalesce() tries each source in order and returns the first non-null value, giving you the best available IP address regardless of which field the source populated.
coalesce() is also the right tool for aggregation safety. When computing averages or percentages across a column that contains null values, the null rows produce null in the calculation. Wrapping the column in coalesce(ColumnName, 0) substitutes zero for null, ensuring the calculation includes every row. Whether substituting zero is correct depends on the semantics. For byte counts and event counts, zero is a reasonable default for missing values. For risk scores, substituting zero would artificially lower the average. The choice of fallback value is an analytical decision, not a syntax decision.
Translating coded values with case
Many security log fields store opaque numeric codes. ResultType in SigninLogs contains values like "50126" (invalid password), "53003" (blocked by Conditional Access), and "500121" (MFA failure). The case function translates codes into human-readable descriptions:
The case function evaluates conditions in order and returns the first match. The final argument without a condition is the default for unmatched values. This pattern transforms opaque error codes into investigation-ready output. An incident report that says "Invalid password failures from IP 198.51.100.44" communicates the attack type instantly. "50126 failures from IP 198.51.100.44" requires the reader to look up the code.
Extracting authentication method chains
The AuthenticationDetails column in SigninLogs is a dynamic array where each element describes one step of the authentication flow. The first element is typically the primary credential (password, certificate, FIDO2 key). The second element, if present, is the MFA step (push notification, SMS, authenticator app code). Extracting these requires array indexing combined with type conversion:
Notice the double conversion: tostring(AuthenticationDetails) serializes the dynamic array to a string, then parse_json() re-parses it into a queryable object. This round-trip is sometimes necessary when the dynamic value has been double-encoded during ingestion. Double-encoding happens when the ingestion pipeline serializes a JSON object into a string before storing it in a dynamic column. The result is a dynamic column that contains a string that contains JSON, rather than a dynamic column that directly contains the JSON object. The symptom is that direct array access like AuthenticationDetails[0] returns the entire array as a single element instead of the first object. The double-conversion pattern resolves this by explicitly serializing and re-parsing.
Not every dynamic column requires the double-conversion. Test with direct access first: AuthenticationDetails[0].authenticationMethod. If that returns a clean value, no double conversion needed. If it returns empty or the entire array, add the tostring() then parse_json() wrapper. The pattern is diagnostic: try the simple path first, fall back to double conversion if it fails.
The output shows which authentication method combinations are in use: password plus push notification, password plus FIDO2 key, certificate plus none (passwordless). This query answers the MFA adoption question from the scenario. In Module 8, you will use this extraction pattern to detect impossible MFA claims where the authentication record shows MFA succeeded but the method field is empty or inconsistent.
IP address comparison without numeric conversion
IP addresses are stored as strings in KQL. You cannot use numeric comparison operators to check whether an IP falls within a range. The ipv4_is_in_range() function handles CIDR matching internally, without requiring you to convert the IP address to a number:
This is one of the few cases where KQL handles the type conversion for you. The ipv4_is_in_range() function accepts string IPs and string CIDR notation and performs the subnet comparison internally. You do not need to convert the IP to a number first. The ipv4_is_in_any_range() variant accepts a dynamic array of CIDR ranges for cleaner syntax when checking against multiple ranges.
Timezone handling in investigations
All timestamps in KQL are stored in UTC. The TimeGenerated column is always UTC, regardless of the user's local timezone. This has two investigation implications that involve type conversion.
First, user-reported times must be converted to UTC before querying. If a London-based user reports suspicious activity "at 3pm" during British Summer Time (UTC+1), search for 14:00 UTC. The datetime() function accepts UTC timestamps: where TimeGenerated between (datetime(2026-05-20T14:00:00Z) .. datetime(2026-05-20T15:00:00Z)).
Second, time-of-day analysis requires explicit offset. To determine whether a user's sign-in activity occurred during business hours in their local timezone, you must add the UTC offset before extracting the hour:
The + 1h adds a timespan to the datetime, shifting it from UTC to local time before hourofday() extracts the hour component. Without the offset, a sign-in at midnight local time appears as 23:00 in the query results, potentially causing you to classify legitimate activity as after-hours anomalous behavior. Always work in UTC for query logic and convert to local time only for the final output.
The analyst converts ResultType with toint() and immediately filters with where ResultCode == 0. The conversion silently turns non-numeric values to null. The null rows are silently excluded from the filter. The analyst does not realize that some sign-in events had non-numeric result types and were dropped. The investigation misses an entire category of events. Always check conversion success rates on real data before using converted values in production filters.
KQL Principle
Type conversion in KQL never errors. It either succeeds or returns null. Null then propagates silently through every subsequent operation, excluding rows from filters, producing NaN in calculations, and creating gaps in aggregations. Treat every conversion as potentially lossy. Verify with isnotnull() before trusting the result. Use coalesce() to provide safe defaults when the conversion may fail for legitimate data values.
Get weekly detection and investigation techniques
KQL queries, detection rules, and investigation methods — the same depth as this course, delivered every Tuesday.
No spam. Unsubscribe anytime. ~2,000 security practitioners.