How can I see still results from both sourcetypes but only from hosts which have cvs score above 7?

[suspense]

Hello,

Syntax:

index=security sourcetype=EDR:*  | eval dest=coalesce(ip,ipaddress) | stats values(sourcetype) values(cvs) values(warning) values(operating_system) values(ID) by dest 

Problem:

sourcetype contains two sourcetypes: EDR:Security EDS:Assets

In Security I have fields ip, cvs, warning
In Assets I have fields ipaddress, operating_system, ID

I use syntax above and I am happy as I see results from both sourcetypes. 
Now I need to see only results that have cvs above 7. 

The problem is that whenever I user cvs>7 or  | search cvs>7 or |where cvs>7 I can see results from EDR:Security (so from sourcetype that I am looking for condition csv>7). 

How can I see still results from both sourcetypes but only from hosts which have cvs score above 7?

[Icecream123 · Accepted solution]

Hi,

This query works for me to get all the values of more than 7. I reassign the name of "values(cvs) as cvs" before performing the where command.

index=security sourcetype=EDR:* | eval dest=coalesce(ip,ipaddress) | stats values(sourcetype) as sourcetype values(cvs) as cvs values(warning) as warning values(operating_system) as operating_system values(ID) as ID by dest | where cvs>7

These are all the values after the stats.

after |where >7

Hope this was what you were looking to do!

[suspense]

Oh God! Thanks! That was the case, hah. I forgot to rename them and I put where at the end (which did not work)... Stupid mistake. Thanks again.

[Icecream123]

Glad to help!

[javiergn]

Hi @suspense ,

I'm not sure I fully understand your request. If you want to show EDR:Security events where cvs > 7 and all EDR:Assets then you can do the following:

index=security ( (sourcetype=EDR:Security cvs>7) OR sourcetype=EDR:Assets )

[suspense]

I tried:
((sourcetype=EDR:* AND cvs>7) OR sourcetype=EDR:*). But Even If I change cvs>100 I still get results with everything (all cvs, no matter what number it is... even if it is empty)

[ITWhisperer]

Try something like this

index=security (sourcetype=EDR:Security AND cvs>7) OR sourcetype=EDR:Assets
| eval dest=coalesce(ip,ipaddress)
| stats values(ip) as ip values(sourcetype) values(cvs) values(warning) values(operating_system) values(ID) by dest 
| where isnotnull(ip)

[suspense]

This is exactly what I tried but I need to see only events where cvs>7 and in the same row, in the same table I need to see data from the other sourcetype (operating_system, etc.). 

I will make visualization. 

My search without conditions:

index=security sourcetype=EDR:*  | eval dest=coalesce(ip,ipaddress) | stats values(sourcetype) values(cvs) values(warning) values(operating_system) values(ID) by dest 

Search with OR conditions:

index=security ( (sourcetype=EDR:Security cvs>7) OR sourcetype=EDR:Assets )

As you can see if condition is met, I get results from one sourcetype only. If conditions are not met, I get all results i one row. I need to have it all - cvs>7, operating_system, ID, etc. in one row.