Splunk Search

How to extract fields from my raw data?

rafiqul
New Member

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 :

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.

0 Karma

gvmorley
Contributor

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>[^,]+),"

alt text
(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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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]*)" | ...
---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...