Convert lscpu output to row based format in excel/csv table format

Question

I have the below data (lscpu output) for 1000 servers and I want the same in excel but in row format and not as in command output in columns

1.2.3.4      CHANGED
Architecture:                    x86_64
CPU op-mode(s):                  32-bit, 64-bit
Byte Order:                      Little Endian
Address sizes:                   39 bits physical, 48 bits virtual
CPU(s):                          8
On-line CPU(s) list:             0-7
Thread(s) per core:              2
Core(s) per socket:              4
Socket(s):                       1
NUMA node(s):                    1
Vendor ID:                       GenuineIntel
CPU family:                      6
Model:                           140
Model name:                      11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
Stepping:                        1
CPU MHz:                         2800.000
CPU max MHz:                     4700.0000
CPU min MHz:                     400.0000
BogoMIPS:                        5606.40
Virtualization:                  VT-x
L1d cache:                       192 KiB
L1i cache:                       128 KiB
L2 cache:                        5 MiB
L3 cache:                        12 MiB
5.6.7.8      CHANGED 
Architecture:                    x86_64
CPU op-mode(s):                  32-bit, 64-bit
Byte Order:                      Little Endian
Address sizes:                   39 bits physical, 48 bits virtual
CPU(s):                          8
On-line CPU(s) list:             0-7
Thread(s) per core:              2
Core(s) per socket:              4
Socket(s):                       1
NUMA node(s):                    1
Vendor ID:                       GenuineIntel
CPU family:                      6
Model:                           140
Model name:                      11th Gen Intel(R) Core(TM) i7-1165G7 @ 2.80GHz
Stepping:                        1
CPU MHz:                         2800.000
CPU max MHz:                     4700.0000
CPU min MHz:                     400.0000
BogoMIPS:                        5606.40
Virtualization:                  VT-x
L1d cache:                       192 KiB
L1i cache:                       128 KiB
L2 cache:                        5 MiB
L3 cache:                        12 MiB

I want this in excel for multiple servers

IP Architecture CPU op-mode(s) ... ...L3 cache
data
data
data

Mircea Vutcovici · Answer 1 · 2023-06-24T16:35:25.503

You can print the output of lscpu in JSON format, then select what you need with jq:

[user@laptop ~]$ lscpu -J|jq .lscpu[0]
{
  "field": "Architecture:",
  "data": "x86_64"
}
[user@laptop ~]$

Or in CSV format:

[user@laptop ~]$ lscpu --parse --output-all
# The following is the parsable format, which can be fed to other
# programs. Each different item in every column has an unique ID
# starting usually from zero.
# BOGOMIPS,CPU,Core,Socket,Cluster,Node,Book,DRAWER,L1d:L1i:L2:L3,Polarization,Address,Configured,Online,Mhz,SCALMHZ%,Maxmhz,Minmhz
5188.21,0,0,0,,0,,,0:0:0:0,U,,,Y,1809.9220,57%,3200.0000,500.0000
5188.21,1,0,0,,0,,,0:0:0:0,U,,,Y,797.6560,25%,3200.0000,500.0000
5188.21,2,1,0,,0,,,1:1:1:0,U,,,Y,880.4450,28%,3200.0000,500.0000
5188.21,3,1,0,,0,,,1:1:1:0,U,,,Y,2095.2959,65%,3200.0000,500.0000
[user@laptop ~]$

Another aproach is to use ansible:

[user@laptop ~]$ ansible -i localhost, -m setup all|sed 's/localhost | SUCCESS => //'|jq .ansible_facts.ansible_processor
[
  "0",
  "GenuineIntel",
  "Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz",
  "1",
  "GenuineIntel",
  "Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz",
  "2",
  "GenuineIntel",
  "Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz",
  "3",
  "GenuineIntel",
  "Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz"
]
[user@laptop ~]$

3rd aproach would be to parse the info from /proc/cpuinfo:

[user@laptop ~]$ grep 'model name' /proc/cpuinfo 
model name  : Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz
model name  : Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz
model name  : Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz
model name  : Intel(R) Core(TM) i7-5600U CPU @ 2.60GHz
[user@laptop ~]$

Vladimir Botka · Answer 2 · 2023-06-24T23:36:08.070

Use JSON format and register the data to the variable lscpu_out

    - command: lscpu -J
      register: lscpu_out

Extract all variables lscpu_out, get the attribute stdout, convert the strings to YAML, and get the attribute lscpu

  lscpu_data: "{{ ansible_play_hosts|
                  map('extract', hostvars, ['lscpu_out', 'stdout'])|
                  map('from_yaml')|
                  map(attribute='lscpu') }}"

Get the list of the keys(field)

  lscpu_keys: "{{ lscpu_data.0|map(attribute='field') }}"

and get the lists of the values(data)

  lscpu_vals: "{{ lscpu_data|map('map', attribute='data') }}"

Write the data to a file on the controller. Fit the format of the content to your needs

    - copy:
        dest: /tmp/lscpu.csv
        content: |
          "IP" "{{ lscpu_keys|join('" "') }}"
          {% for s,v in ansible_play_hosts|zip(lscpu_vals) %}
          "{{ s }}" "{{ v|join('" "') }}"
          {% endfor %}
      run_once: true
      delegate_to: localhost

gives (abridged)

^{shell> cat /tmp/lscpu.csv
"IP" "Architecture:" "CPU op-mode(s):" "Byte Order:" "Address sizes:" ...
"srv1" "x86_64" "32-bit, 64-bit" "Little Endian" "39 bits physical, 48 bits virtual" ...
"srv2" "x86_64" "32-bit, 64-bit" "Little Endian" "16" ...

Example of a complete playbook for testing
- hosts: all

vars:

lscpu_data: "{{ ansible_play_hosts|
map('extract', hostvars, ['lscpu_out', 'stdout'])|
map('from_yaml')|
map(attribute='lscpu') }}"
lscpu_keys: "{{ lscpu_data.0|map(attribute='field') }}"
lscpu_vals: "{{ lscpu_data|map('map', attribute='data') }}"

tasks:

- command: lscpu -J
register: lscpu_out

- copy:
dest: /tmp/lscpu.csv
content: |
"IP" "{{ lscpu_keys|join('" "') }}"
{% for s,v in ansible_play_hosts|zip(lscpu_vals) %}
"{{ s }}" "{{ v|join('" "') }}"
{% endfor %}
run_once: true
delegate_to: localhost}

Convert lscpu output to row based format in excel/csv table format

2 Answers2