Can you help me create the SPL to extract multiple event fields values to a single table column?

[oshirnin]

Hello!

I'm a newbie to Splunk, I need some help with my SPL. I have my data coming from scripted inputs — actually my events are configuration data for VMs. Here is what I have in my events, for example:

1st event:

{
       name: VM1
       virtualDisk:scsi0:0_configuredGB: 60 
       virtualDisk:scsi0:0_fileName: [DS0] VM1/VM1_file0.vmdk   
       virtualDisk:scsi0:1_configuredGB: 3  
       virtualDisk:scsi0:1_fileName: [DS1] VM1/VM1_file1.vmdk   
       virtualDisk:scsi0:2_configuredGB: 16 
       virtualDisk:scsi0:2_fileName: [DS2] VM1/VM1_file2.vmdk   
       virtualDisk:scsi1:0_configuredGB: 200    
       virtualDisk:scsi1:0_fileName: [DS3] VM1/VM1_file3.vmdk   
}

2nd event

{
       name: VM2
       virtualDisk:scsi0:0_configuredGB: 20 
       virtualDisk:scsi0:0_fileName: [DS0] VM2/vm_f0.vmdk   
       virtualDisk:scsi0:1_configuredGB: 15 
       virtualDisk:scsi0:1_fileName: [DS2] VM2/vm_f1.vmdk   
}

.. etc for all the VMs in my environment.

What I want to get is the following table to make future analytics on my datastore utilization:

{
    VMName        | fileName                          | fileSize
    ----------------------------------------------------------------------------------------------------
    VM1           | [DS0] VM1/VM1_file0.vmdk          | 60
    VM1           | [DS1] VM1/VM1_file1.vmdk          | 3
    VM1           | [DS2] VM1/VM1_file2.vmdk          | 16
    VM1           | [DS3] VM1/VM1_file3.vmdk          | 200
    VM2           | [DS0] VM2/vm_f0.vmdk              | 20
    VM2           | [DS2] VM2/vm_f1.vmdk              | 15
    ----------------------------------------------------------------------------------------------------
}

So, my events all have one to unknown count of scsi*_fileName fields, matched count of scsi*_configuredGB fields, which values I want to extract matched to each other (file name to size) and show as fileName and fileSize columns in a new table along with VMName.

How can I search like this? Thanks in advance!

[harsmarvania57 · Accepted solution]

Hi @oshirnin,

Please try below query.

<yourBaseSearch>
| spath
| fillnull fileName, fileSize
| foreach virtualDisk:scsi*:* [ eval <<FIELD>> = if(isnull('<<FIELD>>'),0,'<<FIELD>>') ]
| foreach virtualDisk:scsi*:*_fileName [ eval fileName =mvzip(fileName, '<<FIELD>>') ]
| foreach virtualDisk:scsi*:*_configuredGB [ eval fileSize =mvzip(fileSize, '<<FIELD>>') ]
| makemv delim="," fileName
| makemv delim="," fileSize
| eval temp=mvzip(fileName,fileSize, ";")
| rex mode=sed field=temp "s/(?:\d\;)+\d//g"
| mvexpand temp
| where temp!=""
| eval fileName=mvindex(split(temp,";"),0), fileSize=mvindex(split(temp,";"),1)
| rename name as VMName
| table VMName, fileName,fileSize

Below is run anywhere search based on sample data you have provided.

| makeresults
| eval _raw="{
        \"name\": \"VM1\",
        \"virtualDisk:scsi0:0_configuredGB\": 60,
        \"virtualDisk:scsi0:0_fileName\": \"[DS0] VM1/VM1_file0.vmdk\",
        \"virtualDisk:scsi0:1_configuredGB\": 3,
        \"virtualDisk:scsi0:1_fileName\": \"[DS1] VM1/VM1_file1.vmdk\",
        \"virtualDisk:scsi0:2_configuredGB\": 16,
        \"virtualDisk:scsi0:2_fileName\": \"[DS2] VM1/VM1_file2.vmdk\",
        \"virtualDisk:scsi1:0_configuredGB\": 200,
        \"virtualDisk:scsi1:0_fileName\": \"[DS3] VM1/VM1_file3.vmdk\"
 }"
| append [ makeresults | eval _raw="{
        \"name\": \"VM2\",
        \"virtualDisk:scsi0:0_configuredGB\": 20,
        \"virtualDisk:scsi0:0_fileName\": \"[DS0] VM2/vm_f0.vmdk\",
        \"virtualDisk:scsi0:1_configuredGB\": 15,
        \"virtualDisk:scsi0:1_fileName\": \"[DS2] VM2/vm_f1.vmdk\"
 }"]
| spath 
| fillnull fileName, fileSize
| foreach virtualDisk:scsi*:* [ eval <<FIELD>> = if(isnull('<<FIELD>>'),0,'<<FIELD>>') ]
| foreach virtualDisk:scsi*:*_fileName [ eval fileName =mvzip(fileName, '<<FIELD>>') ]
| foreach virtualDisk:scsi*:*_configuredGB [ eval fileSize =mvzip(fileSize, '<<FIELD>>') ]
| makemv delim="," fileName
| makemv delim="," fileSize
| eval temp=mvzip(fileName,fileSize, ";")
| rex mode=sed field=temp "s/(?:\d\;)+\d//g"
| mvexpand temp
| where temp!=""
| eval fileName=mvindex(split(temp,";"),0), fileSize=mvindex(split(temp,";"),1)
| rename name as VMName
| table VMName, fileName,fileSize

[oshirnin]

Hello, @harsmarvania57, thank you for the answer.

I run your SPL line by line and understand the idea, it works like a charm. The only thing I want to ask you : does the foreach statement takes the fields guaranteed in alphabetical order? My virtualDisk:scsi*:_fileName and virtualDisk:scsi:*_configuredGB may not be aligned to each order in original event, so it may be for example:

{
        name: VM1
        virtualDisk:scsi0:1_configuredGB: 3    
        virtualDisk:scsi0:0_configuredGB: 60    
        virtualDisk:scsi0:2_fileName: [DS_C2] VM1/VM1_file2.vmdk    
        virtualDisk:scsi1:0_configuredGB: 200    
        virtualDisk:scsi0:1_fileName: [DS_D1] VM1/VM1_file1.vmdk    
        virtualDisk:scsi0:2_configuredGB: 16    
        virtualDisk:scsi1:0_fileName: [DS_A3] VM1/VM1_file3.vmdk    
        virtualDisk:scsi0:0_fileName: [DS_B0] VM1/VM1_file0.vmdk
 }

I tryed this data and the query still works.

[harsmarvania57]

It should work but you must have pair of data for example for disk virtualDisk:scsi0:1_fileName: [DS_D1] VM1/VM1_file1.vmdk there must be matching configuredGB like virtualDisk:scsi0:1_configuredGB: 3 otherwise this will not work or query will generate wrong result.

And if this answers solve your query then please accept this answer so that in future other user will able to see this as reference for same type of query.

[oshirnin]

@harsmarvania57 thanks, yes I always have matches, I control this while pushing to Splunk from scripted input.