How to extract a specific data from raw data?

[smanojkumar]

I would like to extract a specific part of data from its raw data,

The data that is to be extracted is ID, Which is highlighted,

"aepassword": "kmdAkcu)n>Ec_.a(m5P7?8-n", "aeci": { "outgoing_server": "mailrv.aaa.com", "email_footer": "C:\\ProgramData\\bbb\\AutomationNote\\Email\\aa_Mail_Footer.png", "email_header": "C:\\ProgramData\\bbb\\AutomationNote\\Email\\aa_Mail_Header.png", "signature": "C:\\ProgramData\\bbb\\Automation\\Email\\bb_Email_Signature.txt", "requires_authentication": "false", "reply-to": "us@aaa.com", "primaryaddress": "ussdev@aaa.com", "host": "ussdev@bbb.com", "entity_alternate_names": "usdev@aaa.com", "outgoing_port": "2675", "entityid": "wmid-1607548215055521", "name": "bbb_MailBox", "entitytype": "Sub-System", "entitytype": "Workplace", "technology": "O736i85", "tenantid": 1000011, "cloudprovider": "", "satellite": "sat-16107579705752592", "resourceid": null, "UDetails": { "creds": { "email": "NA" }, "id": 14, "name": "N/A" }, "encryptionKey": "5inqhg7ckj7klk2w4osk0", "user": { "id": 5, "name": "CRI Admin", "employeecode": "125", "email": "admin@aaa.com" },

[gcusello]

Hi @smanojkumar,

using the spath command (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath) you should extract all json data in one shout!

But if you want to use regex, you could use

| rex "\"user\":\s+\{\s+\"id\":\s+(?<user_id>[^,]+)"

that you can test at https://regex101.com/r/HQX97O/1

Ciao.

Giuseppe

[smanojkumar]

Hi @gcusello ,

    Thanks for you response,

    Its worked, but when I used in search that already includes more field to be extracted, I Didn't get the data, 
So my intention is to extract data from aeci, In that aeci, need to extract entityid, name and id (in aeci under user), these 3 field needs be extracted.

[gcusello]

Hi @smanojkumar,

are you speaking of the solution with spath or with rex?

if spath, you should have all the available fields,

if with regex, try to execute separate regexs, so to extract your three fields:

| rex "\"user\":\s+\{\s+\"id\":\s+(?<user_id>[^,]+)"
| rex "\"entityid\":\s+\"(?<entityid>[^\"]+)"

for the thirs field (name) there are three values, which one?

Ciao.

Giuseppe

[smanojkumar]

Hi @gcusello ,

    In the raw data, There is several entityid, id and name, So the requirement is to fetch the entityid, id(under aeci,user) and name, these 3 should be extracted from aeci, like aeci, there is several different field, that also consists those fields, So can you please help in this.

[gcusello · Accepted solution]

Hi @smanojkumar,

for the third one use this:

| rex "\"aeci\".*\"name\":\s+\"(?<name>[^\"]+)\",\s+\"entitytype\":"

that you can test at https://regex101.com/r/9OlAWL/1

Ciao.

Giuseppe

[smanojkumar]

Hi @gcusello ,

   Thanks for your response, It worked.
Thank you once again!

[ITWhisperer]

This seems to be part of a larger JSON string. spath could be used (although I am not sure why you don't already have the fields extracted?)

| spath aeci.user.id output=id

[smanojkumar]

Hi @ITWhisperer ,

   Thanks for your response.