after doing some more testing to see if i could figure it out. i created the stats in my own lookup table and added perc99 to it so that i could test high values. but to test it out to make sure it was working i ran the following part of a search
| stats sum(count) as sumtotal by identity_id |search identity_id=edf5a5e02d47234647599dd1e76c61ee23a09127 |lookup 90_day_email_stats.csv identity_id OUTPUT 99_perc | rename "99_perc" as extremenumber |search sumtotal > extremenumber
which failed even though i knew that sumtotal was greater than the extremenumber. changed the logic operator to less than which should not have worked.
| stats sum(count) as sumtotal by identity_id |search identity_id=edf5a5e02d47234647599dd1e76c61ee23a09127 |lookup 90_day_email_stats.csv identity_id OUTPUT 99_perc | rename "99_perc" as extremenumber |search sumtotal < extremenumber
this got me the output
identity_id, sumtotal, extremenumber
edf5a5e02d47234647599dd1e76c61ee23a09127, 2972, 32
so the only thing i could think of is maybe the 2972 is not actually a number but a string. not sure why since its only calculated via sum in stats. but what they heck lets check. did a |convert num(sumtotal) then tested again and got the same results. figured i check via an eval | eval test=if(isnum(sumtotal),"is number","not number") and test came out to "is number". so obviously its a number and splunk knows that. so lets see if we can do math on it other than logic. so i tried | eval test=if((sumtotal-extremenumber)>=0,1,0)
this got me the greater than and less than logic i needed. The solution i had to come up with to get it to do logic was:
| stats sum(count) as sumtotal by identity_id |lookup 90_day_email_stats.csv identity_id OUTPUT 99_perc | rename "99_perc" as extremenumber | eval test=if((sumtotal-extremenumber)>=0,1,0) | search test=1 | where sumtotal>=[| xsdisplaycontext FROM email_count_per_1h_by_user |sort extreme desc | head 1| rename "email_count_per_1h_by_user/" as search | table search]
Though this doesn't really explain why the logic operator did not work and makes me wonder what else it is failing on which when your in cyber security and integrity is one of the core principles it makes it hard to trust splunk is doing it right.
btw this instance is in a splunk cloud environment.
... View more