Certainly.
Step 1. Reformulate your two base searches as a single search with a "disjunction" ie a big "OR".
(index=myidx sourcetype=myapp_log status=startSecurityUpgrade) OR (search index=idms_sat sourcetype=myapp_log status=sendCode)
Next we use a technique broadly called "conditional eval", to paint fields called Start_Time and Enrolled_Time on the appropriate events.
(and I'm actually... throwing away your strftime because it's not helping here. _time is epochtime-valued, and a) you need to compare it with the > operator so that's a good thing, and b) the timechart command wants it as an epochtime value, so turning it into a string with strftime isnt going to help either of those things)
So base search plus "conditional eval" now looks like this:
(index=myidx sourcetype=myapp_log status=startSecurityUpgrade) OR (search index=idms_sat sourcetype=myapp_log status=sendCode)
| eval Start_Time=if (status="startSecurityUpgrade",_time,null())
| eval Enrolled_Time = if (status="sendCode",_time,null())
Now we're ready to use stats to do the "joining"
(index=myidx sourcetype=myapp_log status=startSecurityUpgrade) OR (search index=idms_sat sourcetype=myapp_log status=sendCode)
| eval Start_Time=if (status="startSecurityUpgrade",_time,null())
| eval Enrolled_Time = if (status="sendCode",_time,null())
| stats values(Start_Time) as Start_Time values(Enrolled_Time) as Enrolled_Time by host_ip
I'm using values here just... so you can get the lay of the land. If there's only one of each per value of host_ip, all the better. If there are sometimes more than one, you'll have to think about which one you want to use... EIther way I advise starting from values() and thinking carefully about the one-to-many possibilities.
Last but not least, apply your filtering and do your timechart. Oh and let's say you picked min(Start_Time) and max(Enrolled_Time) respectively, once you pondered the values() situation.
(index=myidx sourcetype=myapp_log status=startSecurityUpgrade) OR (search index=idms_sat sourcetype=myapp_log status=sendCode)
| eval Start_Time=if (status="startSecurityUpgrade",_time,null())
| eval Enrolled_Time = if (status="sendCode",_time,null())
| stats min(Start_Time) as Start_Time max(Enrolled_Time) as Enrolled_Time by host_ip
| where Enrolled_Time > Start_Time
| timechart span=10m count
One more note: you had timechart count(host_ip) , which is a little strange. count(host_ip) will count all the rows that have any non-null value of host_ip, and since every row coming out of stats, or for that matter coming out of your join, will have a non-null value of host_ip..... it's the exact same result as doing timechart count . Perhaps your intention was to do dc(host_ip) (ie the distinct-count of host_ip) but.... again in this case that would also be the same as doing count , since each row coming out of this stats command (or out of your join command), has a host_ip value that is guaranteed unique. Long story short... you want just timechart count here.
This is vastly preferable to using join. Not only will you avoid the row-limits and the execution-time limits, (and avoid a terrible deathspiral of increasing maxresultrows in limits.conf), you will do more of the processing out at your indexers, and you'll correspondingly send far less data back over the wire to your search heads. Oh and it'll run much faster and use less resources overall.
... View more
