Splunk Search
Highlighted

How to write a query to get the result clusterwise

Path Finder

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 myhost1 - myhost3 is cluster 1
AND myhost4 - myhost6 is cluster 2
AND myhost7 - myhost9 is cluster 3

0 Karma
Highlighted

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

Champion

Can you write eval:

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

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

Path Finder

@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 currentcount
| streamstats window=10 current=f avg(current
count) as trend
| eval cluster=case(host=myhost01* OR host=myhost02 OR host=myhost03*, "cluster1")
| eval cluster=case(host=my
host04* OR host=myhost05* OR host=myhost06, "cluster2")
| eval cluster=case(host=my_host07
OR host=myhost08 OR host=myhost09, "cluster3")
| eval trend=round(trend)
| eval difference=currentcount-trend
| eval diff
percent=round((difference)/trend*100)
| eval hr=strftime(time, "%H")
| table _time trend current
count difference diff_percent

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

Champion

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

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

Path Finder

@p_gurav

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

checked the query but didn't see anything is missing

0 Karma
Highlighted

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

Champion

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

0 Karma
Highlighted

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

Path Finder

Thank you @Sukisen1981

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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", ... )
0 Karma