Splunk Search

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

gregorymountfor
Explorer

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
0 Karma
1 Solution

chrisyounger
SplunkTrust
SplunkTrust

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.

View solution in original post

woodcock
Esteemed Legend

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
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

gregorymountfor
Explorer

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.

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

gregorymountfor
Explorer

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

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

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.

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

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
0 Karma

gregorymountfor
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...