topic Re: How to get aggregate information from lookup table and return to outer dbxquery? in Splunk Search

How to get aggregate information from lookup table and return to outer dbxquery?

grantmeng — Mon, 06 Jun 2022 19:47:03 GMT

Hi, I have a table as the main search using dbxquery below:

| dbxquery connection=my_connection query="SELECT id, start_date, end_date FROM my_table"

Sample records:
id, start_date, end_date
1, 2020-01-01, 2020-01-04
2, 2020-01-03, 2020-01-05
......

And I have another lookup csv with only two columns below:

date, amount
2020-01-01, 10
2020-01-02, 20
2020-01-03, 10
2020-01-04, 10
2020-01-05, 20
......

The output I want is:

id, start_date, end_date, total
1, 2020-01-01, 2020-01-04, 50 # total sum of 2020-01-01 to 2020-01-04 (10+20+10+10)
2, 2020-01-03, 2020-01-05, 40 # total sum of 2020-01-03 to 2020-01-05 (10+10+20)

What could be the best way to get this done? Thanks in advance!

Re: How to get aggregate information from lookup table and return to outer dbxquery?

ITWhisperer — Tue, 07 Jun 2022 05:05:07 GMT

Re: How to get aggregate information from lookup table and return to outer dbxquery?

grantmeng — Tue, 07 Jun 2022 14:24:58 GMT

Thanks a lot! Below is my solution, but there are some minor differences of the total. not sure which part might be wrong.

| dbxquery connection=my_connection query="SELECT * FROM my_table"
| join max=0
[| inputlookup lookup.csv | fields *]
| where date >= start_date and date <= end_date
| stats sum(amount) as total by id, start_date, end_date

Re: How to get aggregate information from lookup table and return to outer dbxquery?

ITWhisperer — Tue, 07 Jun 2022 15:10:06 GMT

What field is your join joining on? In your original question, you did not have any common fields between the fields returned by the dbxquery and the lookup file.

Assuming you have got a common field, the join will join one event from the lookup file so you would be missing any data from the lookup file which did not have a corresponding value in the dbxquery.

Unless I am missing something, I don't see how your query satisfies your requirement.

Re: How to get aggregate information from lookup table and return to outer dbxquery?

grantmeng — Tue, 07 Jun 2022 15:40:03 GMT

There is no common field between these two queries. The first one (dbxquery) has start_date/end_date, which are used to check the date range only in the second query (lookup.csv) with its date field.

My solution is try to get the cartesian product first (max=0 for this purpose), for each row in the first query, it should return something below:

id, start_date, end_date, date, amount
1, 2020-01-01, 2020-01-04, 2020-01-01, 10
1, 2020-01-01, 2020-01-04, 2020-01-02, 20
1, 2020-01-01, 2020-01-04, 2020-01-03, 10
1, 2020-01-01, 2020-01-04, 2020-01-04, 10
1, 2020-01-01, 2020-01-04, 2020-01-05, 20
......

Then use the date comparison check: date >= start_date AND date <= stop_date to filter out those unmatched rows in the lookup.csv, after that we get below for the first row in the first query:

1, 2020-01-01, 2020-01-04, 2020-01-01, 10
1, 2020-01-01, 2020-01-04, 2020-01-02, 20
1, 2020-01-01, 2020-01-04, 2020-01-03, 10
1, 2020-01-01, 2020-01-04, 2020-01-04, 10

Aggregate the above rows by sum, we get:

1, 2020-01-01, 2020-01-04, 50

Do you see anything not right? Thanks again!

Re: How to get aggregate information from lookup table and return to outer dbxquery?

ITWhisperer — Tue, 07 Jun 2022 16:08:19 GMT

As I said before, join needs a common field between the two data sources, you don't appear to have one, or at least, not the way you have described your data and your search.

Re: How to get aggregate information from lookup table and return to outer dbxquery?

grantmeng — Tue, 07 Jun 2022 16:40:42 GMT

I searched and saw someone mentioned Splunk could do cartesian product in this way. I'll do more research, thanks for your solution again.