All Apps and Add-ons

How do I count rows in a joined search?

LordVoldemort
Explorer

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.

Any thoughts?

0 Karma

LordVoldemort
Explorer

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

LordVoldemort
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...