Splunk Search

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

baklimek
New Member

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
0 Karma

woodcock
Esteemed Legend

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
0 Karma

woodcock
Esteemed Legend

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

Happy Splunking!
0 Karma

baklimek
New Member

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

0 Karma

knielsen
Contributor

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

See: | makeresults | eval foo="1;2;3" | makemv delim=";" foo | mvexpand foo | map search="makeresults | eval bar=$foo$"
- you'll only have bar in the end, not the field foo defined before the 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.

0 Karma

baklimek
New Member

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

0 Karma

baklimek
New Member

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.

0 Karma

knielsen
Contributor

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$...

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...