How do you Calculate _time difference between subsearch and main search?

[gregorymountfor]

I'm trying to calculate the _time difference between the subsearch and main search; but if I try and pass the time through to the main search, it seems to want to include it in the actual search and return me no results.

So, I have two systems, one to publish and one to ingest. For each publish in the last 4 hours, I want to look back in time and find when its ingestion was to see how long it spent in the system. The linkage between the two searches is the "UID"; but in the subsearch, I have to rename it from uid to UID

My search as follows gives me all the ingest events for the last 4hours' worth of successfully published events

index=mca_mmr sourcetype=ixs-9 Method=Ingest [search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd | rename uid as UID | fields UID]

I would expect something similar to this to work, although I don't know how to pass pub_time through to the main search:

index=mca_mmr sourcetype=ixs-9 Method=Ingest [search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd | rename uid as UID | eval pub_time=_time | fields UID] | eval process_time=pub_time-_time

[woodcock]

Like this:

index=mca_mmr sourcetype=ixs-9 Method=Ingest AND
[ search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd
| rename uid as UID | eval pub_time=_time 
| table UID pub_time 
| outputlookup pubtimes.csv
| table UID ]
| lookup pubtimes.csv UID
| eval process_time = pub_time - _time

[gregorymountfor]

this gives me errors "Streamed search execute failed because: Error in 'lookup' command: Lookups: The lookup table 'pubtimes.csv' does not exist or is not available. "
I've not used a lookup before so am not sure how to debug this further just yet.

[woodcock]

I had a silly typo (one place had pubtimes.csv and the other had pRubtimes.csv. I edited it and fixed it. Try it again.

[gregorymountfor]

yup, I noticed & corrected that.
looking at the outputlookup documentation, I'm suspecting that maybe I don't have appropriate permissions to use the command. I'll check that with our admin later today about that.

[chrisyounger · Accepted solution]

Try this:

index=mca_mmr sourcetype=ixs-9 Method=Ingest 
| join uid 
    [ search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd 
    | eval pub_time=_time 
    | fields uid pub_time] 
| eval process_time = pub_time-_time

However a faster solution is this one:

index=mca_mmr sourcetype=ixs-9 Method=Ingest 
| append 
    [ search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd 
    | eval pub_time=_time 
    | fields uid pub_time] 
| stats latest(*) as * by uid 
| eval process_time = pub_time-_time

Hope this helps you.

[gregorymountfor]

I can't understand why the first version (with join) doesn't work - it just returns 0 results.
I tried renaming uid in the inner & outer search to match but no joy.

The second version (with append) returns the expected amount of events, but the output table is just uid & pub_time ; not actually the process_time .
I tried adding | table uid process_time which just leaves an empty process_time column, so I'm not sure that the eval is actually working

[gregorymountfor]

I just tried the join version again and it worked - I must have forgotten to pass the renamed UID out.

So this was successful:

index=mca_mmr sourcetype=ixs-9 Method=Ingest
| join UID
[ search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd
| eval pub_time=_time | rename uid as UID
| fields UID pub_time]
| eval process_time = pub_time-_time | table UID process_time

[chrisyounger]

Good to hear. Although it be better to get it working with append|stats method too. Anyways I am sure you have other things to prioritise.

[chrisyounger]

try this:

index=mca_mmr sourcetype=ixs-9 Method=Ingest | eval start_time = _time
 | append 
     [ search index=mca_mmr earliest=-4h sourcetype=jbpm process=publishEnd 
     | eval pub_time=_time 
     | fields uid pub_time] 
 | stats latest(*) as * by uid 
 | eval process_time = pub_time-start_time

[gregorymountfor]

still no joy: for some reason the start_time is not getting populated.

I put a | table uid pub_time start_time after this expression and I can see that the start_time is just nonexistent.

I moved the eval start_time = _time to just after the subsearch (before the stats latest(*) ) and now for some reason its value is exactly the same as the pub_time value