Splunk Search

How to count number of days a Store in is RED?

niks987
Explorer

Hi All,

I have created a table that displays Store number and its avg(cpu),avg(ram),avg(iowait) using stats command.

sourcetype=xyz OR sourcetype=abc
| stats avg(cpu) avg(.iowait) avg(ram) by source.store_num
| rename avg(cpu) as  Infrastructure 
| rename avg(iowait) as Application 
| rename avg(ram) as Database
| rename store_num as Store_Number
| eval State=case(Infrastructure>"90","Severe",Application>"90","Severe",Database>"90","Severe") 
| table Store_Number,Infrastructure,Application,Database, State

Now I want to add a column which displays number of days the store is in red/severe.
Also if all the three are in green than the counter has to reset and days becomes 0.

Any ideas ???

Thanks in advance.

0 Karma
1 Solution

Shan
Builder

Dear @niks987 ,

Try below query ..

sourcetype=xyz OR sourcetype=abc
| eval Date=strftime(_time,"%d")
| dedup Date
| stats avg(cpu) as Infrastructure, avg(.iowait) as Application, avg(ram) as Database, count(Date) as Date by source.store_num
| rename store_num as Store_Number
| eval State=case(Infrastructure>90,"Severe",Application>90,"Severe",Database>90,"Severe")
| stats values(Infrastructure) as Infrastructure,values(Application) as Application,values(Database) as Database, sum(eval(if(Infrastructure>90 AND Application>90 AND Database>90, Date,0))) as Total_Days, values(State) as State by Store_Number
| table Store_Number,Infrastructure,Application,Database, State,Total_Days

Thanks ..

View solution in original post

VatsalJagani
SplunkTrust
SplunkTrust

@niks987,

How about this query. This query will find noOfDays till it is being Severe for each different store_num. If today's State is not Severe then it will show noOfDays=0.

sourcetype=xyz OR sourcetype=abc
| bin _time span=1h
| stats avg(cpu) as Infrastructure, avg(iowait) as Application, avg(ram) as Database by store_num, _time
| eval State=case(Infrastructure>"90","Severe",Application>"90","Severe",Database>"90","Severe")
| eval stateno = if(State="Severe",1,0)
| sort - _time
| streamstats min(stateno) as stateno by store_num
| stats avg(Infrastructure) as Infrastructure, avg(Application) as Application, avg(Database) as Database, sum(stateno) as noOfDays by store_num

Hope this helps!!!

0 Karma

Shan
Builder

Dear @niks987 ,

Try below query ..

sourcetype=xyz OR sourcetype=abc
| eval Date=strftime(_time,"%d")
| dedup Date
| stats avg(cpu) as Infrastructure, avg(.iowait) as Application, avg(ram) as Database, count(Date) as Date by source.store_num
| rename store_num as Store_Number
| eval State=case(Infrastructure>90,"Severe",Application>90,"Severe",Database>90,"Severe")
| stats values(Infrastructure) as Infrastructure,values(Application) as Application,values(Database) as Database, sum(eval(if(Infrastructure>90 AND Application>90 AND Database>90, Date,0))) as Total_Days, values(State) as State by Store_Number
| table Store_Number,Infrastructure,Application,Database, State,Total_Days

Thanks ..

niks987
Explorer

Hi shankarananth,

Thanks for your reply.

I tried the query but it is not showing any results. Also if i am trying by splitting the query it only displays few store number.

0 Karma

Shan
Builder

Hi @niks987,

Can you kindly share screen shot of complete query result and Query, result of splitting the query and splitting Query.
If possible try to run the query line by line and find the Issue and see whether your getting the expected result in each line of the query..

Thanks ..

0 Karma

niks987
Explorer

Hi shankarananth,

I tried to run the query line by line, when i don't use eval Date, it displays all the stores but when i use eval Date it displays only 6 store. And if i add full query then it won't display anything.

0 Karma

niks987
Explorer

So now the i have updated the query and it seems issue was due to dedup.

sourcetype=xyz OR sourcetype=abc
| eval Date=strftime(_time,"%d")
| stats avg(cpu) as Infrastructure, avg(iowait) as Application, avg(.ram) as Database, values(Date) as Date by source.store_num
| rename source.store_num as Store_Number
| eval State=case(Infrastructure>90,"Severe",Application>90,"Severe",Database>90,"Severe")
| stats values(Infrastructure) as Infrastructure,values(Application) as Application,values(Database) as Database, sum(eval(if(Infrastructure>90 OR Application>90 OR Database>90, Date,0))) as Total_Days, values(State) as State by Store_Number
| stats if(Total_Days==0,0,count(Total_Days))
| table Store_Number,Infrastructure,Application,Database, State,Total_Days

Using this is am getting all the store but the Total_Days column is adding all the dates i.e. if date is 9th and 10th June so its adding and displaying 19.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...