How to edit my search to create a table that shows number of attacks from each security device and summary ?

[bugnet]

Hey all,
I'm trying to create table for SOC members that shows number of attacks from each security device + summary,

My search:

index=CheckPoint  priority>=5 | iplocation src | stats count( index ) by src,Country | rename count(index) as CheckPoint-FW

Table:

src                |       Country         |         CheckPoint-FW
101.xxx.xxx.93     |       China           |         35
51.xx.x.3          |       US              |         21

I need to add two more columns- IPS (index=ips) and Summary.
It should look like this:

Table:

src            |           Country         |          CheckPoint-FW    |    IPS    |     Summary
101.xxx.xxx.93 |           China           |          35               |    10     |     45
51.xx.x.3      |           US              |          21               |    10     |     31

Any ideas ?

[somesoni2 · Accepted solution]

I'm guessing this as you didn't mention what does the index=ips contains. Assuming it has same data as index=CheckPoint
Updated

index=CheckPoint OR index=ips priority>=5 
| eval src=coalesce(src,src_ip)
| chart count over src by index 
| iplocation src | table src Country CheckPoint ips
| rename CheckPoint as "CheckPoint-FW" ips as IPS 
| addtotals labelfield=Summary

[bugnet]

Hi,
No, the data is not the same:

index=checkpoint: the source ip field is "src"
index=ips: the source ip field is "src_ip"

[bugnet]

Thanks for the help!
I get the next error:
" Error in 'eval' command: The 'coalesec' function is unsupported or undefined

[bugnet]

Sorry. Its OK!!

[bugnet]

Hi, I have problem using the "chart" command. you have another idea instead using chart?

[somesoni2]

Try like this

index=CheckPoint OR index=ips priority>=5 
 | eval src=coalesce(src,src_ip) | eval CheckPoint=if(index="CheckPoint",1,0) | eval ips=if(index="ips",1,0) 
 | stats sum(CheckPoint) as CheckPoint sum(ips) as ips by src
 | iplocation src | table src Country CheckPoint ips
 | rename CheckPoint as "CheckPoint-FW" ips as IPS 
 | addtotals labelfield=Summary

Any special issue with using chart command?

[bugnet]

Hi, its look OK, but if I have one more field that created with eval. How I can show it on the table?

the field is Action:
| eval Action = if(msg="ip is block","B","Not blocked")

The table should shows number of attacks from each security device + summary, When the Action field should indicate whether the src address is already blocked:

src | Country | ips | checkpoint | Summary | Action
101.xxx.xxx.93 | China | 35 | 10 | 45 | B
51.xx.x.3 | US | 21 | 10 | 31 | Not blocked

[somesoni2]

Try the updated answer.