Splunk Search

How do I extract a string of numbers using Rex to work the AVG out from a string to a number

Engager

How do I extract a string of numbers using Rex to work the AVG out from a string to a number As it is showing as blank. The number format 00:00:00.0000000.

 

thanks Joe

Labels (3)
0 Karma

Champion

Hi

as we discussed on  Issue with the AVG however min and max work fine.  this is probably on part of date + time field not pure string? Are you interested only for hour, min, sec, fraction part or full timestamp?

Here is how you could convert this time to epoch:

| makeresults
| eval foo = "00:00:00.0000000"
| eval bar = strptime (foo, "%H:%M:%S.%7Q")
| eval foobar = strftime (bar, "%H:%M:%S.%7Q")
| table foo bar foobar
0 Karma

Engager

Hi, I just need to pull the AVG from the string field.

 

0 Karma

Champion

You cannot calculate AVG from string field. Avg function is available only for numeric fields. 

https://docs.splunk.com/Documentation/Splunk/8.0.5/SearchReference/Aggregatefunctions

There are more what and when you could use AVG and other aggregate functions.

Of course if string field's contains numeric values you could first transform string to numeric and then use AVG e.g. with stats to get the average.

0 Karma

Engager

Hi, I did try Convert to Convert the string into Numeric but got the same issue.

0 Karma

Ultra Champion
| makeresults count=20
| eval _raw="sample: ".printf("%02d",random() % 24).":".printf("%02d",random() % 60).":".printf("%02d",random() % 60).".".printf("%07d",random() % 9999999)
| rename COMMENT as "this is sample"
| rename COMMNET as "rex extracts two fields"
| rex "(?<times>\d{2}:\d{2}:\d{2})\.(?<sec>\d{7})"
| convert dur2sec(times)
| eval sec="0.".sec
| eval seconds=times+sec
| eventstats avg(seconds)

convert is useful.

0 Karma