topic Re: Extract a specific data from raw data in Getting Data In

How to extract a specific data from raw data?

smanojkumar — Mon, 13 Jun 2022 20:15:33 GMT

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" },

Re: Extract a specific data from raw data

ITWhisperer — Mon, 13 Jun 2022 12:50:48 GMT

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

Re: Extract a specific data from raw data

gcusello — Mon, 13 Jun 2022 12:53:12 GMT

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

Re: Extract a specific data from raw data

smanojkumar — Mon, 13 Jun 2022 15:22:14 GMT

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.

Re: Extract a specific data from raw data

gcusello — Mon, 13 Jun 2022 16:50:42 GMT

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

Re: Extract a specific data from raw data

smanojkumar — Tue, 14 Jun 2022 09:46:40 GMT

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.

Re: Extract a specific data from raw data

gcusello — Tue, 14 Jun 2022 10:50:27 GMT

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

Re: Extract a specific data from raw data

smanojkumar — Tue, 14 Jun 2022 11:56:06 GMT

Hi @gcusello ,

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

Re: Extract a specific data from raw data

smanojkumar — Tue, 14 Jun 2022 11:56:59 GMT

Hi @ITWhisperer ,

Thanks for your response.