topic Re: How to split one Line into multiple lines while search in Splunk Search

How to split one Line into multiple lines while search

sdaruna — Thu, 31 Dec 2015 15:32:40 GMT

In reference to my other post
https://answers.splunk.com/answers/337397/how-to-break-xml-in-search-time.html

I am adding other way of the question.

I have total xml data in a field like below.

<?xml version="1.0" encoding="UTF-8"?>
<Document xsi:noNamespaceSchemaLocation="EPA_GEODATA_v1.0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   
    <FacilitySite registryId="110007915364">
        <FacilitySiteName>GREAT SOUTHERN WOOD PRESERVING INC</FacilitySiteName>
        <LocationAddressText>1100 HIGHWAY 431 NORTH</LocationAddressText>
        <LocalityName>ABBEVILLE</LocalityName>
        <LocationAddressStateCode>AL</LocationAddressStateCode>
    </FacilitySite>
    <FacilitySite registryId="110000369084">
        <FacilitySiteName>REMBRANDT FOODS- ABBEVILLE</FacilitySiteName>
        <LocationAddressText>496 INDUSTRIAL PARK RD</LocationAddressText>
        <LocalityName>ABBEVILLE</LocalityName>
        <LocationAddressStateCode>AL</LocationAddressStateCode>
        <LocationZIPCode>36310</LocationZIPCode>
    </FacilitySite>
    <FacilitySite registryId="110055437718">
        <FacilitySiteName>RITE AID #7092</FacilitySiteName>
        <LocationAddressText>514 KIRKLAND STREET</LocationAddressText>
        <LocalityName>ABBEVILLE</LocalityName>
        <LocationAddressStateCode>AL</LocationAddressStateCode>
        <LocationZIPCode>36310-2700</LocationZIPCode>
        <LatitudeMeasure>31.56149</LatitudeMeasure>
    </FacilitySite>
</Document>

I need to break the entire field into multiple rows. like below.

----------------------------------------------------------------------------------------
          <FacilitySite registryId="110007915364">
                <FacilitySiteName>GREAT SOUTHERN WOOD PRESERVING INC</FacilitySiteName>
                <LocationAddressText>1100 HIGHWAY 431 NORTH</LocationAddressText>
                <LocalityName>ABBEVILLE</LocalityName>
                <LocationAddressStateCode>AL</LocationAddressStateCode>
            </FacilitySite>
----------------------------------------------------------------------------------------
    <FacilitySite registryId="110000369084">
            <FacilitySiteName>REMBRANDT FOODS- ABBEVILLE</FacilitySiteName>
            <LocationAddressText>496 INDUSTRIAL PARK RD</LocationAddressText>
            <LocalityName>ABBEVILLE</LocalityName>
            <LocationAddressStateCode>AL</LocationAddressStateCode>
            <LocationZIPCode>36310</LocationZIPCode>
        </FacilitySite>
----------------------------------------------------------------------------------------
<FacilitySite registryId="110055437718">
            <FacilitySiteName>RITE AID #7092</FacilitySiteName>
            <LocationAddressText>514 KIRKLAND STREET</LocationAddressText>
            <LocalityName>ABBEVILLE</LocalityName>
            <LocationAddressStateCode>AL</LocationAddressStateCode>
            <LocationZIPCode>36310-2700</LocationZIPCode>
            <LatitudeMeasure>31.56149</LatitudeMeasure>
        </FacilitySite>

Please let me know how could i do it. I tried rex, but i do not think that can give multiple rows out of one.

Re: How to split one Line into multiple lines while search

sundareshr — Thu, 31 Dec 2015 20:07:05 GMT

See if this get you going

| xmlkv | spath output=s path=Document.FacilitySite | table s | eval y=mvindex(s, 2) | mvexpand s | table s, y

In the above query, s has all instances of the node, mvexpand breaks them out into separate rows. mvindex(s, 2) gets the 3rd instance.

For more info...

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/spath

http://docs.splunk.com/Documentation/Splunk/6.2.0/Search/Parsemultivaluefields

Re: How to split one Line into multiple lines while search

sdaruna — Fri, 01 Jan 2016 00:29:35 GMT

Hi,

but, the attribute registry id is not coming. Is it coming for your search.? How could you get that.?

Re: How to split one Line into multiple lines while search

sundareshr — Fri, 01 Jan 2016 14:40:06 GMT

| spath output=r path=Document.FacilitySite{@registryId} will give you registryId.`

Re: How to split one Line into multiple lines while search

sdaruna — Fri, 01 Jan 2016 15:09:41 GMT

NO, sundaresh, I meant, i want both node and the registry id. How can i get two outputs from spath at the same time. >

Re: How to split one Line into multiple lines while search

sundareshr — Fri, 01 Jan 2016 15:33:17 GMT

| spath output=s path=Document.FacilitySite | spath output=r path=Document.FacilitySite{@registryId}

Maybe if you explain what you are trying to accomplish, end goal, I can try and give you a better answer.

Re: How to split one Line into multiple lines while search

sdaruna — Fri, 01 Jan 2016 20:19:40 GMT

Hi Sundaresh,

I would really thank you for your patience and help. The below is the format like which i am expecting output with registry id and node.

I could not paste it properly in the comment, so i have added it in question it self. Please see the latest edit in question.

Re: How to split one Line into multiple lines while search

sdaruna — Fri, 01 Jan 2016 20:34:22 GMT

For each node, i need nodedata and registry id. I tried using two xpaths, but they are coming out as two different groups.

Re: How to split one Line into multiple lines while search

sundareshr — Fri, 01 Jan 2016 23:30:10 GMT

Now I get it. Try this... Basically, I pull all the nodes into variable, then combine the variables so they a single row for all nodedata. Then split them using the separator and finally, get the appropriate values from the array. Let me know if the below query doesn't work for you.

| spath | rename Document.FacilitySite{@registryId} as r | rename Document.FacilitySite.FacilitySiteName as s | rename Document.FacilitySite.LocationAddressText as a | rename Document.FacilitySite.LocalityName as l | rename Document.FacilitySite.LocationAddressStateCode as c |  eval z=mvzip(r, s, "@@") | eval z=mvzip(z, a, "@@") | eval z=mvzip(z, l, "@@") | eval z=mvzip(z, c, "@@") | mvexpand z | eval site=split(z, "@@") | eval regid=mvindex(site, 0) | eval sitename=mvindex(site, 1) | eval add=mvindex(site, 2) | eval local=mvindex(site, 3) | eval state=mvindex(site, 4) | eval zip=mvindex(site, 5) | table regid, sitename, add, local, state