How to process sum of several fields with regexp in a search?

[Nieucel]

Hello,
I have several events with this kind of format:

2012-1-9 15.0.1.290021000 1:0 BD_PANDORA_PROD_TOTAL_USERS_DE=264
2012-1-9 15.0.1.294548000 1:0 BD_PANDORA_PROD_TOTAL_USERS_FR=1819
2012-1-9 15.0.1.298192000 1:0 BD_PANDORA_PROD_TOTAL_USERS_UK=1384
2012-1-9 15.0.1.301780000 1:0 BD_PANDORA_PROD_TOTAL_USERS_NONLDAP=80
2012-1-9 15.0.1.305185000 1:0 BD_PANDORA_PROD_TOTAL_USERS_ACTIONEES=827
2012-1-9 15.0.1.309442000 1:0 BD_PANDORA_PROD_ACTIVE_ACCOUNTS_DE=191
2012-1-9 15.0.1.313060000 1:0 BD_PANDORA_PROD_ACTIVE_ACCOUNTS_FR=1467
2012-1-9 15.0.1.316673000 1:0 BD_PANDORA_PROD_ACTIVE_ACCOUNTS_UK=1119
2012-1-9 15.0.1.320303000 1:0 BD_PANDORA_PROD_ACTIVE_ACCOUNTS_NONLDAP=56
2012-1-9 15.0.1.323756000 1:0 BD_PANDORA_PROD_TOTAL_PROJECTS=629
...

On 1 month period, I want to get the last value of these fields, for that I run the search:

index=jdbc sourcetype="bd_pandora_prod" BD_PANDORA_PROD_TOTAL_USERS_* | stats values(BD_PANDORA_PROD_TOTAL_USERS_*) by _time | tail 1 

It seems to be working (hope it is the most efficient way to do that!).

Search Ouput:

*...Names of fields ...

1/9/12xxxx 827 264 1819 ...etc...*

Furthermore, I wish to display the sum of these displayed values. How could I do that ?

Search output wish:

_time     fields...                     Total (of preceding fields)                        
1/9/12xxxx 827   264  1819 ...etc...        XXXXXX

Thank you for help.

[gcoles]

Hmm I think I fudged the regex, slightly. Try this is your props.conf:

[bd_pandora_prod]
S+s+S+s+S+s+(?P<bd_pandora_type>.*?)=(?P<bd_pandora_value>d+)

Notice the ?P in the regex groupings? 🙂

Also, as a note, I was informed by a Splunk employee recently that changes to props.conf field extractions no longer require a splunk restart or running the | extract reload=T command (which we used to use for this instead of a restart). Thus, you should be able to update the regex and test without restarting splunk. If the fields still don't appear, they could be in the wrong scope (perhaps the wrong props.conf file or the sourcetype specification is off -- but it should work from $SPLUNK_HOME/etc/apps/search/local/props.conf if you are using the default search app to look at the data.

Another method would be to copy the above props regex, open the field extraction web UI with some of your events selected in a search, and edit the field extraction regex, pasting in the one I provided above (with the ?P). When you save that, it will be in your local user's props.conf until you share it out.

[Nieucel]

Thank you very much "gcoles" for your quick response. It is clear.
As a beginneer on Splunk, I have a last question:
I set in my props.conf file a new stanza:
[bd_pandora_prod]
\S+\s+\S+\s+\S+\s+(?.*?)=(?\d+)

And stop/start the server. But Splunk seems to not take account this directive.
Havve you an idea on this?

[kristian_kolb]

You should probably use

[bd_pandora_prod]
EXTRACT-blah =

/k

[gcoles]

You probably want to create some additional fields, rather than just the automatically determined ones, either with rex command or in props, to get a list of the possible field names that could appear over your timespan, and one for the numeric value that appears at the end of the line (after the equals sign). Let's say you called the first field BD_PANDORA_TYPE, and the second BD_PANDORA_VALUE.

Assuming this type of input:

2012-1-9 15.0.1.290021000 1:0 BD_PANDORA_PROD_TOTAL_USERS_DE=264

Your regex might look like this:

\S+\s+\S+\s+\S+\s+(?<BD_PANDORA_TYPE>.*?)=(?<BD_PANDORA_VALUE>\d+)

Now this completely changes how you can search/sort the data:

index=jdbc sourcetype="bd_pandora_prod" BD_PANDORA_PROD_TOTAL_USERS_* | stats sum(BD_PANDORA_VALUE) AS sum by BD_PANDORA_TYPE

The above search will give you totals for each class (type) of record in your data, for the full time period specified. Now, if you wanted to show daily totals (it seems that you might be from your stats by _time query, then you can use timechart:

index=jdbc sourcetype="bd_pandora_prod" BD_PANDORA_PROD_TOTAL_USERS_* | timechart span=1d sum(BD_PANDORA_VALUE) AS sum by BD_PANDORA_TYPE

Now, if you want to combine them, you can use the append command to run the first search I specified and add it as the last row. You might want to play with eval to rewrite _time for that row to be text like "TOTAL" eg: | eval _time = "TOTAL" (hopefully that works!)