I have a query much like the following. The anonymization, if I did it well, should make my intention more clear:
sourcetype=log_car | rename carID as joinID | join usetime=true earlier=true max=1 joinID [search sourcetype=log_seat | rename id as joinID ] | join usetime=true earlier=true max=1 main_id date_year date_month date_mday [search sourcetype=log_seat | stats count as number_seats by mainID, date_year, date_month, date_mday] | stats count AS cars_with_seats, sum(Price) AS total_car_value by mainID, date_year, date_month, date_mday
Every car has at least one seat. The first join is to lookup all the data for the "main" (the special, driver seat designated by the joinID) seat for that car.
This data is aggregated, so I'm displaying the number of cars, and their summed value, broken down by day. That part is fine, where it gets hairy is that I also want to know the total number of seats in each car. There could be thousands (this analogy is not perfect...).
In order to count the number of seats in each car, I added a second join which joins not on the joinID field, but on the mainID and on the day, so the aggregation will work. That returns results, but the number_of_seats is not in the final table.
Therefore, I think my question is, "how do I make the number_of_seats be available in the final table?" and that might have a quick, simple answer.
But if my idea of doing a second join against the same sourcetype is backwards and wrongminded, then my question is as originally posed, how do I count the number of rows in a join? This is complicated by the aggregation going on in the stats call, as I want to see the number of seats in each car, on each day.
Figured it out. It was simple:
The join uses max=1, needlessly. Changing this to maximum 50000 resolved my problem and made number_seats available for each row.
| join usetime=true earlier=true max=1 main_id date_year date_month date_mday [search sourcetype=log_seat | stats count as number_seats by mainID, date_year, date_month, date_mday]
Anyone? Did my swarthy data anonymization render my case impossible to solve?
To summarize, I want to count the rows in the join. If I do the stats count as a separate query, it counts perfectly. I can't seem to make splunk perform the count, and then make the result available as a field for each row in the table. number_seats is always empty, even though the count works.