Good afternoon everyone! I'm hoping someone can assist in shedding some light on the following issue.
I'm getting the following error : "Error in 'eval' command: The expression is malformed. Expected )." and I'm uncertain why it isn't functioning. Perhaps there's something I'm missing or I've attempted this eval incorrectly. Any assistance would be greatly appreciated!
I've provided the end part of my overall query, the objective here is to determine if there's an increase in the SCORE by 5% each day over a 3 day period, and if there is on any of the days, then flag it.
| eval SCORE=(Asales + Bsales / ITEM_COUNT)
| stats dc(ITEM) as ITEM_COUNT
values(ITEM) as ITEM
sum(SALES) as TotalSales
sum(SCORE) as SCORE
by _time TITLE
| where ITEM_COUNT > 1
| eval 0DATE=if(_time >= relative_time(now(), "-1d@-d"),SCORE,0)
| eval 1DATE=if(_time >= relative_time(now(), "-2d@-1d"),SCORE,0)
| eval 2DATE=if(_time >= relative_time(now(), "-3d@-2d"),SCORE,0)
| eval 1TREND=if(1DATE > 0DATE*1.05,1,0)
| eval 2TREND=if(2DATE > 0DATE*1.05,1,0)
| eval BREAK=if((2DATE+3DATE) > 0,"TRUE","FALSE")
| table *
everything works up to | where ITEM_COUNT > 1 and I'm getting results as anticipated. but the eval itself is failing. I've also attempted to add each of these evals via an appendpipe but to no avail.
Thanks in advance!
The error message is misleading. The problem is with the field names beginning with digits, which is not allowed. See https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Aboutregularexpressionswithfieldextrac...
Thanks so much, that definitely solves 1 problem! Now I've run into another lol.
| eval DATE0=if(_time >= relative_time(now(), "-1d@-d"),SCORE,0)
| eval DATE1=if(_time >= relative_time(now(), "-2d@-1d"),SCORE,0)
| eval DATE2=if(_time >= relative_time(now(), "-3d@-2d"),SCORE,0)
The initial time frame search is earliest=-72h@h latest=now, so I'll get 3 days worth of results, and I'm trying to eval each of those days into columns, then ultimately eval those against each other as indicated above, but I'm getting the same results for each DATE0 DATE1 DATE2.
current output:
_time | TITLE | ITEM_COUNT | SCORE | DAY0 | DAY1 | DAY2 |
2021-11-19 19:00 | Title1 | foo | 10 | 10 | 10 | 10 |
2021-11-20 19:00 | Title1 | foo | 100 | 100 | 100 | 100 |
2021-11-21 19:00 | Title1 | foo | 1000 | 1000 | 1000 | 1000 |
required output:
_time | TITLE | ITEM_COUNT | SCORE | DAY0 | DAY1 | DAY2 |
2021-11-19 19:00 | Title1 | foo | 10 | 10 | 100 | 1000 |
2021-11-20 19:00 | Title1 | foo | 100 | 10 | 100 | 1000 |
2021-11-21 19:00 | Title1 | foo | 1000 | 10 | 100 | 1000 |
Any thoughts?
thx <3!
Any date that is less than 1 day old is also less than 2 days old and is less than 3 days old so there is some overlap in the evals. We can resolve that with <.
| eval DATE0=if(_time >= relative_time(now(), "-1d@-d"),SCORE,0)
| eval DATE1=if(_time >= relative_time(now(), "-2d@-1d") AND _time < relative_time(now(),"-1d@d"),SCORE,0)
| eval DATE2=if(_time >= relative_time(now(), "-3d@-2d") AND _time < relative_time(now(),"-2d@d"),SCORE,0)
Thank you so much for your help thus far! You've been amazing.
I adjusted the dates as recommended but it still seems like the timeframes aren't being bucketed properly 😕
I used the following:
| eval DATE0=if(_time >= relative_time(now(), "-1d@-d"),SCORE,0)
| eval DATE1=if(_time >= relative_time(now(), "-2d@-1d") AND _time < relative_time(now(),"-1d@d"),SCORE,0)
| eval DATE2=if(_time >= relative_time(now(), "-3d@-2d") AND _time < relative_time(now(),"-2d@d"),SCORE,0)
| eval TREND_DAY1=if(DAY1 > (DAY0*1.05),1,0)
| eval TREND_DAY2=if(DAY2 > (DAY0*1.10),1,0)
| eval THRESHBREAK=if((TREND_DAY1 + TREND_DAY2) > 0,"TRUE","FALSE")
| table _time CCAP NODE_COUNT HEALTH DAY0 DAY1 DAY2 TREND_DAY1 TREND_DAY2 BREAK
_time | TITLE | ITEM_COUNT | SCORE | DAY0 | DAY1 | DAY2 | TREND_DAY1 | TREND_DAY2 | BREAK |
2021-11-22 | TITLE2 | 3 | 52 | 0 | 52 | 0 | 1 | 0 | TRUE |
2021-11-22 | TITLE3 | 2 | 63 | 0 | 63 | 0 | 1 | 0 | TRUE |
2021-11-23 | TITLE3 | 2 | 30 | 30 | 0 | 0 | 0 | 0 | FALSE |
2021-11-24 | TITLE2 | 2 | 46 | 46 | 0 | 0 | 0 | 0 | FALSE |
As you can see, the ITEM_COUNT isn't being appropriately slotted into each DAY* column. It also seems somewhat random as to where it does appear.
TITLE3 DAY0 should be 63
TITLE3 DAY1 should be 30
Yet it shows the opposite, and isn't showing for each row. 😕
I'm at a loss as to why it isn't outputting like expected... Is there something wrong with the way I've written the qeury? Or is there a better way that this can be done?
thx!
I'm not sure I follow what you're trying to do (especially with the trend fields), but wonder if you're trying to do what the bucket command does.
Does this help move you toward your goal?
| eval SCORE=(Asales + Bsales / ITEM_COUNT)
| bucket span=1d _time
| stats dc(ITEM) as ITEM_COUNT
values(ITEM) as ITEM
sum(SALES) as TotalSales
sum(SCORE) as SCORE
by _time TITLE
| where ITEM_COUNT > 1
| table *
I'm currently using | bucket _time span=1d, it helps get the results by day for the SCORE that I'm looking for.
Apologies for the confusion. I'll try and break it down.
If the scores are
DAY0 = 10
DAY1 = 100
DAY2 = 1000
The trend evals are looking for:
Is the score for DAY1 5% greater than DAY0, if so the value for DAY1 is 1, otherwise 0
Is the score for DAY2 10% greater than DAY0, if so the value for DAY2 is 1, otherwise 0
If the sum of DAY1+DAY2 is greater than 0 then THRESHBREAK=TRUE.
I hope that helps clarify.
What it seems like is that the search for the scores within the time frame specified isn't finding it accurately and I'm unsure why.