topic Two searches using three indexes with different fields in Splunk Search

Two searches using three indexes with different fields

nsantiago17 — Mon, 24 Jun 2019 17:33:04 GMT

I have this search below:

 index=BI_1 sourcetype=jobs_info fieldJ IN (Flamengo)
| search index=BI_2 sourcetype=tel_drv
| dedup _raw
| eval indextime = strftime(_indextime, "%Y-%m-%d")
| stats sum(TNeg) as TNeg_drv by indextime
| appendcols [search index=BI_3 sourcetype=tel_eqt | dedup _raw | eval indextime = strftime(_indextime, "%Y-%m-%d") | stats sum(TNeg) as TNeg_eqt by indextime]

And need to sum the fields "TNeg_eqt" "TNeg_drv" then show in the table with the indextime but I'm having difficulties.

Re: Two searches using three indexes with different fields

jnudell_2 — Wed, 30 Sep 2020 01:02:59 GMT

Hi @nsantiago17 ,

Some sample data would be helpful, along with how you would like to the report/table to look. Some questions:
What is TNeg_eqt? What is TNeg_drv? What is the unique value for these events (transaction ID, line number, serial number, etc)? Why are you using dedup?

I would use something like this:



( index=BI_1 sourcetype=jobs_info fieldJ IN (Flamengo) ) OR 

( index=BI_2 sourcetype=tel_drv )

| eventstats sum(TNeg_eqt) as TNeg_eqtSum sum(TNeg_drv) as TNeg_drvSum

This search will show the sum of each value as an additional field. If you just want the total sum and no other information, you could use stats:



( index=BI_1 sourcetype=jobs_info fieldJ IN (Flamengo) ) OR 

( index=BI_2 sourcetype=tel_drv )

| stats sum(TNeg_eqt) as TNeg_eqtSum sum(TNeg_drv) as TNeg_drvSum

If the fields are named differently between indexes, you would use an eval to create the regular field:



( index=BI_1 sourcetype=jobs_info fieldJ IN (Flamengo) ) OR 

( index=BI_2 sourcetype=tel_drv )

| eval TNeg_eqt = case(index=="BI_1", TNeg_eqt_BI_1, index=="BI_2", neg_BI_2)

| eval TNeg_drv = case(index=="BI_1", TNeg_drv_BI_1, index=="BI_2", drv_BI_2)

| stats sum(TNeg_eqt) as TNeg_eqtSum sum(TNeg_drv) as TNeg_drvSum

Without knowing more about your data, it's hard to provide an accurate answer.

Also, when you do a | stats sum(x) by _time you're usually only going to end up with the same number in the stats calculation because _time will most likely be unique, unless you're using bucket or bin.

Re: Two searches using three indexes with different fields

nsantiago17 — Wed, 30 Sep 2020 01:03:02 GMT

Hi,
It's not easy to show some sample bc I'm dealing with classified data and I'm afraid that can be prejudicial to me thats the reason.
TNeg_eqt and the other one are variable that I receive from the source and they are numbers of negociations per day. The dedup was sent wrong so I apologize for that. All I need is to generate a table that contains the _time, TNeg_eqt* and TNeg_drv*. Then I can add some other fields that belong to the index (BI_1)

*Those guys have the same index (BI_2) but different sourcetypes and in each one of them I have different fields to extract.

I hope it makes easier to understand but it's complicated to show everything that I need help, thanks again;

Re: Two searches using three indexes with different fields

jnudell_2 — Mon, 24 Jun 2019 19:03:30 GMT

I updated the answer to try and provide more information.

Re: Two searches using three indexes with different fields

nsantiago17 — Tue, 25 Jun 2019 13:11:51 GMT

Worked well, I really appreciate your help.