topic Re: json output read single value when there are multiple for a segment in Getting Data In

json output read single value when there are multiple for a segment

surekhasplunk — Mon, 28 Oct 2019 10:25:52 GMT

Hi,
I have a json output which is getting indexed correctly.
And i am collectng ip from remotemanagement{}.ip . But for some cases i have multiple ips under remotemanagement. In those cases i need to select only that one ip where protocol.name is NOT console. If there are 3 ips and for one the protocol.name is console then leave it and out of the rest 2 take any one ip.
As you can see from the screen shot one has protocol.name = console and for the third one protocol.name = ssh
So here will need to eval ssh_ip=192.0.32.38

And then use it in my below query to filter only those records.

index="unicorn" ( "infrastructure{}.type"=critical OR "infrastructure{}.type"=vital ) |mvexpand infrastructure{}.name |rename assetId as "AssetID" infrastructure{}.name as "Infrastrucure Name" name as Nom remoteManagement{}.ip as Ip realm{}.name as Type | table "Infrastrucure Name" "AssetID" Nom Ip Type |mvexpand Ip | where Ip=ssh_ip

how to calculate ssh_ip here ? as i tried to use
| spath "remoteManagement{}.protocol.name" | search "remoteManagement{}.protocol.name"!=console
OR
| spath "remoteManagement{}.protocol.name" | search "remoteManagement{}.protocol.name"=ssh

But its giving all the 3 ips.

Please help.

Thanks

Re: json output read single value when there are multiple for a segment

aberkow — Mon, 28 Oct 2019 18:06:52 GMT

I don't see a screenshot - can you give a sanitized version of the result up to where you're happy with the output to that point? (i.e. what is the result before you're trying your spath command)

Re: json output read single value when there are multiple for a segment

woodcock — Mon, 28 Oct 2019 23:30:48 GMT

Show us entire sample events and a mockup of the desired output.

Re: json output read single value when there are multiple for a segment

woodcock — Tue, 29 Oct 2019 01:09:42 GMT

And by show I DO NOT mean a picture; send us plain text.

Re: json output read single value when there are multiple for a segment

surekhasplunk — Tue, 29 Oct 2019 05:31:42 GMT

hi @aberkow and @woodcock ,

I am so sorry for the inconvenience, hope you can see the images now.

Re: json output read single value when there are multiple for a segment

surekhasplunk — Wed, 30 Oct 2019 11:43:16 GMT

Hi @woodcock and @aberkow,

Could you please help me here. As i have uploaded the images now.

Thanks in advance.

Re: json output read single value when there are multiple for a segment

woodcock — Wed, 30 Oct 2019 13:30:53 GMT

Did you notice where I said NOT to use images? Post TEXT.

Re: json output read single value when there are multiple for a segment

surekhasplunk — Thu, 31 Oct 2019 08:47:47 GMT

Hi @woodcock,

Below is what i am receiving under remoteManagement which i am evaluating for Ip.
Now my requirement is i need to get only the ip where protocol.name=ssh

 remoteManagement:  [   [-] 
    {   [-] 
     additionalInformation:  null   
     device:     7490   
     id:     18450  
     ip:     184.7.138.72   
     login:  HASDf  
     password:   null   
     plainTextURL:   null   
     port:   7013   
     protocol:  {   [-] 
         name:   console    
    }   
    }   
    {   [-] 
     additionalInformation:  null   
     device:     7490   
     id:     18451  
     ip:     192.0.32.38    
     login:  matricule SG   
     password:   null   
     plainTextURL:   null   
     port:   443    
     protocol:  {   [-] 
         name:   https  
    }   
    }   
    {   [-] 
     additionalInformation:  null   
     device:     7490   
     id:     18449  
     ip:     192.0.32.38    
     login:  matricule SG   
     password:   null   
     plainTextURL:   null   
     port:   22 
     protocol:  {   [-] 
         name:   ssh    
    }   
    }   
]

Re: json output read single value when there are multiple for a segment

kamlesh_vaghela — Thu, 31 Oct 2019 08:50:01 GMT

Hello @surekhasplunk

Kindly post _raw event.

Re: json output read single value when there are multiple for a segment

surekhasplunk — Wed, 30 Sep 2020 02:48:11 GMT

For example:
Below is my query and i know for this asset id i have 3 values under remoteManagement{}.ip

index="unicorn"| spath assetId | search assetId=MA9624121 |lookup Input_splunk_all.csv RTR as name |spath output=manage remoteManagement{} | table name manage

Below is the output.

name    manage
HFSOFW401   
{"id":18450,"protocol":{"name":"console"},"ip":"184.7.138.72","port":"7013","additionalInformation":null,"plainTextURL":null,"login":"HFSOFW401","password":null,"device":7490}
{"id":18451,"protocol":{"name":"https"},"ip":"192.0.32.38","port":"443","additionalInformation":null,"plainTextURL":null,"login":"matricule SG","password":null,"device":7490}
{"id":18449,"protocol":{"name":"ssh"},"ip":"192.0.32.38","port":"22","additionalInformation":null,"plainTextURL":null,"login":"matricule SG","password":null,"device":7490}

Re: json output read single value when there are multiple for a segment

woodcock — Sat, 09 Nov 2019 21:41:52 GMT

I think that I finally get it. Try adding this to drop the console values from the multivalued manage field:

... | eval manage = mvfilter(NOT match(manage, "\"name\":\"console\""))

Re: json output read single value when there are multiple for a segment

to4kawa — Sat, 11 Jan 2020 00:48:43 GMT

index="unicorn"
| spath assetId 
| search assetId=MA9624121 
| lookup Input_splunk_all.csv RTR as name 
| spath output=manage remoteManagement{} 
| table name manage
| stats values(name) as name by manage
| spath input=manage

Hi, @surekhasplunk
How about this?