json field extraction

[Nadhiya_Dubai]

Hi ,

Below is the json snippet

properties: { [-]
columns: [ [-]
{ [-]
name: PreTaxCost
type: Number
}
{ [-]
name: UsageDate
type: Number
}
{ [-]
name: Currency
type: String
}
]
nextLink: null
rows: [ [-]
[ [-]
37.399436789282746
20200301
USD
]
[ [-]
37.4605201027181
20200302
USD
]

how can i extract the fields pretaxcost ,usagedate

[manjunathmeti · Accepted solution]

Hi @Nadhiya_Dubai,

Try this:

| spath path=properties.rows{}{} output=values | nomv values | eval values=split(values, "USD") | mvexpand values | rex field=values "^\s*(?<PreTaxCost>[\d\.]+)\s*(?<UsageDate>[\d]+)$" | where isnotnull(PreTaxCost) and isnotnull(UsageDate) | spath | table id, name, eTag, name, type, PreTaxCost, UsageDate, property.nextLink

Sample query:

| makeresults | eval _raw="{\"sku\": null, \"name\": \"b0b36773-618e-4490-9c11-57eaf5cb0f49\", \"properties\": {\"rows\": [[37.39104138816985, 20200301, \"USD\"], [37.45212432304218, 20200302, \"USD\"], [37.19654530403741, 20200303, \"USD\"], [37.34428721156659, 20200304, \"USD\"], [38.220217017864336, 20200305, \"USD\"], [37.70977490679157, 20200306, \"USD\"], [37.53643148801985, 20200307, \"USD\"], [37.47332013352408, 20200308, \"USD\"], [27.71226225182299, 20200309, \"USD\"]], \"columns\": [{\"type\": \"Number\", \"name\": \"PreTaxCost\"}, {\"type\": \"Number\", \"name\": \"UsageDate\"}, {\"type\": \"String\", \"name\": \"Currency\"}], \"nextLink\": null}, \"eTag\": null, \"location\": null, \"type\": \"Microsoft.CostManagement/query\", \"id\": \"subscriptions/2808ca16-1c43-4d1c-8088-223b7c378d46/providers/Microsoft.CostManagement/query/b0b36773-618e-4490-9c11-57eaf5cb0f49\"}" | spath path=properties.rows{}{} output=values | nomv values | eval values=split(values, "USD") | mvexpand values | rex field=values "^\s*(?<PreTaxCost>[\d\.]+)\s*(?<UsageDate>[\d]+)$" | where isnotnull(PreTaxCost) and isnotnull(UsageDate) | spath | table id, name, eTag, name, type, PreTaxCost, UsageDate, property.nextLink

[nwenzl_splunk]

Hi, have you tried using spath?
https://docs.splunk.com/Documentation/Splunk/8.0.2/SearchReference/Spath

[to4kawa]

| makeresults 
| eval _raw="properties: { 
columns: 
{ 
name: PreTaxCost
type: Number
}
{ 
name: UsageDate
type: Number
}
{ 
name: Currency
type: String
}
]
nextLink: null
rows: 
[
37.399436789282746
20200301
USD
]
[
37.4605201027181
20200302
USD
]"
| rex max_match=0 "(?ms)(?<pretaxcost>[\d\.]+)\s*(?<usagedate>\d+)"

[Nadhiya_Dubai]

Hi .. Its not working .

[to4kawa]

what's your query?
so, your json log is not valied json.
what' s this log?

[Nadhiya_Dubai]

When i give the above mentioned query provide by you with raw data ,it is working .
For my below json data

{ [-]
eTag: null
id: subscriptions/2808ca16-1c43-4d1c-8088-223b7c378d46/providers/Microsoft.CostManagement/query/b0b36773-618e-4490-9c11-57eaf5cb0f49
location: null
name: b0b36773-618e-4490-9c11-57eaf5cb0f49
properties: { [-]
columns: [ [-]
{ [-]
name: PreTaxCost
type: Number
}
{ [-]
name: UsageDate
type: Number
}
{ [-]
name: Currency
type: String
}
]
nextLink: null
rows: [ [-]
[ [-]
37.39104138816985
20200301
USD
]
[ [-]
37.45212432304218
20200302
USD
]

index=azure_new i | rex max_match=0 "(?ms)(?[\d.]+)\s*(?\d+)" | table pretaxcost,usagedate

It is not working . What is the issue

[to4kawa]

will you provide _raw data?

[Nadhiya_Dubai]

{"sku": null, "name": "b0b36773-618e-4490-9c11-57eaf5cb0f49", "properties": {"rows": [[37.39104138816985, 20200301, "USD"], [37.45212432304218, 20200302, "USD"], [37.19654530403741, 20200303, "USD"], [37.34428721156659, 20200304, "USD"], [38.220217017864336, 20200305, "USD"], [37.70977490679157, 20200306, "USD"], [37.53643148801985, 20200307, "USD"], [37.47332013352408, 20200308, "USD"], [27.71226225182299, 20200309, "USD"]], "columns": [{"type": "Number", "name": "PreTaxCost"}, {"type": "Number", "name": "UsageDate"}, {"type": "String", "name": "Currency"}], "nextLink": null}, "eTag": null, "location": null, "type": "Microsoft.CostManagement/query", "id": "subscriptions/2808ca16-1c43-4d1c-8088-223b7c378d46/providers/Microsoft.CostManagement/query/b0b36773-618e-4490-9c11-57eaf5cb0f49"}