Archive

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

Path Finder

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.

alt text

How to achieve this is a single search? Please advise

Tags (1)
0 Karma
1 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

View solution in original post

0 Karma

Path Finder

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

0 Karma

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

View solution in original post

0 Karma

Path Finder

That works!

Thank you so much.

0 Karma

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

0 Karma

Influencer

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

0 Karma

Path Finder

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

0 Karma

Influencer

edited the original answer as well

0 Karma

Path Finder

I am getting NULL for everything

0 Karma

Influencer

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")

0 Karma

Path Finder

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 
0 Karma

Path Finder

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.

0 Karma

Influencer

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

0 Karma

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

0 Karma

Path Finder

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!