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

[niks987]

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.

[VatsalJagani]

@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!!!

[Shan · Accepted solution]

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]

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.

[Shan]

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 ..

[niks987]

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.

[niks987]

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.