Cant parse .log to json

erdo · January 31, 2019, 12:20pm

My log file

2019-01-18 14:03:07,666 - Request - ..................... - http://......................................................................................... - getOpenInvoices - 
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="http://....................................................................." xmlns:types="http://............................................................................................" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
        <tns:getOpenInvoices>
            <invoiceQueryOpenRequest href="#id1" />
        </tns:getOpenInvoices>
        <q1:InvoiceQueryOpenRequest id="id1" xsi:type="q1:InvoiceQueryOpenRequest" xmlns:q1="java:com.....................collgw.model.invoice">
            <bankId xsi:type="xsd:int">23</bankId>
            <compId xsi:type="xsd:int">533</compId>
            <curr xsi:type="xsd:string">949</curr>
            <custId xsi:nil="true" />
            <invCount xsi:type="xsd:int">5</invCount>
            <msgDate xsi:nil="true" />
            <msisdn xsi:type="xsd:long">12345678</msisdn>
            <orig xsi:nil="true" />
            <period xsi:type="xsd:string">201901</period>
            <procDate xsi:nil="true" />
            <procTime xsi:nil="true" />
            <sessionId xsi:type="xsd:string">.........................</sessionId>
            <stan xsi:type="xsd:long">0</stan>
        </q1:InvoiceQueryOpenRequest>
    </soap:Body>
</soap:Envelope>

My config...
input {
file {
path => "C:\Users............\Desktop\xml\20190118.log"
type => "test-xml"
start_position => "beginning"
codec => multiline {
pattern => "^"
negate => true
what => "previous"
}
}
}
filter {
grok{
match =>{
"message" =>"%{NUMBER:bankId} %{NUMBER:compId} "
}
}
xml {
store_xml => "false"
source => "data"
xpath => [
"/soap:Envelope/soap:Body/q1:InvoiceQueryOpenRequest/bankId/text()", "bankId",
"/soap:Envelope/soap:Body/q1:InvoiceQueryOpenRequest/compId/text()", "compId"
]
}
mutate {
rename => [
"[bankId][0]", "bankId",
"[compId][0]", "compId"
]
}
}

output {
file
{
path => "C:\Users............\Desktop\xml2\20190118.json"
}
}

Badger · January 31, 2019, 1:14pm

Please do not post images of text. Just post the text itself.

Badger · January 31, 2019, 1:45pm

Since the field names are unambiguous, you could do that using

        xpath => {
            "//bankId/text()" => "bankId"
            "//compId/text()" => "compId"
        }

I cannot get absolute paths to work either.

erdo · January 31, 2019, 2:37pm

I remove grok and also change xpath names. ["_grokparsefailure"] is gone but still all xml file is in "message"

Badger · January 31, 2019, 2:41pm

I would expect that. What problem are you having?

erdo · January 31, 2019, 2:47pm

Expecting something like this after parse

       {
          "soap:Envelope": {
            "-xmlns:soap": "http://schemas.xmlsoap.org/soap/envelope/",
            "-xmlns:soapenc": "http://schemas.xmlsoap.org/soap/encoding/",
            "-xmlns:tns": "http://........................................",
            "-xmlns:types": "http://.........................................................",
            "-xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
            "-xmlns:xsd": "http://www.w3.org/2001/XMLSchema",
            "soap:Body": {
              "-soap:encodingStyle": "http://schemas.xmlsoap.org/soap/encoding/",
              "tns:getOpenInvoices": {
                "invoiceQueryOpenRequest": { "-href": "#id1" }
              },
              "q1:InvoiceQueryOpenRequest": {
                "-xmlns:q1": "java:com.........................................",
                "-id": "id1",
                "-xsi:type": "q1:InvoiceQueryOpenRequest",
                "bankId": {
                  "-xsi:type": "xsd:int",
                  "#text": "23"
                },
                "compId": {
                  "-xsi:type": "xsd:int",
                  "#text": "533"
                },
                "curr": {
                  "-xsi:type": "xsd:string",
                  "#text": "949"
                },
                "custId": { "-xsi:nil": "true" },
                "invCount": {
                  "-xsi:type": "xsd:int",
                  "#text": "5"
                },
                "msgDate": { "-xsi:nil": "true" },
                "msisdn": {
                  "-xsi:type": "xsd:long",
                  "#text": "1234567"
                },
                "orig": { "-xsi:nil": "true" },
                "period": {
                  "-xsi:type": "xsd:string",
                  "#text": "201901"
                },
                "procDate": { "-xsi:nil": "true" },
                "procTime": { "-xsi:nil": "true" },
                "sessionId": {
                  "-xsi:type": "xsd:string",
                  "#text": "..............................."
                },
                "stan": {
                  "-xsi:type": "xsd:long",
                  "#text": "0"
                }
              }
            }
          }
        }

Badger · January 31, 2019, 2:52pm

Try

xml {
    source => "data"
    target => "theXML"
    store_xml => true
}

erdo · January 31, 2019, 3:10pm

