topic Re: How to write a query to get the result clusterwise in Splunk Search

How to write a query to get the result clusterwise

iqbalintouch — Tue, 29 Sep 2020 19:05:54 GMT

So my base Query to check sell is below:-

index=myapp sourcetype=my_sourcetype host="*myhost*" "Logger*" AND "sold event" vertical=H

Now, I need to write an efficient and fast query which shows cluster-wise sell?

like my_host1 - my_host3 is cluster 1
AND my_host4 - my_host6 is cluster 2
AND my_host7 - my_host9 is cluster 3

Re: How to write a query to get the result clusterwise

p_gurav — Sun, 15 Apr 2018 04:55:25 GMT

Can you write eval:

| eval cluster=case(host=host1 OR host=host2 OR host=host3, "cluster1") and so on...

Re: How to write a query to get the result clusterwise

iqbalintouch — Tue, 29 Sep 2020 19:05:57 GMT

@p_gurav Thank you.

do I need to write eval in new line for each cluster? My requirement is actually something like below with base search query.

| timechart partial=f span=15m count as current_count
| streamstats window=10 current=f avg(current_count) as trend
| eval cluster=case(host=my_host01* OR host=my_host02 OR host=my_host03*, "cluster1")
| eval cluster=case(host=my_host04* OR host=my_host05* OR host=my_host06*, "cluster2")
| eval cluster=case(host=my_host07* OR host=my_host08 OR host=my_host09, "cluster3")
| eval trend=round(trend)
| eval difference=current_count-trend
| eval diff_percent=round((difference)/trend*100)
| eval hr=strftime(_time, "%H")
| table _time trend current_count difference diff_percent

Re: How to write a query to get the result clusterwise

iqbalintouch — Sun, 15 Apr 2018 05:10:47 GMT

sorry I am not an expert in Splunk and learning basic of it. Thank you.

Re: How to write a query to get the result clusterwise

p_gurav — Sun, 15 Apr 2018 06:07:27 GMT

You can write one eval:

index=myapp sourcetype=my_sourcetype host="myhost" "Logger*" AND "sold event" vertical=H
| timechart partial=f span=15m count as current_count
| streamstats window=10 current=f avg(current_count) as trend
| eval cluster=case(host=my_host01* OR host=my_host02* OR host=my_host03*, "cluster1", host=my_host04* OR host=my_host05* OR host=my_host06*, "cluster2", host=my_host07* OR host=my_host08 OR host=my_host09, "cluster3")
| eval trend=round(trend)
| eval difference=current_count-trend
| eval diff_percent=round(difference/trend*100)
| eval hr=strftime(_time, "%H") 
| table _time trend current_count difference diff_percent cluster

Re: How to write a query to get the result clusterwise

iqbalintouch — Sun, 15 Apr 2018 07:29:33 GMT

@p_gurav

getting error: "Error in 'eval' command: The expression is malformed. Expected )"

checked the query but didn't see anything is missing

Re: How to write a query to get the result clusterwise

Sukisen1981 — Sun, 15 Apr 2018 08:03:07 GMT

Hi,

host is a string just change your first eval to - eval cluster=case(host="my_host01*" OR host="my_host02*" OR host="my_host03*", "cluster1", host="my_host04*" OR host="my_host05*" OR host="my_host06*", "cluster2", host="my_host07*" OR host="my_host08" OR host="my_host09", "cluster3")

Re: How to write a query to get the result clusterwise

DalJeanis — Sun, 15 Apr 2018 23:41:39 GMT

Okay, you've almost got it. One problem you are running into now is probably because you are using incorrect syntax for your comparisons.

When you are running a search, you can use * at the end of a literal to tell splunk to match anything else that follows.

| search host="myhost_01*"

However, that comparison is not valid as a normal boolean test. In a boolean, you need to use one of the eval functions such aslike(variable,SQLPattern) or match(variable,RegexPattern), as per this...

| eval cluster=case(like(host,"my_host01%"), "FirstValue", ... )

...or this...

| eval cluster=case(match(host,"my_host01.*"), "FirstValue", ... )

Re: How to write a query to get the result clusterwise

iqbalintouch — Mon, 16 Apr 2018 01:10:23 GMT

Thank you @Sukisen1981