topic Re: Multiple sourcetypes with where condition in Splunk Search

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

suspense — Tue, 04 Apr 2023 10:53:28 GMT

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?

Re: Multiple sourcetypes with where condition

suspense — Tue, 04 Apr 2023 08:26:15 GMT

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)

Re: Multiple sourcetypes with where condition

ITWhisperer — Tue, 04 Apr 2023 08:42:58 GMT

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)

Re: Multiple sourcetypes with where condition

javiergn — Tue, 04 Apr 2023 08:45:09 GMT

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 )

Re: Multiple sourcetypes with where condition

Icecream123 — Tue, 04 Apr 2023 09:05:18 GMT

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!

Re: Multiple sourcetypes with where condition

suspense — Tue, 04 Apr 2023 09:07:52 GMT

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.

Re: Multiple sourcetypes with where condition

suspense — Tue, 04 Apr 2023 09:10:40 GMT

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.

Re: Multiple sourcetypes with where condition

Icecream123 — Tue, 04 Apr 2023 09:11:32 GMT

Glad to help!