Can you help me with my query within a query?

[mansinchu]

Hi,

I am trying to see if this type of query is possible

I am creating an alert base on 2 conditions.

The first condition must be met and then check the second condition

First Condition: tid result be unique and more than 4 results return
Second Condition: host must be unique and must be more than 1 hosts

Query:

("ERROR")|dedup tid|eval hostname=substr(host,1,5)

It will return something like this

Original host name is

londn1
londn2
eurpn1
eurpn2

The eval will give me

londn and eurpn

I am able to get the first condition working. Once I get the first condition met, I will need to check the result to make sure it comes from more than 1 host (without the 1 and 2 in the hostname).

How should I go about this?

[mansinchu]

Thank you.

[DalJeanis]

You don't need to overcomplicate your query. you are just pulling additional information out of what you have already done, not doing another query.

your search
| stats values(host) as host by tid
| where mvcount(host)>3
| mvexpand host
| eval shost=substr(host,1,5)
| stats values(host) as host values(shost) as shost by tid
| where mvcount(shost) > 1

Of course, once you review that code, you really don't need to put it together, pull it all apart and put it back together again.

Just do it this way...

your search
| eval shost=substr(host,1,5)
| stats values(host) as host values(shost) as shost by tid
| where mvcount(shost) > 1 AND mvcount(host)>3

[mlevsh]

@DalJeanis

Can we use mvcount to get result requested in the question?
Let's say we have multi-valued field like cve

cve
CVE-2011-0653;CVE-2011-1252;CVE-2011-1890;CVE-2011-1891;CVE-2011-1892;CVE-2011-1893
CVE-2011-0653;CVE-2011-1252;CVE-2011-1890;CVE-2011-1891;CVE-2011-1892
CVE-2011-0653;CVE-2011-1252;CVE-2011-1890

Then we can apply
...|eval cvecount=mvcount(split(cve,";))

That will give us result

cvecount cve
6               CVE-2011-0653;CVE-2011-1252;CVE-2011-1890;CVE-2011-1891;CVE-2011-1892;CVE-2011-1893
5               CVE-2011-0653;CVE-2011-1252;CVE-2011-1890;CVE-2011-1891;CVE-2011-1892
3               CVE-2011-0653;CVE-2011-1252;CVE-2011-1890

@mansinchu , I believe, wants to accomplish the following :

Alert needs to be triggered when number of unique tid values is more than 4 with numbers of unique host values more than one

For example:
These results should trigger the alert : 5 unique tid with 2 unique hosts (londn and eurpn)

host        tid
londn       123
londn        456
eurpn        786
eurpn        910
eurpn        135

These results should not trigger the alert: 4 unique tid values but with just one unique host londh

host        tid
londn       123
londn        456
londn       786
londn        910
londn        135