topic Re: How do I extract a numerical field and rename the value? in Splunk Search

How do I extract a numerical field and rename the value?

rwiley — Thu, 10 Nov 2016 15:19:58 GMT

this is the raw data from my search index=myindex sourceype=mysourcetype

2016-11-10 07:41:29 Local7.Debug    22.85.184.15    community=PUBLIC, enterprise=1.3.6.1.4.1.1046.11.1.1.1, uptime=25949301, agent_ip=, version=Ver2, 1.3.6.1.4.1.1046.11.1.1.1.1.1=2, 1.3.6.1.4.1.1046.11.1.1.1.1.2=0, 1.3.6.1.4.1.1046.11.1.1.1.1.3=0, 1.3.6.1.4.1.1046.11.1.1.1.1.4=1, 1.3.6.1.4.1.1046.11.1.1.1.1.5=1, 1.3.6.1.4.1.1046.11.1.1.1.1.6=1, 1.3.6.1.4.1.1046.11.1.1.1.1.7=2, 1.3.6.1.4.1.1046.11.1.1.1.1.8=30, 1.3.6.1.4.1.1046.11.1.1.1.1.9=0, 1.3.6.1.4.1.1046.11.1.1.1.1.10=426353, 1.3.6.1.4.1.1046.11.1.1.1.1.11=1478789235, 1.3.6.1.4.1.1046.11.1.1.1.1.12=1478788830, 1.3.6.1.4.1.1046.11.1.1.1.1.13=1478786400, 1.3.6.1.4.1.1046.11.1.1.1.1.14=4500, 1.3.6.1.4.1.1046.11.1.1.1.1.15=30, 1.3.6.1.4.1.1046.11.1.1.1.1.16=30000, 1.3.6.1.4.1.1046.11.1.1.1.1.17=20001, 1.3.6.1.4.1.1046.11.1.1.1.1.18=4688211, 1.3.6.1.4.1.1046.11.1.1.1.1.19=0, 1.3.6.1.4.1.1046.11.1.1.1.1.20=0, 1.3.6.1.4.1.1046.11.1.1.1.1.21=0, 1.3.6.1.4.1.1046.11.1.1.1.1.22=0000000, 1.3.6.1.4.1.1046.11.1.1.1.1.23=LABCAPMP4, 1.3.6.1.4.1.1046.11.1.1.1.1.24=HLNH, 1.3.6.1.4.1.1046.11.1.1.1.1.25=, 1.3.6.1.4.1.1046.11.1.1.1.1.26=LabTest30_n, 1.3.6.1.4.1.1046.11.1.1.1.1.27=Preroll, 1.3.6.1.4.1.1046.11.1.1.1.1.28=:

i need to break these values into two fields 1.3.6.1.4.1.1046.11.1.1.1.1.1=2

first field 1.3.6.1.4.1.1046.11.1.1.1.1.* the last decimal will be 1-28 i also want to replace this with the description of the code
so 1.3.6.1.4.1.1046.11.1.1.1.1.1 would be Version
1.3.6.1.4.1.1046.11.1.1.1.1.2 would be Box
1.3.6.1.4.1.1046.11.1.1.1.1.3 would be Port etc.

second field =2 i need the part after =. this will be numerical value or a name so it could be a 1,2,3etc or it could read something like Preroll

Re: How do I extract a numerical field and rename the value?

niemesrw — Fri, 11 Nov 2016 01:41:17 GMT

If you can get ahold of the MIB you might be able to create a lookup table and match up those values with the text of the MIB.

Something like this:

OID,name
1.3.6.1.4.1.1046.11.1.1.1.1.1, Version
1.3.6.1.4.1.1046.11.1.1.1.1.2, Box
1.3.6.1.4.1.1046.11.1.1.1.1.3, Port

Although looking at the data is splunk extracting each OID as a separate field?

Re: How do I extract a numerical field and rename the value?

gokadroid — Fri, 11 Nov 2016 04:14:59 GMT

Firstly, to match each of the *.1 as Version, *.2 as Box and *.3 as Port you will require a lookup table which in this case I will call as idLookup.csv and its lookup definition I will call as idLookup_def. See here on how to create a lookup table and lookup definition.

suffix,description
1,Version
2,Box
3,Port
:
:
28,DescriptionValueFor28

Once the lookup table and lookup definition is done, we will need the search part which will extract the data from your events and will lookup the data against the description.

Your data has key value pairs where there are no corresponding values for a particular key after =, for example
1.3.6.1.4.1.1046.11.1.1.1.1.25=,

To handle such values first I have rexed/sed a value NA to make them 1.3.6.1.4.1.1046.11.1.1.1.1.25=NA,. After that you can take the values out in three fields called stringPrefix, stringSuffix and stringValue. such that for the kv pairs in your data like 1.3.6.1.4.1.1046.11.1.1.1.1.23=LABCAPMP4 or 1.3.6.1.4.1.1046.11.1.1.1.1.25=NA will end up with string fields as follows:

stringPrefix with value 1.3.6.1.4.1.1046.11.1.1.1.1.
stringSuffix with value 23 or 25...
stringValue with value LABCAPMP4 or NA...and so on.

Once you have these three strings for each of the 28 key value pair from your data then we can match the stringSuffix with your suffix field in the lookup definition using | lookup command and get the description saved in idLookup.csv.

Below is then the complete query that should achieve the result you seek:

your query to get the events
| rex field=_raw mode=sed "s/=,/=NA,/g"
| rex field=_raw max_match=0 "(?<sP>(\d+\.)+)(?<sS>\d+)=(?<sV>[^,]+)"
| eval tempString=mvzip(sP, (mvzip (sS, sV, "~")), "~")
| mvexpand tempString
| rex field=tempString "(?<stringPrefix>[^~]+)~(?<stringSuffix>[^~]+)~(?<stringValue>(.*))"
| eval myString=stringPrefix."".stringSuffix
| lookup idLookup_def suffix as stringSuffix OUTPUT description as description
| table stringPrefix, stringSuffix, myString, description, stringValue

Hope it helps.

Re: How do I extract a numerical field and rename the value?

rwiley — Fri, 11 Nov 2016 14:18:54 GMT

worked perfectly. i had the lookup but i wasn't sure how to break it down correctly. much thanks!!