I have a preliminary search of a web-server-like log that looks like: index=whatever Method=GET | where Response in (200,404) | replace 200 with "Hit", 404 with "Miss" in Response There is also a User field. I want to: Calculate the percentage of misses from the total, e.g., p = misses / (hits + misses), by User. Show the Hits & Misses for only those users where the percentage of misses exceeds some percentage, say 50%. How can I add to the search to get what I want? Thanks. ... View more

Given transit data like: 2019-03-19 19:00:32 GMT vehicle_id="58" stop_direction=Inbound 2019-03-19 19:05:45 GMT vehicle_id="57" stop_direction=Outbound 2019-03-19 19:10:45 GMT vehicle_id="59" stop_direction=Outbound 2019-03-19 19:21:32 GMT vehicle_id="58" stop_direction=Inbound 2019-03-19 19:25:06 GMT vehicle_id="57" stop_direction=Outbound 2019-03-19 19:37:43 GMT vehicle_id="58" stop_direction=Outbound 2019-03-19 19:41:02 GMT vehicle_id="59" stop_direction=Inbound A "trip" is starting at one end of the line and going to the other end. I want to calculate the duration of every trip. You know when a trip ends because the a particular vehicle's stop_direction changes either from "Inbound" to "Outbound" or vice versa. For example, when vehicle 58 changes from "Inbound" (line 4) to "Outbound" (line 6), then it had a "trip" starting from the oldest contiguous "Inbound" (line 1) to the last "Inbound" (line 4) before changing to "Outbound" (line 6). Hence, the duration would conceptually be time_of_event(4) - time_of_event(1). Explained another way: for a given vehicle, if you were to plot its stop direction (where I is "Inbound" and O is "Outbound"), then: IIII OOOOO III OOO IIIIIIII ^^^^ ^^^^^ ^^^ ^^^ ^^^^^^^^ T1 T2 T3 T4 T5 I.e., a run of the same stop direction constitutes a "trip." When the direction changes, it's the next trip. I want to calculate the duration of every trip and chart it such that the X-axis is the trip and the Y-axis is the duration of that trip. ... View more

I currently have a search: ... | eval hour=strftime(_time,"%H") | streamstats time_window=1h dc(vehicle_id) AS dc_vid | timechart max(dc_vid) by hour fixedrange=false This correctly produces the number of distinct vehicles on a particular route by hour. But now assume that there are two different vehicle types: bus and streetcar. So I want to modify the chart to show the same thing, but each bar should be a stacked bar composed of the number of distinct vehicles by vehicle_type by hour. I've tried all manner of fiddling with the search and I can't seem to get it. BTW: the existing search shows each hour as a different colored bar. I don't actually care about that. For the new chart, two colors would be fine (one for each vehicle type in the stacked bar). ... View more

I have a vehicle fleet lookup table like: vehicle_id,vehicle_year,vehicle_type,vehicle_ends,vehicle_agency,vehicle_livery 1,1912,"A",2,"San Francisco Municipal Railway","San Francisco Municipal Railway" 228,1934,"",2,"Blackpool Tramways","Blackpool Tramways" 578,1896,"G",2,"Market Street Railway","Market Street Railway" 737,1952,"PCC",1,"Brussels, Belgium","Zurich, Switzerland" 1807,1928,"Milan",1,"Milan, Italy","Milan, Italy (1928)" A transforms.conf file like: [Historic_fleet] filename = Historic_fleet.csv And a props.conf file like: [NextBus] category = Custom disabled = false LOOKUP-fleet = Historic_fleet vehicle_id This all works fine. However, there are occasionally other vehicles out that are busses. They have their own vehicle_id s that are not in the lookup table. What I want is to have any vehicle_id that is not found to map to a vehicle_type=bus . I'd like to do this automatically so I don't have to do it for every query I write. I'm aware that a lookup table can have a default value, but how do you specify that a default value of, say, bus , is supposed to go into the vehicle_type field? Can I do this with static tables? Or must I resort to using an external lookup Python script? ... View more

I'm time-charting public transit vehicle "layover" time. ("Layover" is how long a driver takes a break upon reaching the end of a line before resuming driving in the opposite direction.) My query and timechart work fine: I'm doing a stacked-bar of Max. Layover over Avg. Layover. However, some max. values are outliers (that could be caused by any number of reasons that aren't relevant here). I'd like to remove the outliers. Every example I've seen of doing this using the outlier command is of the form: ... | eval layover=(end-start)/60 | timechart span=1d eval(round(max(layover),0)) as "Max. Layover" eval(round(avg(layover),0)) as "Avg. Layover" | outlier action=rm The problem with that is that outlier max values are entirely removed from the chart leaving no max value at all for certain bars. That's not what I want. In order to compute the max. layover in the first place, Splunk takes all the layover values, sorts them, then takes the largest value. What I want is to do that, but if the largest value is an outlier, remove only that value and instead use the next-most max. value; then repeat (i.e., if that value is also an outlier, remove that value too; etc.). So I tried this instead: ... | eval layover=(end-start)/60 | outlier action=rm layover | timechart ... That seems to work (I don't see outlier values and no bars have missing values). But is it (a) correct and (b) the optimal way to solve my problem? ... View more

Suppose I have vehicle data of the form: 2016-10-18 17:37:05 GMT vehicle_id="1011" vehicle_distance=185 stop_tag="5239" 2016-10-18 17:39:25 GMT vehicle_id="1009" vehicle_distance=51 stop_tag="4532" 2016-10-18 17:41:37 GMT vehicle_id="1010" vehicle_distance=107 stop_tag="4529" 2016-10-18 17:43:35 GMT vehicle_id="1009" vehicle_distance=104 stop_tag="4532" 2016-10-18 17:45:59 GMT vehicle_id="1011" vehicle_distance=98 stop_tag="5240" I also have a look-up table that give additional information about stops keyed by tag: stop_tag,stop_title,stop_id,stop_direction,stop_lat,stop_lon ... "5239","King St & 4th St","15239","Outbound",37.776270,-122.394170 "5240","King St & 4th St","15240","Inbound",37.776270,-122.394080 ... Each physical stop actually has two stop tags: one for "Inbound" and another for "Outbound," for example, the "King St & 4th St" stop shown. The direction is given by stop_direction . Hence, given a log entry such as the first, at 17:37:05, vehicle 1011 is at stop 5239 --- which is King St & 4th St outbound. Then, nearly 9 minutes later, the same vehicle is at stop 5240 --- the same stop, but inbound. Hence, the vehicle had a "layover" of approximately 9 minutes. For each vehicle, when it changes direction from outbound to inbound (or vice versa) as given by the direction of the stop tag it's at, I want to chart both the average and maximum layover as the Y-axis and either: All vehicles taken together --- where the X-axis would be time. By individual vehicle -- where the X-axis would be the vehicle_id . How can I do this? ... View more