topic Re: How to add and parse the xml data into splunk in Getting Data In

How to add and parse the xml data into splunk

smusunuri — Thu, 31 Dec 2015 00:15:17 GMT

Structure of the XML file looks like this

10:26:10 PST 16 Nov 2015
    <employee details="ename;position;branch" department="XYZ">AA;systems engineer;seattle
</employee>

1:26:10 PST 16 Nov 2015
    <employee details="ename;position;branch" department="XYZ">BB;Lead;seattle
CC;Tech Lead,Redmond    
</employee>

6:26:10 PST 16 Nov 2015
    <employee details="ename;position;branch" department="XYZ">DD;data architect;annapolis
</employee>

I need the output as:

ename        position            branch
AA           systems engineer    seattle
BB           Lead                seattle
CC           Tech Lead           Redmond    
DD           data architect      annapolis

Re: How to add and parse the xml data into splunk

sundareshr — Thu, 31 Dec 2015 01:41:10 GMT

The following SEDCMD will remove unwanted xml tags and refer to the delim stanza in transforms

SEDCMD-removeunwanted=s/(\<.*\>)(.*)(\W.*\>)/$2/g
REPORT-extractfields=extract_fields

See http://docs.splunk.com/Documentation/Splunk/6.2.0/Data/Anonymizedatausingconfigurationfiles for details

And add the following to your transforms

[extract_fields]
DELIMS = ";"
FIELDS = "ename", "position", "branch"

http://docs.splunk.com/Documentation/Splunk/6.2.0/admin/Transformsconf

Re: How to add and parse the xml data into splunk

smusunuri — Thu, 31 Dec 2015 19:46:31 GMT

Actually I didn't work for me. Data with in tagsis not accepted by using $2. I'm getting output as

ename
10:26:10 PST 16 Nov 2015 $2
1:26:10 PST 16 Nov 2015 $2
6:26:10 PST 16 Nov 2015 $2

Re: How to add and parse the xml data into splunk

smusunuri — Thu, 31 Dec 2015 19:54:05 GMT

When I have a single row of values with in the tags-

 <employee details="ename;position;branch" department="XYZ">AA;systems engineer;seattle
 </employee>

Then I'm able to parse the data properly as I require. But when there are multiple rows ( multiple set of values) as mentioned in the above example posted,

<employee details="ename;position;branch" department="XYZ">BB;Lead;seattle
 CC;Tech Lead;Redmond    
 </employee>

then I'm facing difficult to parse the data.

Example that worked out for me.

Data:

10:26:10 PST 16 Nov 2015
     <employee details="ename;position;branch" department="XYZ">AA;systems engineer;seattle
 </employee>

 1:26:10 PST 16 Nov 2015
     <employee details="ename;position;branch" department="XYZ">BB;Lead;seattle
 </employee>

 6:26:10 PST 16 Nov 2015
     <employee details="ename;position;branch" department="XYZ">DD;data architect;annapolis
 </employee>

props.conf

[employee]
SHOULD_LINEMERGE = true (combines multiple lines into single event)
MUST_BREAK_AFTER  = </employee>    (dividing the data into events)
NO_BINARY_CHECK = true
disabled = false
pulldown_type = true
REPORT-employee = emp   (transform stanza name)

transforms.conf

[emp]
REGEX = <employee details="ename,position,branch" department="XYZ">(.*?)</employee>  (regular expression for capturing the data within the tags)
FORMAT = details::$1 (format of the event)
MV_ADD = true (multivalued field)
REPEAT_MATCH = true

CSV data formatting

Splunk Query: 

index = main sourcetype = employee | eval data = split(details,";") | eval name= mvindex(data,0) | eval position = mvindex(data,-2) | eval branch= mvindex(data,-1) | table data, name, position , branch

Output:

 ename        position                          branch
 AA           systems engineer              seattle
 BB           Lead                                    seattle 
 DD           data architect                  annapolis

Re: How to add and parse the xml data into splunk

sundareshr — Thu, 31 Dec 2015 20:12:27 GMT

Not sure I understand your comment "When I have a single row of values with in the tags"? Can you share an example of when it doesn't work

Re: How to add and parse the xml data into splunk

smusunuri — Thu, 31 Dec 2015 20:15:27 GMT

See above. Modified my comment.