Splunk Search

How to get duration for the product A in 2 occurrences ?

moinyuso96
Path Finder

So what I have now from my search so far

Product     Status    Time

A                   Start        8.00 AM

A                    A1            8.05 AM

A                    A2            8.15 AM

A                    End          8.20 AM

A                   Start         8.40 AM

A                    End          9.40 AM

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.

|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")
|stats max(eval(if(status=="Start",time,null()))) as Start_Time,max(eval(if(status=="End",time,null()))) as End_Time by product
|eval duration=round((End_Time-Start_Time)/60)

Labels (3)
0 Karma
1 Solution

bowesmana
Champion

streamstats may be your friend

| 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

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.

This example shows products A and B, where before the streamstats you sort by product and time

| 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

View solution in original post

moinyuso96
Path Finder

Thank you for the clear and thorough explanation. 

0 Karma

bowesmana
Champion

streamstats may be your friend

| 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

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.

This example shows products A and B, where before the streamstats you sort by product and time

| 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

View solution in original post

Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.