alert based on different keywords/sources but from the same index

[kollachandra]

I would like to search for the events from the same index but from different host names. I would like to create a new field names silo based on the host name I would like to name them as silo1 or silo2 by running 1 search instead of 2. Following is the result I am expecting.

How to achieve this is a single search? Please advise

[kollachandra]

index=xyz host=server1 OR host=server2 OR host=server3 OR host=server4
| stats values(host) AS host, count, avg(time_taken) AS avg_time_taken BY cs_uri_stem
| eval Silo=case(host="server1" OR host="server3", "Silo1", host="server2" OR host="server4", "Silo2", 1=1, "SILO NOT FOUND")
| eval avg_time_taken=round(avg_time_taken, 2)
| table cs_uri_stem count avg_time_taken Silo

[elliotproebstel · Accepted solution]

How about this:

index=xyz host=server1 OR host=server2 OR host=server3 OR host=server4
| stats values(host) AS host, count, avg(time_taken) AS avg_time_taken BY cs_uri_stem
| eval Silo=case(host="server1" OR host="server3", "Silo1", host="server2" OR host="server4", "Silo2", 1=1, "SILO NOT FOUND")
| eval avg_time_taken=round(avg_time_taken, 2)
| table cs_uri_stem count avg_time_taken Silo

[kollachandra]

That works!

Thank you so much.

[elliotproebstel]

You're welcome. I've converted it to an answer.

[strive]

Try this
index = xyz
| stats count avg(time_taken) by cs_uri_stem, host
| eval Silo = case(1==1 AND (host=server1 OR host=server3), "Silo1", 1==1 AND (host=server2 OR host=server3), "Silo2", true(), "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)
| table cs_uri_stem count avg(time_taken) Silo

[kollachandra]

getting the below error. Error in 'eval' command: The arguments to the 'case' function are invalid.

[strive]

edited the original answer as well

[kollachandra]

I am getting NULL for everything

[strive]

Modified Search:

index = xyz
| stats count avg(time_taken) by cs_uri_stem, host
| eval Silo = case(1==1 AND (host=server1 OR host=server3), "Silo1", 1==1 AND (host=server2 OR host=server3), "Silo2", true(), "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)
| table cs_uri_stem count avg(time_taken) Silo

Tested similar search locally and works
index=XYZ | stats count avg(bytes) by column1 | eval Silo = case(1==1 AND column1="CACHE_MISS", "Silo1", 1==1 AND (column1="CACHE_MEM_HIT" OR column1="CACHE_REVALIDATED_MEM_HIT"), "Silo2", true(), "NULL")

[FeatureCreeep]

There are different options based on what your data looks like. If you really only have 2 hosts then you can do something simple like this. You can create a new field called silo and then set it to the correct value based on which host the event is from.

host=Host1 OR host=Host2 | eval silo=case(host="Silo1Critera", "Silo1", host="Silo2Critera", "Silo2") | stats count, avg(time_taken) by cs_uri_stem, silo 

[kollachandra]

I really appreciate your reply. This is same exact way I did initially but I wasn't getting any result.
index=iis host=server1 OR host=server3 OR host=server2 OR host=server4
| eval silo=case(host=server1 OR host=server3 , "Silo1",
host=host=server2 OR host=server4 , "Silo2")
| stats count, avg(time_taken) by cs_uri_stem silo
| table count silo

The query I am using now is:
index = xyz ( host=server1 OR host=server3)
| stats count avg(time_taken) by cs_uri_stem
| eval Silo = if(1==1, "Silo1", "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)
| append
[ search index = iis ( host=server2 OR host=server4 )
| stats count avg(time_taken) by cs_uri_stem
| eval Silo = if(1==1, "Silo2", "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)]
| table cs_uri_stem count avg(time_taken) Silo

But this query runs 2 searches, which I would like to avoid 2 searches and implement it in 1 search.

[strive]

Try this
index = xyz
| stats count avg(time_taken) by cs_uri_stem, host
| eval Silo = case(1==1 AND (host=server1 OR host=server3), "Silo1", 1==1 AND (host=server2 OR host=server3), "Silo2", "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)
| table cs_uri_stem count avg(time_taken) Silo

[elliotproebstel]

Can you share the queries that are getting you these results and some examples of the events being returned by them?

[kollachandra]

index = xyz ( host=server1 OR host=server3)
| stats count avg(time_taken) by cs_uri_stem
| eval Silo = if(1==1, "Silo1", "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)
| append
[ search index = iis ( host=server2 OR host=server4 )
| stats count avg(time_taken) by cs_uri_stem
| eval Silo = if(1==1, "Silo2", "NULL")
| eval avg(time_taken)=round('avg(time_taken)',2)]
| table cs_uri_stem count avg(time_taken) Silo