topic Re: How to get duration for the product A in 2 occurrences ? in Splunk Search

topic Re: How to get duration for the product A in 2 occurrences ? in Splunk Search https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554003#M157271 <P>streamstats may be your friend</P><LI-CODE lang="markup">| makeresults | eval product="A" | eval status="Start A1 A2 End Start End" | makemv status | mvexpand status | appendcols [| makeresults | eval time="08:00 08:05 08:15 08:20 08:40 09:40" | makemv time | mvexpand time ] | rename COMMENT as "Dummy data ends here" | eval time=strptime(time,"%H:%M") | streamstats reset_on_change=t reset_after="status=\"End\"" min(time) as Start_Time max(time) as End_Time by product | eval duration=round((End_Time-Start_Time)/60) | where status="End" | table product duration *_Time</LI-CODE><P>but you would need to handle the cases where there is no End and also ensure that there is no interleaving of "starts" for the same product before an end - but you would need some other correlation field in that case.</P><P>This example shows products A and B, where before the streamstats you sort by product and time</P><LI-CODE lang="markup">| makeresults | eval product="A" | eval status="Start A1 A2 End Start End" | makemv status | mvexpand status | appendcols [| makeresults | eval time="08:00 08:05 08:15 08:20 08:40 09:40" | makemv time | mvexpand time ] | append [ | makeresults | eval product="B" | eval status="Start B1 B2 End Start End" | makemv status | mvexpand status | appendcols [| makeresults | eval time="08:02 08:07 08:15 08:50 08:55 09:30" | makemv time | mvexpand time ] ] | sort product time | rename COMMENT as "Dummy data ends here" | eval time=strptime(time,"%H:%M") | streamstats reset_on_change=t reset_after="status=\"End\"" min(time) as Start_Time max(time) as End_Time by product | eval duration=round((End_Time-Start_Time)/60) | where status="End" | table product duration *_Time</LI-CODE> Wed, 02 Jun 2021 03:12:13 GMT bowesmana 2021-06-02T03:12:13Z How to get duration for the product A in 2 occurrences ? https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554001#M157269 <P>So what I have now from my search so far</P><P>Product     Status    Time</P><P>A                   Start        8.00 AM</P><P>A                    A1            8.05 AM</P><P>A                    A2            8.15 AM</P><P>A                    End          8.20 AM</P><P>A                   Start         8.40 AM</P><P>A                    End          9.40 AM</P><P>Right now I only get the second start-end duration = 60 minutes only. How can I get it to show the first start-end duration = 20 minutes as well? The "Product" name needs to be the same.</P><P>|makeresults |eval product="A"|eval status="Start A1 A2 End Start End"|makemv status|mvexpand status<BR />|appendcols [|makeresults|eval time="08:00 08:05 08:15 08:20 08:40 09:40"|makemv time|mvexpand time ]<BR />|rename COMMENT as "Dummy data ends here"<BR />|eval time=strptime(time,"%H:%M")<BR />|stats max(eval(if(status=="Start",time,null()))) as Start_Time,max(eval(if(status=="End",time,null()))) as End_Time by product<BR />|eval duration=round((End_Time-Start_Time)/60)</P> Wed, 02 Jun 2021 02:53:13 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554001#M157269 moinyuso96 2021-06-02T02:53:13Z Re: How to get duration for the product A in 2 occurrences ? https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554003#M157271 <P>streamstats may be your friend</P><LI-CODE lang="markup">| makeresults | eval product="A" | eval status="Start A1 A2 End Start End" | makemv status | mvexpand status | appendcols [| makeresults | eval time="08:00 08:05 08:15 08:20 08:40 09:40" | makemv time | mvexpand time ] | rename COMMENT as "Dummy data ends here" | eval time=strptime(time,"%H:%M") | streamstats reset_on_change=t reset_after="status=\"End\"" min(time) as Start_Time max(time) as End_Time by product | eval duration=round((End_Time-Start_Time)/60) | where status="End" | table product duration *_Time</LI-CODE><P>but you would need to handle the cases where there is no End and also ensure that there is no interleaving of "starts" for the same product before an end - but you would need some other correlation field in that case.</P><P>This example shows products A and B, where before the streamstats you sort by product and time</P><LI-CODE lang="markup">| makeresults | eval product="A" | eval status="Start A1 A2 End Start End" | makemv status | mvexpand status | appendcols [| makeresults | eval time="08:00 08:05 08:15 08:20 08:40 09:40" | makemv time | mvexpand time ] | append [ | makeresults | eval product="B" | eval status="Start B1 B2 End Start End" | makemv status | mvexpand status | appendcols [| makeresults | eval time="08:02 08:07 08:15 08:50 08:55 09:30" | makemv time | mvexpand time ] ] | sort product time | rename COMMENT as "Dummy data ends here" | eval time=strptime(time,"%H:%M") | streamstats reset_on_change=t reset_after="status=\"End\"" min(time) as Start_Time max(time) as End_Time by product | eval duration=round((End_Time-Start_Time)/60) | where status="End" | table product duration *_Time</LI-CODE> Wed, 02 Jun 2021 03:12:13 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554003#M157271 bowesmana 2021-06-02T03:12:13Z Re: How to get duration for the product A in 2 occurrences ? https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554004#M157272 <P>Thank you for the clear and thorough explanation. </P> Wed, 02 Jun 2021 03:16:54 GMT https://community.splunk.com/t5/Splunk-Search/How-to-get-duration-for-the-product-A-in-2-occurrences/m-p/554004#M157272 moinyuso96 2021-06-02T03:16:54Z