Facing same problem still whole xml file is in "message":"2019-01-18 16:05:54,016 - ........." only
"type":"test-xml" is changed

Thats my output

{  
"host":"CWMISL119",
"@version":"1",
"message":"2019-01-18 16:05:54,016 - Response - ............................. - http://..............................- getOpenInvoices - &lt;?xml version=\"1.0\" encoding=\"utf-8\"?&gt;&lt;soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:soapenc=\"http://schemas.xmlsoap.org/soap/encoding/\" xmlns:tns=\"http://............................................." xmlns:types=\"http://........................................................." xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"&gt;&lt;soap:Body soap:encodingStyle=\"http://schemas.xmlsoap.org/soap/encoding/\"&gt;&lt;tns:getOpenInvoices&gt;&lt;invoiceQueryOpenRequest href=\"#id1\" /&gt;&lt;/tns:getOpenInvoices&gt;&lt;q1:InvoiceQueryOpenRequest id=\"id1\" xsi:type=\"q1:InvoiceQueryOpenRequest\" xmlns:q1=\"java:com....................collgw.model.invoice\"&gt;&lt;bankId xsi:type=\"xsd:int\"&gt;23&lt;/bankId&gt;&lt;compId xsi:type=\"xsd:int\"&gt;533&lt;/compId&gt;&lt;curr xsi:type=\"xsd:string\"&gt;949&lt;/curr&gt;&lt;custId xsi:nil=\"true\" /&gt;&lt;invCount xsi:type=\"xsd:int\"&gt;5&lt;/invCount&gt;&lt;msgDate xsi:nil=\"true\" /&gt;&lt;msisdn xsi:type=\"xsd:long\"&gt;.............................&lt;/msisdn&gt;&lt;orig xsi:nil=\"true\" /&gt;&lt;period xsi:type=\"xsd:string\"&gt;201901&lt;/period&gt;&lt;procDate xsi:nil=\"true\" /&gt;&lt;procTime xsi:nil=\"true\" /&gt;&lt;sessionId xsi:type=\"xsd:string\"&gt;...............................;/sessionId&gt;&lt;stan xsi:type=\"xsd:long\"&gt;0&lt;/stan&gt;&lt;/q1:InvoiceQueryOpenRequest&gt;&lt;/soap:Body&gt;&lt;/soap:Envelope&gt;\r",
"@timestamp":"2019-01-31T15:05:54.688Z",
"path":"C:\\Users\\erdogan.alper\\Desktop\\xml\\20190118.log",
"type":"test-xml"
}

Badger · January 31, 2019, 3:52pm

Sorry, I misread your initial message. You need to strip off the first line of the message so that it just contains XML.

    mutate { gsub => [ "message", "^[^<]+<", "<" ] }
    xml {
        source => "message"
        target => "theXML"
        store_xml => true
    }

Then I get

    "theXML" => {
        "xmlns:xsd" => "http://www.w3.org/2001/XMLSchema",
       "xmlns:soap" => "http://schemas.xmlsoap.org/soap/envelope/",
    "xmlns:soapenc" => "http://schemas.xmlsoap.org/soap/encoding/",
        "xmlns:tns" => "http://.....................................................................",
      "xmlns:types" => "http://............................................................................................",
             "Body" => [
        [0] {
                    "getOpenInvoices" => [
                [0] {
                    "invoiceQueryOpenRequest" => [

erdo · February 1, 2019, 7:18am

It works quite well right know, now I try to things within the q1:InvoiceQueryOpenRequest. For example "bankId":"23" with using xpath

erdo · February 1, 2019, 7:23am

with this you gave i can able to take specific things but how to remove rest of the stuff witihn json file only want bankId and compId?

erdo · February 1, 2019, 7:43am

With using this I get rid of my unwanted fields

mutate {
      remove_field => ["message","theXML"]
    }

erdo · February 1, 2019, 1:05pm

Any Suggestion for taking only things within InvoiceQueryOpenRequest ?

with only 1 xpath

<q1:InvoiceQueryOpenRequest id="id1" xsi:type="q1:InvoiceQueryOpenRequest" xmlns:q1="java:com.turkcelltech.collgw.model.invoice">
            <bankId xsi:type="xsd:int">23</bankId>
            <compId xsi:type="xsd:int">533</compId>
            <curr xsi:type="xsd:string">949</curr>
            <custId xsi:nil="true"/>
            <invCount xsi:type="xsd:int">5</invCount>
            <msgDate xsi:nil="true"/>
            <msisdn xsi:type="xsd:long">1234567</msisdn>
            <orig xsi:nil="true"/>
            <period xsi:type="xsd:string">201901</period>
            <procDate xsi:nil="true"/>
            <procTime xsi:nil="true"/>
            <sessionId xsi:type="xsd:string">********************</sessionId>
            <stan xsi:type="xsd:long">0</stan>
        </q1:InvoiceQueryOpenRequest>

xpath =>{
"//q1:InvoiceQueryOpenRequest/*/text()" => "nvoiceQueryOpenRequest"
}

system · March 1, 2019, 1:05pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.