how do i calculate duration=date-endtime?

[puneethgowda]

Hi Please help me with this query

index=UAT_Ncache_UserSearchesInfo searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287" | eval date=substr(date,1,16) | fields date,searchid | join type=outer searchid [ SEARCH index=UAT_Ncache_BookingInfo searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287"]

i want calculate duration=date-endtime

[Richfez]

Two answers: one using your search, then another that should end up 173.54 times as fast1. Actually three - one using your search that won't work, one that should, then one that's 173.54 times as fast1.

First, using your search you simply need to add an eval to create the new field after your join.

index=UAT_Ncache_UserSearchesInfo searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287" | eval date=substr(date,1,16) | fields date,searchid | join type=outer searchid [ SEARCH index=UAT_Ncache_BookingInfo searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287"] | eval duration = date-endtime

Now, while I say that it's that simple, it probably isn't because you'll likely need to convert those text-type date/time strings into something easier to add and subtract with and convert it back into something readable.

To do that try the following. Note it got long enough I busted my additional parts into separate lines.

index=UAT_Ncache_UserSearchesInfo searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287" | eval date=substr(date,1,16) | fields date,searchid | join type=outer searchid [ SEARCH index=UAT_Ncache_BookingInfo searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287"] 
| eval d1=strptime(date ,"%Y-%m-%d %H:%M:%S.%3N") 
| eval d2=strptime(endtime,"%Y-%m-%d %H:%M:%S.%3N") 
| eval duration=d1-d2
| eval duration=tostring(duration, "duration")

Now, I think every single time I write these I get my subtraction backwards, so just change duration=d1-d2 to duration=d2-d1 if necessary. The two strptime things convert the date/time strings into epoch times (e.g. seconds) which makes them easy to subtract. The eval duration=d1-d2 subtracts the two to get your duration, then the last statement just reformats the duration to be something other than seconds. You can leave that off it it'll always be very small numbers. Try it and see.

That should then do what you want, try it and let us know!

Now, on to the third way. Join is a wonderful command, but 95% or more of the time it's not needed and is practically always the slowest way to do something. The docs on Join even point this out and give alternates for other possible ways to do this. Given that you seem to be able to group these on searchid, try this using transaction.

index=UAT_Ncache_UserSearchesInfo OR index=UAT_Ncache_BookingInfo | eval date=substr(date,1,16) | transaction maxspan=10m searchid

Now, that's ONLY a sample, it's not complete because "duration" it creates is not the duration you want2. Even with it being incomplete, it should take all the events from either index and group them on searchid, making each group a single event. Note please that you'll need to adjust your maxspan=10m to something reasonable for you. What it does is make transaction way more efficient by telling it how far forward/back in the event timestream it needs to look for the matching searchid to close the transaction and create the group. If you can reasonably say that will always be less than a minute, set it to 2m or something slightly higher. If you try some timeframe that seems reasonable, you can check it by looking at the field "eventcount" I think and confirming there are no oddities there.

To this if you'd like to do more we certainly can. For instance, we can overwrite the default-created duration with your own by adding that logic to the end.

index=UAT_Ncache_UserSearchesInfo OR index=UAT_Ncache_BookingInfo 
| eval date=substr(date,1,16) 
| transaction maxspan=10m searchid
| eval d1=strptime(date ,"%Y-%m-%d %H:%M:%S.%3N") 
| eval d2=strptime(endtime,"%Y-%m-%d %H:%M:%S.%3N") 
| eval duration=d1-d2
| eval duration=tostring(duration, "duration")

So the sky's the limit. I think if you want to do more or have more specific questions on this, first try playing around with it a bit, but if you get stuck I'd say a new question would probably be your best bet!

Oh, one last note - leave them in to check your work and that it's doing the right math and stuff, but you can always remove fields at the very end like ... | fields - d1 d2 or whatever.

Happy Splunking!
-Rich

Caveats/Notes:
1 I made up 173.54, but in larger data sets (e.g. greater than 2 events), the join will get VERY slow and even transaction will be significantly faster. In bigger data sets (thousands or millions of events) the difference could be many orders of magnitude. AND transaction isn't even the best way to do this, but it is "good enough" usually and the way it groups events is very useful when you aren't sure what all you want to do with the resulting group and is easy to understand.

2 It creates a duration based on the _time of the events, using the difference between first and last. By the way, I think the time on your indexer is off by a minute or two. Check it out. 🙂 In addition, I can't see the rest of your events but I'd almost be thinking of making those two date/time "fields" be your _time at ingestion. This deserves another question if you'd like to pursue it, but may - again depending on what's being used as _time already and depending on your data - make many things much, much simpler.

[puneethgowda]

i want calculate date - endtime

date from one index and endtime from another index search is is common

so duration = date-endtime

[Richfez]

puneethgowda,

If one of these answers helped you in your problem, could you please mark one as "Accepted?" This helps people who stumble across this question/answer later know that there was indeed a solution.

Thanks!

-Rich

[kyaparla]

since you have common field(searchid) on both data sources, use transaction command like below.

index=UAT_Ncache* searchid="8e0aa7bf-9346-453b-870d-2639e7c8d287" | transaction searchid

[sundareshr]

You need to convert your date and endtime to epoch format and then format as duration eval duration=tostring(duration, "duration")

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables

[somesoni2]

WHere is the endtime field available? Is it available in the join subsearch? What is the format of date in field date and endtime?