Convert Columns in Rows delimited by a strings

Shell Programming and Scripting
1

Hi Gurus,
I have a file that contain inventory information from someones computers:

UserName
domain\user1
DNSHostName
machine1
Caption
Microsoft Windows 7 Professional
OSArchitecture
64 bits
SerialNumber
XXX
Name
HP EliteBook Revolve 810 G1
NumberOfProcessors
1
Name
Intel(R) Core(TM) i5-3437U CPU @ 1.90GHz
TotalVisibleMemorySize
12443116
IPAddress
{"1.2.4.7", "fe80::e967:1d63:ebfa:8fa1"}
Description
Microsoft Office Professional Plus 2010
Microsoft Office OneNote MUI (Spanish) 2010
Microsoft Office Office 32-bit Components 2010
Microsoft Office Shared 32-bit MUI (Spanish) 2010
Microsoft Office InfoPath MUI (Spanish) 2010
Microsoft Office Access MUI (Spanish) 2010
Microsoft Office Excel MUI (Spanish) 2010
Microsoft Office PowerPoint MUI (Spanish) 2010
UserName
DNSHostName
machine2
Caption
Microsoft Windows Server 2008 R2 Enterprise
OSArchitecture
64-bit
SerialNumber
VMware-42 39 f1 5b 54 c0 fd 4d-eb 1e 23 4d bb a4 92 8c
Name
VMware Virtual Platform
NumberOfProcessors
4
Name
Intel(R) Xeon(R) CPU           X5670  @ 2.93GHz
Intel(R) Xeon(R) CPU           X5670  @ 2.93GHz
Intel(R) Xeon(R) CPU           X5670  @ 2.93GHz
Intel(R) Xeon(R) CPU           X5670  @ 2.93GHz
TotalVisibleMemorySize
8388152
IPAddress
{"1.2.4.8"}
Description
Tripwire Enterprise Agent
Microsoft Visual C++ 2005 Redistributable (x64)
Microsoft Visual C++ 2005 Redistributable
UserName
domain\user3
DNSHostName
machine3
Caption
Microsoft Windows 7 Professional
OSArchitecture
64 bits
SerialNumber
xxx
Name
HP EliteBook Revolve 810 G1
NumberOfProcessors
1
Name
Intel(R) Core(TM) i5-3437U CPU @ 1.90GHz
TotalVisibleMemorySize
12443116
IPAddress
{"1.2.4.9", "fe80::69ff:c1f1:1b19:38c7"}
{"192.1.1.40", "fe80::58b5:64a4:6582:6402"}
Description
Microsoft Lync Web App Plug-in
Microsoft Office Professional Plus 2010
Microsoft Office OneNote MUI (Spanish) 2010
Microsoft Office Office 32-bit Components 2010
Microsoft Office Shared 32-bit MUI (Spanish) 2010

I search something like this:

1.- in some case the UserName is not captured when this happens, the field should be left blank.

2.- for each machine, there are many programs installed, I need for each program generate new line containing the name of the program and machine(hostname).

3.- If any field not get information, need to insert blank.

4.- Field Separator needed is pipe character.

I search something like this:

UserName|DNSHostName|Caption|OSArchitecture|SerialNumber|Name|NumberOfProcessors|Name|TotalVisibleMemorySize|IPAddress|Description
domain\user1|machine1|Microsoft Windows 7 Professional	64 bits|XXX|HP EliteBook Revolve 810 G1	1|Intel(R) Core(TM) i5-3437U CPU @ 1.90GHz|12443116|1.2.4.7|Microsoft Office Professional Plus 2010
 |machine1| | | | | | | | |Microsoft Office OneNote MUI (Spanish) 2010
 |machine1| | | | | | | | |Microsoft Office Office 32-bit Components 2010
 |machine1| | | | | | | | |Microsoft Office Shared 32-bit MUI (Spanish) 2010
 |machine1| | | | | | | | |Microsoft Office Access MUI (Spanish) 2010
 |machine1| | | | | | | | |Microsoft Office Excel MUI (Spanish) 2010
 |machine1| | | | | | | | |Microsoft Office PowerPoint MUI (Spanish) 2010
 |machine1| | | | | | | | |Microsoft Office OneNote MUI (Spanish) 2010
 |machine1| | | | | | | | |Microsoft Office OneNote MUI (Spanish) 2010
 |machine2|Microsoft Windows Server 2008 R2 Enterprise|64-bit|VMware-42 39 f1 5b|VMware Virtual Platform|4|Intel(R) Xeon(R) CPU X5670  @ 2.93GHz,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670,Intel(R) Xeon(R) CPU X5670|8388152|1.2.4.8|Tripwire Enterprise Agent
 |machine2| | | | | | | | |Microsoft Visual C++ 2005 Redistributable (x64)
 |machine2| | | | | | | | |Microsoft Visual C++ 2005 Redistributable
domain\user3|machine3|Microsoft Windows 7 Professional|64 bits|xxx|HP EliteBook Revolve 810 G1|1|Intel(R) Core(TM) i5-3437U CPU @ 1.90GHz|12443116|1.2.4.9|Microsoft Lync Web App Plug-in
 |machine3| | | | | | | | |Microsoft Office Professional Plus 2010
 |machine3| | | | | | | | |Microsoft Office OneNote MUI (Spanish) 2010
 |machine3| | | | | | | | |Microsoft Office Professional Plus 2010
 |machine3| | | | | | | | |Microsoft Office Professional Plus 2010

Is it possible to achieve this with awk?

I much appreciate any help

Regards MG.

2

Here is something I wrote based on your input data:

awk '
        BEGIN {
         print "UserName|DNSHostName|Caption|OSArchitecture|SerialNumber|NumberOfProcessors|TotalVisibleMemorySize|IPAddress|Name|Name|Description"
         n = split ( "UserName:DNSHostName:Caption:OSArchitecture:SerialNumber:NumberOfProcessors:TotalVisibleMemorySize:IPAddress", V, ":" )
         while ( ++k <= n )
               H[V[k]]
        }
        /UserName/ {
                f = 0
                c = 0
                split ( "", R )
        }
        function getval(par, str)
        {
                if ( par == "IPAddress" )
                        gsub ( /\{\"|\".*/, X, str )
                return str
        }
        /^Name/ {
                getline
                N[++c] = $0
                next
        }
        $1 in H {
                i = $1
                getline
                if ( !( $1 in H ) )
                {
                        if ( $1 != "Name" )
                                R = getval( i, $0 )
                }
                else
                {
                        i = $1
                        getline
                        if ( $1 != "Name" )
                                R = getval( i, $0 )
                }
                next
        }
        /Description/ {
                f = 1
                next
        }
        f && !/Description/ {
                for ( j = 1; j <= n; j++ )
                        printf "%s|", R[V[j]]
                printf "%s|%s|", N[1], N[2]
                printf "%s\n", $0
        }
' OFS='|' file

Feel free to make changes as per your requirement.

3

Yoda Wuaaau many thanks. Your code turned out successful for me.
After will post my own script.

Regards M.G.