topic Re: How to extract fields from my raw data? in Splunk Search

How to extract fields from my raw data?

rafiqul — Wed, 01 Feb 2017 15:41:05 GMT

Need help to extract fields between comma (,). The raw data below have two results, FAILURE and SUCCESS. I want to create some select fields and stats them in to a table. So far I was able to use following regular expression, and extracted USERNAME ( in this example "xxxyyy" is the username extracted from 5th and 6th comma), MACADDRESS (in this example "54-26-96-1B-54-BC" extracted between 8th and 9th comma). Here are the challenges I am facing when I want to to extract SUCCESS/FAILURE and cause fields :

For SUCCESS, I want to extract SUCCESS between 18th and 19th comma, and the services field between 19th and 20th comma.
For FAILURE, I want to extract FAILURE between 17th and 18th comma, and cause field between 19th and 20th comma.

SUCCESS :
2017-02-01T15:17:02.057Z,au:23,MSIAuth,24.27.228.162,!xxxyyy@aaa.company.com,xxxyyy,0/0/0/840,nycmny83-cr01ras01.wifi.rr.com,54-26-96-1B-54-BC,74-3E-2B-2E-16-20:CableWiFi,,,,CableWiFi,95ms,0A440002060000000BD71DFC,86400,,SUCCESS,TWCULTIMATEINTERNET300,ServiceName:ABWAUTHSVC01

FAILURE:
2017-02-01T15:17:01.867Z,au:16,MSIAuth,24.27.228.162,!xxxyyy@aaa.company.com,xxxyyy,0/0/0/840,nycmny83-cr01ras01.wifi.rr.com,54-26-96-1B-54-BC,,,,,CableWiFi,62ms,0A440002060000000BD71DC4,86400,,FAILURE,TWCULTIMATEINTERNET300,DeviceLimit,FAILURE -- FAILURE -- Failure response from 75.180.151.70:1812

So, far following regex provided me a table with TIME STAMP, MACADDRESS and USERNAME (like I mentioned above) :

sourcetype="aaa-AuthAttempts" MSIAuth NOT TWCWiFi-Passpoint failure | rex "MSIAuth\,\d+\.\d+\.\d+\.\d+\,(?[^\,]+)\,(?[^\,]+)\,0\/0\/0\/\d+\,\w{6}\d+\-\w{2}\d+\w+\d+\.\w+\.\w+\.\w+\,(?[^\,]+)" | stats count by _time , MACADDRESS, USERNAME

Can anyone please help to add columns in the table with SUCCESS, FAILURE and other fields based on the pattern of the raw data outlined above ? Such as :

Re: How to extract fields from my raw data?

richgalloway — Wed, 01 Feb 2017 17:22:24 GMT

This is probably not the most elegant solution, but it works on regex101.com with your sample events.

... | rex "MSIAuth,\d+\.\d+\.\d+\.\d+,(?<field1>[^,]+),(?<username>[^,]+),0\/0\/0\/\d+,\w{6}\d+-\w{2}\d+\w+\d+.\w+.\w+.\w+,(?<MAC>[^,]+),[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,[^,]*,(?<status>[^,]*),(?<cause>[^,]*),(?<failureReason>[^,\n]*)" | ...

Re: How to extract fields from my raw data?

gvmorley — Thu, 02 Feb 2017 05:29:16 GMT

Don't feel like you have to do it all in one rex command.

You could do:

| rex "MSIAuth,.*,(?<result>SUCCESS|FAILURE),"
| rex "MSIAuth,.*,FAILURE,.*,(?<failure_cause>[^,]+),"
| rex "MSIAuth,.*,SUCCESS,(?<success_services>[^,]+),"

You can also do some testing by using makeresults, eval & append to create your test data:

| makeresults count=1 
| eval text="2017-02-01T15:17:02.057Z,au:23,MSIAuth,24.27.228.162,!xxxyyy@aaa.company.com,xxxyyy,0/0/0/840,nycmny83-cr01ras01.wifi.rr.com,54-26-96-1B-54-BC,74-3E-2B-2E-16-20:CableWiFi,,,,CableWiFi,95ms,0A440002060000000BD71DFC,86400,,SUCCESS,TWCULTIMATEINTERNET300,ServiceName:ABWAUTHSVC01" 
| append 
    [| makeresults count=1 
    | eval text="2017-02-01T15:17:01.867Z,au:16,MSIAuth,24.27.228.162,!xxxyyy@aaa.company.com,xxxyyy,0/0/0/840,nycmny83-cr01ras01.wifi.rr.com,54-26-96-1B-54-BC,,,,,CableWiFi,62ms,0A440002060000000BD71DC4,86400,,FAILURE,TWCULTIMATEINTERNET300,DeviceLimit,FAILURE -- FAILURE -- Failure response from 75.180.151.70:1812"]
| rename text as _raw
| rex "MSIAuth,.*,(?<result>SUCCESS|FAILURE),"
| rex "MSIAuth,.*,FAILURE,.*,(?<failure_cause>[^,]+),"
| rex "MSIAuth,.*,SUCCESS,(?<success_services>[^,]+),"

(ignore _time in this example; this is created by makeresults. You're just testing your extractions)

The rex statements in the example are fairly 'loose', but if you know your data, you can make them more specific as required.