Moving to Azure Stream Analytics from StreamInsight

Question

I've been using StreamInsight for CEP for several months and am interested in moving to ASA. I have a question that I can't answer from the ASA documentation. In StreamInsight I can alter an event's duration to create signal streams from discrete point in time events. E.g.

var mySignal = from s in myEventStream
                .AlterEventDuration(s => TimeSpan.MaxValue)
                .ClipEventDuration(myEventStream, (s, e) => s.DeviceId == e.DeviceId && s.EventCode == e.EventCode && s.Value != e.Value)
                .ShiftEventTime(e => TimeSpan.FromTicks(1))
                where s.EventCode == "512"
                join e in myEventStream on s.DeviceId equals e.DeviceId
                select new
                    {
                       DeviceId = s.DeviceId,
                       EventCode = s.EventCode,
                       Start = s.Timestamp,
                       End = e.Timestamp,
                       Duration = e.Timestamp - s.Timestamp,
                       Value = s.Value
                    };

What I'm trying to do is detect an overlap between this high/low type signal and another stream of point-in-time events. How can I achieve the same in ASA?

Background: I have devices that send events from multiple sources into an azure event hub. Some of those events are on/off events from contacts. Each contact type is represented by 'EventCode' and the 'Value' field indicates whether the contact is open or closed. What I'm looking for is an ASA query that will tell me when EventCode 512 is in an 'On' state at the same time as EventCode X is also in an 'On' state.

Solution

Below is the modified code from @Andrew which detects a Contact being closed while a Switch is Open. 'inStream' is defined in a WITH statement timestamped by the Timestamp field:

SELECT
    SwitchOpen.DeviceId,
    SwitchOpen.Timestamp,
    '511' AS [EventCode],
    'Switch is Open' AS [EventDescription],
    SwitchOpen.Value,
    SwitchOpen.LocationCode
INTO [outStream]
FROM
    [inStream] AS SwitchOpen
    INNER JOIN [inStream] AS ContactClosed
    ON SwitchOpen.DeviceId = ContactClosed.DeviceId
    AND DateDiff(second, SwitchOpen, ContactClosed) BETWEEN 1 AND 3600
    LEFT JOIN [inStream] AS SwitchClosed
    ON SwitchOpen.DeviceId = SwitchClosed.DeviceId
    AND SwitchClosed.EventCode = SwitchOpen.EventCode
    AND SwitchClosed.Value != SwitchOpen.Value
    AND DateDiff(second, SwitchOpen, SwitchClosed) BETWEEN 1 AND 3600
    AND DateDiff(second, SwitchClosed, ContactClosed) BETWEEN 1 AND 3600
WHERE
SwitchOpen.Value = 0.0
AND SwitchOpen.EventCode = '256'
AND ContactClosed.Value = 1.0
AND ContactClosed.EventCode = '512'
AND SwitchClosed.Timestamp IS NULL

Answer 1

I don't have your data for specifics but this format should do what you're looking for.

SELECT
    DeviceOn.Time AS StartFault,
    Device2On.Time AS Endfault,
    DATEDIFF(second, DeviceOn.Time, Device2On.Time) AS FaultDuraitonSeconds
    DeviceOn.ID ,
    DeviceOn.EventCode,
    DeviceOn.Value
FROM
    Input AS DeviceOn TIMESTAMP by Time
    INNER JOIN Input as Device2On TIMESTAMP by Time on DeviceOn.DeviceId equals Device2On.DeviceId
    ON DATEDIFF(Second, DeviceOn, Device2On) BETWEEN 1 AND 3600
    INNER JOIN Input AS Device2Off TIMESTAMP BY Time
    ON DATEDIFF(second, DeviceOn , Device2Off ) BETWEEN 1 AND 3600
    AND DATEDIFF(second, Device2Off , DeviceOn) BETWEEN 1 AND 3600
    LEFT JOIN Input AS DeviceOff TIMESTAMP BY Time
    ON DATEDIFF(second, DeviceOn , DeviceOff ) BETWEEN 1 AND 3600
    AND DATEDIFF(second, DeviceOff , DeviceOn ) BETWEEN 1 AND 3600
WHERE
DeviceOn.Value = "On"
AND Device2On.Value =Null

Similar to Query example: Detect duration of a condition here https://azure.microsoft.com/en-us/documentation/articles/stream-analytics-stream-analytics-query-patterns/