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
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.
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.
worked perfectly. i had the lookup but i wasn't sure how to break it down correctly. much thanks!!
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?