Count based on status on latest association
One way to accomplish this is with a window function and grouping applied using the GROUP BY clause and an aggregate function with a case statement like so:
WITH CTE_Log_Sorted AS -- Creates a CTE (can do this in a subquery too) to generate unique row numbers for each status within a partition of parentProcess starting with the latest status first (ordered by created descending).
(
    SELECT status, ROW_NUMBER() OVER (PARTITION BY parentProcess ORDER BY created DESC) AS SortId
    FROM Log
)

SELECT
    SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) AS PENDING, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END) AS FAILED, -- Case statements to sum up only the status we want to count in this column
    SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS -- Case statements to sum up only the status we want to count in this column
FROM CTE_Log_Sorted
WHERE SortId = 1 -- Filter out everything but the latest status of each parentProcess
GROUP BY status -- Group on each status so we can sum up the count of each in our SELECT clause