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.
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
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
Thanks very much arjunpkishore5. This has worked well, just as needed! Good to know this usage.
One cannot correlate events that have nothing in common.