Compare time fields from different sourcetypes

[junchao]

Hi,

I have two searches that output different things.

Search.1 is a DB query that returns the latest DB record modify time - the timefield in concern is the LATESTMODTS_Epoch.

| dbxquery shortnames=1 connection=Oracle-DB query="SELECT MAX(modify_ts) as LATESTMODTS FROM Database1" | eval LATESTMODTS_Epoch = strptime(LATESTMODTS, "%Y-%m-%d %H:%M:%S") | fields LATESTMODTS_Epoch

Search.2 is a normal search that returns a timefield from another sourcetype - the fimefield in concern is FailureTime_Epoch.

index=xxx sourcetype=abc | eval FailureTime_Epoch = strptime(FailureTime, "%Y-%m-%d %H:%M:%S") | stats count by users, FailureTime_Epoch

Now i have LATESTMODTS_Epoch from Search.1 and FailureTime_Epoch from Search.2, both in epoch time format which i want to compare these two time and produce another search with results, something like below.

index=xxx sourcetype=abc | stats count by users | where FailureTime_Epoch > LATESTMODTS_Epoch

My question is how would i correlate the two fields though to compare their value?

Search.1 and Search.2 are both indenpendent searches with no common shared fields or values.

I'd really appreacite if anyone would kindly give me a hint or advice.

Thanks very much.

[arjunpkishore5 · Accepted solution]

Here is how you can do this. Since you don't have a common field between the 2 datasets, you need to create a common field to join them. I've done this by adding eval joiner=1 to both queries

index=xxx sourcetype=abc 
| eval FailureTime_Epoch = strptime(FailureTime, "%Y-%m-%d %H:%M:%S") 
| stats count by users, FailureTime_Epoch 
| eval joiner=1 
| join joiner 
    [| dbxquery shortnames=1 connection=Oracle-DB query="SELECT MAX(modify_ts) as LATESTMODTS FROM Database1" 
    | eval LATESTMODTS_Epoch = strptime(LATESTMODTS, "%Y-%m-%d %H:%M:%S") 
    | eval joiner=1 
    | fields joiner, LATESTMODTS_Epoch] 
| where FailureTime_Epoch > LATESTMODTS_Epoch

Please mark as answer if this works for you.

Cheers

[junchao]

Thanks very much arjunpkishore5. This has worked well, just as needed! Good to know this usage.

[richgalloway]

One cannot correlate events that have nothing in common.