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
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
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
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
That works!
Thank you so much.
You're welcome. I've converted it to an answer.
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
getting the below error. Error in 'eval' command: The arguments to the 'case' function are invalid.
edited the original answer as well
I am getting NULL for everything
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")
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
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.
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
Can you share the queries that are getting you these results and some examples of the events being returned by them?
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