Hello,
I am trying to add fields for month and include the count of tickets in each month. I bolded the part of the search below that is not loading data correctly. This is my current search but count(eval(date_month=august))
is showing zero but if I search date_month=august | stats count as AUG
- I get the actual number. What is my problem?
index=indexname $oci$ | stats count(eval(date_month=july)) as JUL, count(eval(date_month=august)) as AUG, count(eval(date_month=september)) as SEP, count(eval(date_month=october)) as OCT, count(eval(date_month=november)) as NOV, count(eval(date_month=december)) as DEC, count(eval(date_month=january)) as JAN, count(eval(date_month=february)) as FEB, count(eval(date_month=march)) as MAR, count(eval(date_month=april)) as APR, count(eval(date_month=may)) as MAY, count(eval(date_month=june)) as JUN, count as TOTAL by cmdb_ci | join type=outer overwrite=false cmdb_ci [search index=it_snow_call_kiosk_logs_weekly| stats count as TicketCount by cmdb_ci date_month | stats avg(TicketCount) as Baseline by cmdb_ci] | eval Baseline = round(Baseline,0) | table cmdb_ci Baseline JUL AUG SEP OCT NOV DEC JAN FEB MAR APR MAY JUN TOTAL | sort 0 -TOTAL
This is tricky. When you use eval
, it uses where
-style logic ("WSL") which is slightly different than search
-style logic ("SSL"). WSL presumes that the right-hand-side ("RHS") s a field name, where as SSL presumes it is a string. Furthermore, you cannot make SSL interpret the RHS as a field name HOWEVER you can make WSL interpret either. The way to make WSL interpret the RHS as a string is to put it inside double-quotes, like count(eval(date_month="august"))
. This is why we always teach people to use search
when RHS is a constant and use where
when RHS is a field name (even though you can make where
do either) and also to ALWAYS use double-quotes when RHS is a constant, not a field name, even when (e.g. with search
) it is not necessary.
try this,
index=indexname $oci$ | timechart span=1mon count as TOTAL by cmdb_ci | eval date_month=strftime(_time,"%b") | join type=outer overwrite=false cmdb_ci [search index=it_snow_call_kiosk_logs_weekly| stats count as TicketCount by cmdb_ci date_month | stats avg(TicketCount) as Baseline by cmdb_ci] | eval Baseline = round(Baseline,0) | table cmdb_ci Baseline date_month TOTAL | sort 0 -TOTAL
You're comparing string so value of the month should be in double quotes. ( count(eval(date_month=july)
should be count(eval(date_month="july"))
)