Error in 'eval' command: The expression is malformed. Expected AND.

[woodencraft]

Hello,

I am trying to execute the following query but keep getting... Error in 'eval' command: The expression is malformed. Expected AND.

. . . | streamstats current=f last(_time) as last_time by host | eval gap = last_time - _time | where gap > 50 | convert ctime(last_time) as last_time | eval refresh_seconds = (avg(last_time) / 1000) as refresh_minutes

What am I doing wrong?

[PickleRick]

avg() is a stat function, not eval one.

And judging by your use of "as" clause, you wanted to use stats indeed.

[johnhuang]

What are you trying to accomplish? 

[woodencraft]

I did figure out the problem is I did not need the 'as refresh_minutes' in for the eval

Ultimately I want a table that shows time gaps > 50 between 2 events. I am refreshing a token every 50 minutes and I need to see those gaps in a table

I've changed it to this but I get no data in the time_in_minutes column.

. . . | streamstats current=f last(_time) as last_time by host | eval gap = last_time - _time | where gap > 50 | convert ctime(last_time) as last_time | eval time_in_minutes = last_time / 60000 | table LocalTranId time_in_minutes

[PickleRick]

First you use ctime() to render timestamp to string then you're trying to divide the string by number. It doesn't work that way.

BTW, why do you want to divide by 60000?  Timestamps are in seconds.

[woodencraft]

I figured out about not dividing by 60000. I changed to 60 and then round(time_in_minutes, 0) to get rid of the decimal places. I also took out the ctime()

This is what I have now:

. . . | streamstats window=2 range(_time) as timediff | eval time_in_minutes = timediff / 60 | eval time_in_minutes = round(time_in_minutes, 0) | table _time LocalTranId time_in_minutes

Based on how I described what I want is the 'window=2' giving me what I am looking for? I scanned through events around the time of the 51 shown in the table below and I can't find any 2 events > 50 minutes

It's clear I'm new at this. I've only been creating simple queries which I'm sure this is probably very simple to you and I appreciate you helping me. I've just  now realized I need to do more to get what I really need.

[PickleRick]

You have the event immediately after, that's 51 minutes later.

Remember that splunk generally* returns search events in reverse chronological order (from most recent to oldest), so - unless you resort them - you're calculating streamstats from the most recent events. Which means that it's the difference from the previous event _in the event list_, not chronologically. So - in your case - you're calculating the difference from the event that happened immediately _after_ the one you're analysing.

*There are some additional factors affecting event order if you're using commands like append or multisearch.