Splunk Search

Two searches using three indexes with different fields

Explorer

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.

0 Karma
1 Solution

Builder

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.

View solution in original post

0 Karma

Builder

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.

View solution in original post

0 Karma

Explorer

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;

0 Karma

Builder

I updated the answer to try and provide more information.

0 Karma

Explorer

Worked well, I really appreciate your help.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!