How to transfer the array to csv format

terrymu · March 27, 2018, 1:44am

Hi experts,

I have parsed the XML file and get some useful NODEs' information, they are saved as a array format. I want to draw a trend chart by using Kibana, but I can't draw it because all of data are saved as array format. Would you please help me solve this block issue?

The input XML files shows as below:

<?xml version="1.0" encoding="UTF-8"?>
<OMeS version="2.3">
  <PMSetup startTime="2018-03-09T08:15:00.000+01:00" interval="5">
    <PMMOResult>
      <MO dimension="network_element">
        <DN>NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-admintd-node-0/DBTYPE-cmdb</DN>
      </MO>
      <PMTarget measurementType="DBMEAS">
        <M704B1C1>32</M704B1C1>
        <M704B1C2>188</M704B1C2>
        <M704B1C3>0</M704B1C3>
        <M704B1C4>0</M704B1C4>
      </PMTarget>
    </PMMOResult>
    <PMMOResult>
      <MO dimension="network_element">
        <DN>NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-admintd-node-1/DBTYPE-cmdb</DN>
      </MO>
      <PMTarget measurementType="DBMEAS">
        <M704B1C1>30</M704B1C1>
        <M704B1C2>116</M704B1C2>
        <M704B1C3>0</M704B1C3>
        <M704B1C4>1</M704B1C4>                      
... ...(about 19 similar node)... ...

The paresd XML pattern shows as below:

                xpath => [
                        "/OMeS/PMSetup/@startTime", "audit_time",
                        "/OMeS/PMSetup/PMMOResult/MO/@dimension", "ELKNE",
                        "/OMeS/PMSetup/PMMOResult/MO/DN/text()", "ELKDN",
                        "/OMeS/PMSetup/PMMOResult/PMTarget/@measurementType", "ELKPMtype",
                        "/OMeS/PMSetup/PMMOResult/PMTarget/M704B1C1/text()", "ELKPV1",

]

The parsed output shows as below:

 "audit_time": [
      "2018-03-09T07:50:00.000+01:00"
    ],


"ELKPV1": [
      "32",
      "36",
      "33",
      "34",
      "36",
      "35",
      "34",
      "7",
      "40",
      "6",
      "32",
      "8",
      "33",
      "34",
      "34",
      "36",
      "33",
      "35",
      "33"
    ]



"ELKDN": [
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-admintd-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-admintd-node-1/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-amc-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-db-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-dtd-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-l4td-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-oam-node-1/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-oam-node-1/DBTYPE-gls-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-oam-node-2/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-oam-node-2/DBTYPE-gls-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-oam-node-3/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-oam-node-3/DBTYPE-gls-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-smd-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-spfe-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-storage-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-storage-node-1/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-storage-node-2/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-tafe-node-0/DBTYPE-cmdb",
      "NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-tafe-node-1/DBTYPE-cmdb"
    ]

Now I want to transfer them like a CSV format, such like below:

audit_time 	       ELKDN 	       ELKPV1
v1		        v1         	v1
v1		        v2	        v2
v1		        v3	        v3
v1		        v4	        v4
             ... ...

as we known, audit_time is only one value, other field includes 19 values, how can I transfer them to the above format?

Thank you.

wwalker · March 27, 2018, 4:42am

Oddly worded title, probably why you aren't getting a whole lot of attention. You also need to be a bit more open when sharing your configuration. Not asking for personal information, but sharing only a snippet of your configuration makes it very difficult to fully understand what is happening. For this particular problem, the issue is with your input section, not the XML section, something that I wouldn't have figured out if not for the linked thread we collaborated on earlier.

Here's the config I am working with that you posted on the previous thread:

