Find differences in time between events with some shared field values in a subsearch

[brajaram]

We have our logs in JSON structured data. Events contain the following fields Time, ID, Client

I am trying to compare the times between events of different clients that contain the same ID. My query thus far:

index=... Client=A [ search index=... Client=B| table id]

This correctly finds all events with Client=A that occurs in Client=B. However, what I want to do is find the difference of Time between the events of the subsearch and the events of the primary search across ID. (i.e if 10 different IDs have a time of 1, 2, 3 for client A..., and a time of 2, 3, 4... for client B, I want a table that says ID TimeDiff so I can get summary statistics of the difference. I think I need to be using the delta command, but not sure how to set up the data to get that.

[somesoni2 · Accepted solution]

Give this a try (the chart command will create fields with name same as value of field Client, so update the where and eval command accordingly)

index=... Client=A OR Client=B [search index=... Client=B| table ID]
| fields _time ID Client
| chart values(_time) over ID by Client
| where isnotnull('A') AND isnotnull('B')
| eval TimeDiff='B' - 'A'

[brajaram]

That worked perfectly. I never even knew the chart functionality worked like that, but seeing it produce the output makes a lot more sense, but I never would have been able to figure that out at all. Thanks!