I am using awk command but unable to print IPs in in column using shell script

Question

I have a file "LM1" in below format and I wanted these to be printed in separated column like this below example

I have used below awk command to achieve this but I cannot extract IP4 field in csv file rest of the data is coming in columns.

I execute below command to achieve my requirements

I have a file "LM1" in below format and I wanted these to be printed in separated column like this below example

awk -F": " -v OFS="\t" '
BEGIN {print "CustName", "OS", "LM_Name", "Name", "IPv4", "Status" }
{
gsub(/"/,"")
sub(/^[[:blank:]]+/,"")
}
$1 == "customer_name" {
if ("customer_name" in data && !have_data)
print data["customer_name"]
have_data = 0
}
{
data[$1] = $2
}
("os_type" in data) && ("local_hostname" in data) && ("name" in data) && ("local_ipv4" in data) && ("status" in data) {
print data["customer_name"], data["os_type"], data["local_hostname"], data["name"], data["local_ipv4"], data["status"]
delete data["os_type"]
delete data["local_hostname"]
delete data["name"]
delete data["local_ipv4"]
delete data["status"]
have_data = 1
}
' LM1 | column -s $'\t' -t > LM-Status-Report.csv

Expected (Output) data format:

Column A   Column B         Column C              Column D
==========================================================
Customer   Local_Hostname   IP4 Addresse          Status
==========================================================
ABC        ABC-log-01       10.9.9.9,10.9.3.4     OK
ABC        ABC-log-02       10.8.8.8              New
XYZ        XYZ-log-01       10.10.2.2,10.2.4.6    Ok
XYZ        XYZ-log-02       10.2.3.4              New

RAW Data existing (input) format:

customer_name: "ABC"
{
  "syslog": {
    "created": {
      "at": 1478195183
      "by": 0
    }
    "id": "886707D0-4069-1005-8535-0050568525D9"
    "metadata": {
      "local_hostname": "ABC-log-01"
      "local_ipv4": [
        "10.9.9.9"
        "10.9.3.4"
      ]
      "public_ipv4": [
        "127.0.0.1"
        "10.1.1.1"
      ]
      "total_mem_mb": 3884
    }
    "modified": {
      "at": 1478195247
      "by": 0
    }
    "name": "ABC-log-01"
    "policy_id": "9125663A-04EA-4F1D-A436-ADFEF069D4BA"
    "stats": {
      "last_day_bytes": 0
      "last_update_ts": 0
    }
    "status": {
      "details": []
      "status": "ok"
    }
  }
}
{
  "eventlog": {
    "created": {
      "at": 1499888362
      "by": 0
    }
    "id": "A4D2EA92-5423-1005-B1B3-0050568505BC"
    "metadata": {
      "os_type": "windows"
      "local_hostname": "ABC-log-02"
      "local_ipv4": [
        "10.8.8.8"
      ]
      "num_logical_processors": 2
      "os_details": "Windows Server (R) 2008 Standard; 6.0.6002; Service Pack 2; x86"
      "public_ipv4": [
        "10.1.1.1"
      ]
      "public_ipv4": [
        "10.4.4.4"
      ]
      "total_mem_mb": 2046
    }
    "modified": {
      "at": 1512582221
      "by": 13939
    }
    "name": "ABC-log-03"
    "stats": {
      "last_day_bytes": 0
    }
    "status": {
      "details": []
      "status": "new"
      "timestamp": 1508228598
      "updated": 1508228598
    }
    "tags": []
  }
}
customer_name: "XYZ"
{
  "syslog": {
    "created": {
      "at": 1507196910
      "by": 0
    }
    "id": "9E47B629-5AC9-1005-B1B3-0050568505BC"
    "metadata": {
      "host_type": "standalone"
      "os_type": "unix"
      "version": "1"
      "local_hostname": "XYZ-log-01"
      "local_ipv6": [
        "10.10.1.2"
        "10.10.2.3"
      ]
      "num_logical_processors": 4
      "os_details": "Linux; 2.6.32-696.6.3.el6.x86_64; #1 SMP Wed Jul 12 14:17:22 UTC 2017; x86_64"
      "public_ipv4": [
        "10.10.2.2"
        "10.2.4.6"
      ]
      "total_mem_mb": 3951
    }
    "modified": {
      "at": 1507196910
      "by": 0
    }
    "name": "XYZ-log-01"
    "policy_id": "7135663A-04EA-4F1D-A436-ADFEF069D4BA"
    "stats": {
      "last_day_bytes": 0
      "last_update_ts": 0
    }
    "status": {
      "details": []
      "status": "ok"
      "timestamp": 1508228712
      "updated": 1519723490
    }
    "tags": []
  }
}
{
  "eventlog": {
    "created": {
      "at": 1507196961
      "by": 0
    }
    "id": "2F417043-5AC9-1005-B1B3-0050568505BC"
    "metadata": {
      "host_type": "standalone"
      "os_type": "windows"
      "version": "1"
      "local_hostname": "XYZ-log-02"
      "local_ipv4": [
        "10.2.3.4"
      ]
      "num_logical_processors": 2
      "os_details": "Windows Server (R) 2008 Standard; 6.0.6002; Service Pack 2; x86"
      "public_ipv4": [
        "10.2.3.4"
      ]
      "public_ipv6": [
        "*.*.*.*"
      ]
      "total_mem_mb": 2046
    }
    "modified": {
      "at": 1507196961
      "by": 0
    }
    "name": "XYZ-log-02"
    "stats": {
      "last_day_bytes": 0
      "last_update_ts": 0
    }
    "status": {
      "details": []
      "status": "new"
      "timestamp": 1508228722
      "updated": 1508228722
    }
    "tags": []
  }
}

Your input file looks like JSON, and you'd likely be much better off utilizing a JSON specific parser to extract the data you need, then formatting that, rather than trying to do it all via `awk`, as your solution would likely be brittle. This question - https://stackoverflow.com/questions/3858671/unix-command-line-json-parser - has many options for doing so. — zzevannn, Feb 27 '18 at 22:14
I have an API and first I run cURL command and parse data using jq then I get the raw format data and from there I need to extract Name, IP, Customer field. — Majeed, Feb 28 '18 at 07:41

Andriy Makukha · Accepted Answer · 2018-02-28T08:50:39.410

All you have to do to print IPs is to replace this block:

{
data[$1] = $2
}

with this one:

{
    if ($2 == "[")
        list = $1
    else if (list && $1 =="]")
        list = 0
    else if (list)
        data[list] = data[list] "," $1
    else
        data[$1] = $2
}

This will parse all the JSON lists in your data into single strings, in which all list elements will be joined by comma. So this will work not only for local_ipv4, but also for public_ipv6 and others.

And then you just need to get rid of an extra comma. So instead of:

print data["customer_name"], data["os_type"], data["local_hostname"], data["name"], data["local_ipv4"], data["status"]

use substr(data["local_ipv4"],2) function to remove the comma in front:

print data["customer_name"], data["os_type"], data["local_hostname"], data["name"], substr(data["local_ipv4"],2), data["status"]

At least, this is a simple way of doing it based on your code.

Thank you Andriy, it works for me. Appreciate your quick response. — Majeed, Feb 28 '18 at 19:15

I am using awk command but unable to print IPs in in column using shell script

1 Answers1