How would I divide this table into hours?

[wagnerj02]

source=****** "Result from operation"
| rex field=message ".*?returnCode=(?<code>\d+).*"
| eval status=if(code=0000,"success","failure")
| stats count(eval(status="success")) as complete, count(eval(status="failure")) as incomplete
| eval success = complete, failures = incomplete, total=(success +failures), percent = (success/total)
| table total, complete, failures, percent

No matter what I try, when I try to add time to this table it does not display a seperate search with

|eval hour=strftime(_time, "%H")
|table hour

works. Why can't I add hour to the table above? What would I do instead?

[macadminrohit · Accepted solution]

You can either do this bucket _time span=1h add the bucket command after your first pipe and then add _time to your stats . Or i would use eventstats . Let us know how it works for you.

[wagnerj02]

like this? it is saying:

Error in 'stats' command: The argument '_time' is invalid.

| bucket _time span=1h
| rex field=message ".?returnCode=(?\d+)."
| eval status=if(code=0000,"success","failure")
| stats count(eval(status="success")) as complete, count(eval(status="failure")) as incomplete, _time
| eval success = complete, failures = incomplete, total=(success +failures), percent = (success/total)
| table total, complete, failures, percent, _time


[macadminrohit]

you missed the by clause in your stats command.

[wagnerj02]

| bin _time span=1h as hour
| rex field=message ".?returnCode=(?\d+)."
| eval status=if(code=0000,"success","failure")
| stats count(eval(status="success")) as complete, count(eval(status="failure")) as incomplete by _time
| eval success = complete, failures = incomplete, total=(success +failures), percent = (success/total)
| table total, complete, failures, percent, _time

no luck, it is still breaking up in to every transaction

[wagnerj02]

oh woops i left it as hour on accident, I think it is working now

[macadminrohit]

Can you accept the answer if it worked for you ?

[richgalloway]

The problem lies with the stats command. Any field not explicitly mentioned in the command is not available to subsequent commands. In your example, 'complete' and 'incomplete' are the only fields passed on by stats so _time is not available to the eval. The solution is to include _time in stats or use streamstats. The streamstats command does not remove fields.

[wagnerj02]

I see, thank you for that pointer! But I am running into issues still.

Adding _time to stats doesn't seem to be working. If I stream stats, it gets divided up into individual transactions which I also can't seem to work around?