Calculations on Timechart Counts

[jchensor]

Hello, everyone.

I have a field known as EVENTTYPE and I’m doing a timechart based on the EVENTTYPEs found. So the query looks something like this:

index=web EVENTTYPE="field1" OR EVENTTYPE="field2" OR EVENTTYPE="field3" earliest=-7d@d latest=@d | timechart span=1d count by EVENTTYPE

So I get results like:

    _time                       field1      field2      field3
1   9/27/12 12:00:00.000 AM     4342        4828        4139
2   9/28/12 12:00:00.000 AM     6802        5681        4844
3   9/29/12 12:00:00.000 AM     6791        5524        4682
4   9/30/12 12:00:00.000 AM     6852        4762        4083
5   10/1/12 12:00:00.000 AM     3930        4380        3762
6   10/2/12 12:00:00.000 AM     4064        3298        2836
7   10/3/12 12:00:00.000 AM     6064        3680        3108

What I want to do is add a few columns calculating a percentage. For example, I want a column between field2 and field3 that is essentially field2 divided by field1. And then add a column at the end after field3 that is field3 divided by field2.

The problem is, since these columns are generated by the “timechart” command, I don’t know how to assign them to a variable name to be used for mathematical manipulation. I thought I could just access them directly just by the column name, but I tried the following and it didn’t seem to work:

index=web EVENTTYPE="field1" OR EVENTTYPE="field2" OR EVENTTYPE="field3" earliest=-7d@d latest=@d | timechart span=1d count by EVENTTYPE | eval percentage=field2/field1 | fields + field1, field2, percentage, field3

So I'm not sure how to go about doing this. Thanks, everyone, for any help!

• James

[jchensor · Accepted solution]

Apparently, in order to do calculations on field names with the "eval" command and performing any sort of mathematical computations on them, DO NOT USE DAHSES in the name of the field. In other words, if your field name is something like:

field-1

It won't work. However, if you use the "rename" function to change it to something such as:

field_1

It works just fine. In the example I had above, I changed the names of my fields to something generic for this public post. But in actuality, the names I have contained Dashes. As soon as I used the rename function to get rid of the dashes, everything worked normally.

• James

[jchensor]

I don't think quotes will work. Quotes turns the text into a "string" instead of keeping it as a number, so the calculations will be rejected trying to pass a string into a field that needs to be an integer. Renaming the field seems to be the only way around this for now.

[emiller42]

Is the issue still there if you quote the fields? "field-1" instead of just field-1?

[emiller42]

what you're showing here should work. If I do the following:

index=_internal sourcetype=splunkd OR sourcetype=splunkd_access | timechart count by sourcetype | eval percentage=splunkd_access/splunkd

I get my timechart with an additional column called 'percentage' that has the appropriate ratio. I would check case on your column names, as they are case-sensitive when referenced further down the pipe.

[l0pher]

Awesome! I didn't know timechartcan do additional columns if we do an eval on it. Thanks a lot!!

[jchensor]

Yeah, I stumbled upon a random post somewhere else mentioning something, and apparently it fixed my problem: I can't use field names with '-' characters in them! I changed my field names for this public post, but my actual field names contained dashes in their name. I don't know if this is a bug with Splunk or what, but if I use a "rename" on the fields and change their names to contain no dashes, suddenly it all works normally.

[dart]

Percentage of what? Normally I'd sum all three fields and divide each field by the sum to get the percentage of events that are that eventtype. Is that what you're trying to do?