topic Re: Can you help me use start and end times in one search in a mapped subsearch? in Splunk Search

Can you help me use start and end times in one search in a mapped subsearch?

baklimek — Fri, 22 Feb 2019 21:31:35 GMT

I'm trying to connect the sum of measurements from a certain process and connect them to workorders by the times the orders are in place.

However, when I attempt to map the data using $StartTime$ and $EndTime$, the statistics table disappears and will not even show the fields from my first search. What am I doing wrong? The Start and EndTimes are in epoch time for the first part of the search.

My search:

index="all_usf_hardsurfaces_orderhistory" TargetOrg="XX" MachineName="YXD12" (Status="IP") 
| stats values(_time) as StartTime by WorkOrderNumber, MaterialName, _time, Quantity
| eval Qty=round(Quantity,0)
| fields  StartTime EndTime WorkOrderNumber MaterialName Qty
| sort by -StartTime
| delta _time as DeltaStart
| eval DeltaStart=abs(DeltaStart)
| eval EndTime=_time+DeltaStart
| fields Time EndTime WorkOrderNumber MaterialName Qty
|map search="search index=pltxx_da ProcessName="Defecting" ItemName="Current Length Output (No Waste)" earliest=$StartTime$ latest=$EndTime$  
|dedup Measurement consecutive=true |stats sum(Measurement) as Measurment |eval Measurement=Measurement/304.8"
|table StartTime EndTime WorkOrderNumber MaterialName Measurement

Re: Can you help me use start and end times in one search in a mapped subsearch?

renjith_nair — Sat, 23 Feb 2019 05:27:00 GMT

@baklimek,
Two observations from your search.
Line : 2 - values(_time) as StartTime could be a multivalue field since you are using values. To make sure there is only one value, try eval StartTime =mvindex(StartTime ,0) after the stats

Line : 9 , You have a field Time but not StartTime . Probably it's a typo but worth to check

Re: Can you help me use start and end times in one search in a mapped subsearch?

baklimek — Mon, 25 Feb 2019 13:24:29 GMT

It was a typo, but the query still doesn't work with your line 2 suggestion. Any thoughts?

Re: Can you help me use start and end times in one search in a mapped subsearch?

knielsen — Mon, 25 Feb 2019 13:51:42 GMT

For one thing, I am pretty sure StartTime EndTime WorkOrderNumber MaterialName are empty after your mapped search.

The other thing, playing with it and data that does not correlate to your specific use case at all, I noticed I had an empty EndTime in my first row of the input table to the map command, preventing it to run at all. After I added where EndTime>StartTime before the map command, I at least got a table with Measurement calculated.

Hth,
-Kai.

Re: Can you help me use start and end times in one search in a mapped subsearch?

baklimek — Mon, 25 Feb 2019 17:37:36 GMT

I'm a tad unfamiliar to the makeresults command (I'm newer to Splunk). Would the entire string be placed at the beginning? And how exactly would this help the values in StartTime, EndtTime, WorkOrderNumber, and MaterialName carry over?

I appreciate your help!!

Re: Can you help me use start and end times in one search in a mapped subsearch?

baklimek — Mon, 25 Feb 2019 19:04:15 GMT

I haven't added in the makeresults yet, but I have gotten the search to work. You are correct in the fact that the fields from the base search do not show up, only the searched field from the mapped search.

Re: Can you help me use start and end times in one search in a mapped subsearch?

knielsen — Tue, 26 Feb 2019 14:09:19 GMT

Oh, don't bother with the makeresults example. That should just be a oneliner to show how map works: It doesn't add events or statistics to your existing data, it's like a new search where you provided the input parameters.

You will often answers here that work with makeresults to provide a cut & paste example that works everywhere, just for conveniance. Since we all don't share a common data set, we often generate artificial data for a search, and makeresults comes in handy to execute the search.

If you need the fields that were available before the map command, I guess you could just add them inside the map like eval Measurement=Measurement/304.8, StartTime=$StartTime$, EndTime=$EndTime$, WorkOrderNumber=$WorkOrderNumber$...

Re: Can you help me use start and end times in one search in a mapped subsearch?

woodcock — Wed, 06 Mar 2019 04:06:01 GMT

Your main mistake was not escaping the double-quotes inside of your map string. You also may not have a full understanding of what map does and how it works based on how you are handling the fields. I have made many assumptions to guess what you are trying to do. Try this:

index="all_usf_hardsurfaces_orderhistory" TargetOrg="XX" MachineName="YXD12" (Status="IP") 
| stats values(_time) as StartTime by WorkOrderNumber, MaterialName, _time, Quantity 
| eval Qty=round(Quantity,0) 
| fields StartTime EndTime WorkOrderNumber MaterialName Qty 
| sort by -StartTime 
| delta _time as DeltaStart 
| eval DeltaStart=abs(DeltaStart) 
| eval EndTime=_time+DeltaStart 
| fields StartTime EndTime WorkOrderNumber MaterialName Qty 
| map search="search index=pltxx_da ProcessName=\"Defecting\" ItemName=\"Current Length Output (No Waste)\" earliest=$StartTime$ latest=$EndTime$  
   |dedup Measurement consecutive=true
   | stats sum(Measurement) as Measurment
   | eval Measurement=Measurement/304.8, StartTime=$StartTime$, EndTime=$EndTime$, WorkOrderNumber=$WorkOrderNumber$, MaterialName=$MaterialName$, Qty=$Qty$" 
| table StartTime EndTime WorkOrderNumber MaterialName Measurement Qty

Re: Can you help me use start and end times in one search in a mapped subsearch?

woodcock — Wed, 06 Mar 2019 04:07:12 GMT

Also, both StartTime and EndTime must be time_t (AKA epoch, a number).