input {
  file {
    path => ["/home/admin/log/NTASlog/*xml"]
    start_position => "beginning"
    type => "pmxmllog"
    sincedb_path => "/home/admin/log/NTASlog/.sincedb_file"
    codec => multiline {
      pattern => "<?xml version"
      #auto_flush_interval => 5
      #max_lines => 60000
      what => "previous"
      negate=> true
    }
  }
}
filter {
  if [type] == "pmxmllog" {
    xml {
      source => "message"
      target => "parsed"
      xpath => [
        "/OMeS/PMSetup/@startTime", "audit_time",
        "/OMeS/PMSetup/PMMOResult/MO/@dimension", "ELKNE",
        "/OMeS/PMSetup/PMMOResult/MO/DN/text()", "ELKDN",
        "/OMeS/PMSetup/PMMOResult/PMTarget/@measurementType", "ELKPMtype",
        "/OMeS/PMSetup/PMMOResult/PMTatget/M704B1C1/text()", "ELKPVo"
        #"/OMeS/PMSetup/PMMOResult/PMTatget[@measurementType]/M704B1C1/text()", "ELKPVoo",
        #"/OMeS/PMSetup/PMMOResult/PMTatget//M704B1C1/text()", "ELKPVooo"
        #"/OMeS/PMSetup/PMMOResult/PMTatget[1]/text()", "PV2"
        #"/OMeS/PMSetup/PMMOResult/PMTatget/M704B1C3[0]", "PV3",
        #"/OMeS/PMSetup/PMMOResult/PMTatget/M704B1C4[1]", "PV4"
      ]
    }
  }
  #mutate {
  #       split => [ "NE", "," ]
  #}
  #mutate {
  #        split => [ "DN", "," ]
  #}
  date {
    match => [ "audit_time", "YYYY-MM-DD HH:MM:SS.SSS" ]
#   target => "audit_time"
#   locale => "en_US"
    timezone =>  "UTC"
  }
}
output {
  elasticsearch {
    hosts => ["localhost:9200"]
  }
}

Logstash delineates events by lines. Your input's multline codec says, "If it doesn't start with <?xml version stick it on the previous line. Since this pattern is only seen once in an XML file, the entire file ends up on a single line and Logstash sees it as a single event. This is then passed onto the XML filter where it sees multiple values that match for each xpath and places them into an array for their respective fields.

That said, we need to determine how to send the data to logstash so that each event ends up on a single line. Using the example you provided previously:

<?xml version="1.0" encoding="UTF-8"?>
<OMeS version="2.3">
  <PMSetup startTime="2018-03-09T08:15:00.000+01:00" interval="5">
    <PMMOResult>
      <MO dimension="network_element">
        <DN>NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-admintd-node-0/DBTYPE-cmdb</DN>
      </MO>
      <PMTarget measurementType="DBMEAS">
        <M704B1C1>32</M704B1C1>
        <M704B1C2>188</M704B1C2>
        <M704B1C3>0</M704B1C3>
        <M704B1C4>0</M704B1C4>
      </PMTarget>
    </PMMOResult>
    <PMMOResult>
      <MO dimension="network_element">
        <DN>NTAS-tas01/HOSTNAME-cbam-4c63de7aeae2460289cd4197dc7-admintd-node-1/DBTYPE-cmdb</DN>
      </MO>
      <PMTarget measurementType="DBMEAS">
        <M704B1C1>30</M704B1C1>
        <M704B1C2>116</M704B1C2>
        <M704B1C3>0</M704B1C3>
        <M704B1C4>1</M704B1C4>
      </PMTarget>
    </PMMOResult>

It looks like you want to consider each PMMOResult as a separate event? If that's the case, the timestamp value would fall outside of the event and not be recorded with each event. That would require either another filter I am unaware of, not to say I know about all the different filters there are, or restructuring of the XML file before being ingested by Logstash. Assuming the timestamp isn't important, you would change your multiline pattern to "<PMMOResult>".

terrymu · March 27, 2018, 8:37am

Hi WWALKER,

Thanks a lot for your reply patiently, i want to use the parsed data to draw a trend picture by using kibana, such like below:

Every file will be an event, so I need set the multiline pattern to <?xml version. Now my major question is how can I format the exist array that can draw the trend picture like above red marked?

Thank you.

terrymu · March 27, 2018, 8:43am

I have a lot of XML file which has the same format, every file is included only one event. every file/event will be based on one time stamp.

such like this:
elk3

wwalker · March 27, 2018, 1:00pm

Unfortunately, I am not sure how you would do that. I had a similar issue with some data I was ingesting and had to build a PowerShell script to copy the required data into each event.

terrymu · March 30, 2018, 2:16am

wwalker,

I have met a new issue, can you help to check it? thank you.

terrymu · March 30, 2018, 2:18am

wwalker,

I have met a new issue related to convert data type, can you help to check it? thank you.

system · April 27, 2018, 2:18am

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

Topic		Replies	Views
Failed to convert the data type from string to float Logstash	4	5266	April 27, 2018
Parsing XML managing arrays and multilines Logstash	3	1705	July 6, 2017
Concat the XML array in logstash Logstash	1	491	January 13, 2017
Field imported as array Logstash	1	291	March 13, 2018
Kibana dashboard spacing between values in an array Kibana	3	472	January 10, 2022

How to transfer the array to csv format

Related topics