Splunk Search

How to split one Line into multiple lines while search

sdaruna
Explorer

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.

Tags (1)
0 Karma
1 Solution

sundareshr
Legend

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

View solution in original post

sundareshr
Legend

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

sdaruna
Explorer

Hi,

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

0 Karma

sundareshr
Legend

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

0 Karma

sdaruna
Explorer

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

0 Karma

sundareshr
Legend

| 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.

0 Karma

sdaruna
Explorer

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

0 Karma

sundareshr
Legend

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
0 Karma

sdaruna
Explorer

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.

0 Karma
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 ...