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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In January, the Splunk Threat Research Team had one release of new security content via the Splunk ES Content ...

Expert Tips from Splunk Professional Services, Ensuring Compliance, and More New ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Observability Release Update: AI Assistant, AppD + Observability Cloud Integrations & ...

This month’s releases across the Splunk Observability portfolio deliver earlier detection and faster ...