topic Re: Read from JSON Array in Getting Data In

Read from JSON Array

Thulasiraman — Thu, 09 Nov 2023 22:22:13 GMT

I have json file with below data, I would like to get name and status and display it in table. Help here is much appreciated. I'm new to splunk

Name Status
assetPortfolio_ValidateAddAssetForOthers passed
assetPortfolio_ValidatePLaceHolderText failure
assetPortfolio_ValidateIfFieldUpdated passed

{
"name": "behaviors",
"children": [
{
"name": "assetPortfolio_ValidateAddAssetForOthers",
"status": "passed"
},
{
"name": "assetPortfolio_ValidatePlaceHolderText",
"status": "failure"
},
{
"name": "assetPortfolio_ValidateIfFieldUpdated",
"status": "passed"
}
]
}

Re: Read from JSON Array

ITWhisperer — Thu, 09 Nov 2023 23:00:36 GMT

| spath children{} output=children | mvexpand children | spath input=children | table name status

Re: Read from JSON Array

Thulasiraman — Mon, 13 Nov 2023 13:44:54 GMT

Thank you for your inquiry. This is useful for isolated json files. However, this file is generated every day, and I'd like to display the latest 7 days' numbers in a table by date.

Re: Read from JSON Array

ITWhisperer — Mon, 13 Nov 2023 13:51:49 GMT

Splunk, in particular SPL, works on a pipeline of events. Each event in the pipeline is processed. If you have a number of events over a number of days, how do you distinguish them from each other as your event example doesn't appear to have a timestamp?

That being said, if you do have a way to identify the original events, before the mvexpand, you can use stats by to gather the separate parts together again.

Perhaps if you provided more representative examples of the events you are dealing with, an explanation of exactly what you are trying to achieve and a representation of your expected / desired output, we might be able to assist you further.

Re: Read from JSON Array

Thulasiraman — Tue, 14 Nov 2023 18:50:15 GMT

Thanks again!

Yes! Below json files are generated every day and I would like to show them in table format as below

Source: Group01/1318/test.json
Generated timestamp: 11/12 12:00 AM

{

"Portfolio_Validate1":"skipped",

"Portfolio_Validate2":"passed",

"Portfolio_Validate3":"passed",

"Portfolio_Validate4":"broken"

}

Source: Group01/1319/test.json
Generated timestamp: 11/13 12:00 AM

{

"Portfolio_Validate1":"passed",

"Portfolio_Validate2":"passed",

"Portfolio_Validate3":"passed",

"Portfolio_Validate4":"broken"

}

Source: Group01/1320/test.json
Generated timestamp: 11/14 12:00 AM

{

"Portfolio_Validate1":"passed",

"Portfolio_Validate2":"failed",

"Portfolio_Validate3":"passed",

"Portfolio_Validate4":"passed"

}

	11/14 12:00 AM	11/14 12:00 AM	11/12 12:00 AM
Portfolio_Validate1	passed	passed	skipped
Portfolio_Validate1	failed	passed	passed
Portfolio_Validate1	passed	passed	passed
Portfolio_Validate1	passed	broken	broken

Re: Read from JSON Array

ITWhisperer — Tue, 14 Nov 2023 19:19:21 GMT

Assuming your timestamp is in _time and your events (as shown) are in _raw, try this

| spath | untable _time name state | eval date=strftime(_time,"%F") | xyseries name date state

Re: Read from JSON Array

Thulasiraman — Tue, 14 Nov 2023 20:04:05 GMT

Thanks Again! You're my saver

Your query works. However, For some reason I see state twice. Also, i see source, host etc... being listed in the table

Re: Read from JSON Array

ITWhisperer — Tue, 14 Nov 2023 21:51:35 GMT

Try removing the other fields

Re: Read from JSON Array

Thulasiraman — Tue, 14 Nov 2023 22:00:41 GMT

Thanks one more time.
Interestingly, your recent query is fetching only 77 values where as i have 182 values in json file. Is this splunk limitation?

Re: Read from JSON Array

ITWhisperer — Tue, 14 Nov 2023 22:09:57 GMT

You possibly have duplicates/triplicates in your events.

Re: Read from JSON Array

Thulasiraman — Tue, 14 Nov 2023 22:17:54 GMT

I checked complete json, don't see any duplicates. I see some solutions in the blog asking to switch from "INDEXED_EXTRACTIONS = JSON" to "KV_MODE = json". I'm not sure that will work in my case.

https://community.splunk.com/t5/Splunk-Search/INDEXED-EXTRACTIONS-JSON-limiting-multivalued-fields-to-10/td-p/279893

Re: Read from JSON Array

ITWhisperer — Tue, 14 Nov 2023 23:07:59 GMT

The table _time _raw and spath effectively reparse the JSON otherwise you have the extracted files from the ingest as well as the fields from the spath.

Without seeing the actual events, I can't tell what might be causing the disparity between the counts and number of lines. Perhaps there are extra blank lines, or new line characters.

Re: Read from JSON Array

Thulasiraman — Wed, 15 Nov 2023 03:21:07 GMT

I don't see any new line character. I have attached a snippet of the event. Please let me know how can I send event file (.json file). json is not supported attachment here.

Re: Read from JSON Array

ITWhisperer — Wed, 15 Nov 2023 08:55:11 GMT

Which fields are missing?

Re: Read from JSON Array

ITWhisperer — Wed, 15 Nov 2023 08:55:58 GMT

Copy the raw event and paste into a code block </>

Re: Read from JSON Array

Thulasiraman — Wed, 15 Nov 2023 13:32:16 GMT

Due to character limitation in the blog i removed assetPortfolio_Verify prefix in the name

example: "AssetDetailsPage":"passed", should be "assetPortfolio_VerifyAssetDetailsPage":"passed",
<

{
"AssetDetailsPage":"passed",
"AddAsset_OTHERDistributor":"passed",
"AddAssetForOthers":"passed",
"AddAssetValidationDetailsOboUser":"passed",
"AddLocationDetailsFields_SUMAUser":"passed",
"AddLocationUIChangesOboUser":"passed",
"AddLocationDetailsFields":"passed",
"AddAssetValidationDetailsSumaUser":"passed",
"AddLocationUIChangesSumaUser":"passed",
"AddPropertyValidationDetails":"passed",
"NoResultsMessage":"passed",
"AssetFilters":"passed",
"EditFunctionality":"passed",
"APMManageAssetTab":"passed",
"AddAssetValidationDetails":"passed",
"AddAssetLabels":"passed",
"AddLocationUIChanges":"passed",
"InvalidFormatOnPurchaseDateFiled":"passed",
"APMPropertyDropdown":"passed",
"SearchOnManageAsset":"broken",
"AddPropertyValidationDetailsForOboUser":"passed",
"AddPropertyValidationDetailsForSumaUser":"passed",
"AssetDetailsDateFieldsInvalidErrors":"passed",
"AssetDetailsDateFieldsInvalidErrors_SUMAUser":"passed",
"AssetDetailsToolTipOnAddEditCopyLocation":"broken",
"ChangeLocationDetailsFields":"broken",
"ChangeLocationDetailsFieldsOboUser":"passed",
"ChangeLocationDetailsFieldsSumaUser":"failed",
"ChangeLocationUIChangesDetailsFields":"passed",
"ChangeLocationUIChangesDetailsFieldsOboUser":"passed",
"ChangeLocationUIChangesDetailsFieldsSumaUser":"passed",
"CopyLocationAllDetailsFields":"passed",
"CopyLocationAllDetailsFieldsOboUser":"passed",
"CopyLocationAllDetailsFieldsSumaUser":"passed",
"CopyLocationUIChanges":"passed",
"CopyLocationUIChangesOboUser":"passed",
"CopyLocationUIChangesSumaUser":"passed",
"EditAssetValidationDetails":"passed",
"EditAssetValidationDetailsOboUser":"passed",
"EditAssetValidationDetailsSumaUser":"passed",
"EditLocationAllDetailsFields":"passed",
"EditLocationAllDetailsFieldsOboUser":"passed",
"EditLocationAllDetailsFieldsSumaUser":"passed",
"EditLocationUIChanges":"passed",
"EditLocationUIChangesOboUser":"passed",
"EditLocationUIChangesSumaUser":"passed",
"EditPropertyValidationDetails":"passed",
"EditPropertyValidationDetailsOboUser":"passed",
"EditPropertyValidationDetailsSumaUser":"passed",
"FieldSonAddAsset_OTHERDistributor":"passed",
"LabelsUnderLocationsWithValues":"broken",
"ListOfPropertiesInPropertyTypeDropdown_AddProperty":"passed",
"ListOfPropertiesInPropertyTypeDropdown_EditProperty":"passed",
"ManufacturerComponentOnManageAssets":"passed",
"PerformanceDataFieldsInvalidErrors":"passed",
"PerformanceDataFieldsInvalidErrors_SUMAUser":"passed",
"PropertiesListInSortedOrder":"passed",
"PurchaseDateFiled_AddLocationModal":"passed",
"PurchaseDateFiled_CopyLocationModal":"passed",
"PurchaseDateFiled_EditLocationModal":"passed",
"RemoveLocationUIChanges":"passed",
"RemoveLocationUIChangesOboUser":"passed",
"RemoveLocationUIChangesSumaUser":"passed",
"BulkUploadNarrativeOnAddAssetModal":"passed",
"BulkUploadOnManageAssetPage":"passed",
"BulkUploadPageDetails":"passed",
"BulkUploadPageNameFromManageAssetsPage":"passed",
"BulkUploadPageNameFromManageAssetsPage_OBO":"passed",
"BulkUploadPageNameFromManageAssetsPage_SUMA":"passed",
"BulkUploadTab":"passed",
"PageNamesOnBulkUploadPages":"passed",
"PageNamesOnBulkUploadPages_OBO":"passed",
"PageNamesOnBulkUploadPages_SUMA":"passed",
"TabsOnBulkUploadPage":"passed",
"AssetCountOnDashboard":"passed",
"BETAOnMyAssetManager":"passed",
"BETATextOnMyAssetManager_NonAPMUser":"passed",
"DashboardAssetLifeStatusNewTable":"passed",
"DashboardAssetLifeStatusNewTableOboUser":"broken",
"DashboardAssetLifeStatusNewTableSorting":"passed",
"DashboardAssetLifeStatusNewTableSortingOboUser":"passed",
"DashboardAssetLifeStatusNewTableSortingSumaUser":"broken",
"DashboardAssetLifeStatusNewTableSumaUser":"passed",
"DashboardAssetLifeStatusReplaceNowTable":"broken",
"DashboardAssetLifeStatusReplaceNowTableOboUser":"passed",
"DashboardAssetLifeStatusReplaceNowTableSumaUser":"passed",
"DashboardAssetLifeStatusReplaceSoonTable":"passed",
"DashboardAssetLifeStatusReplaceSoonTableOboUser":"passed",
"DashboardAssetLifeStatusReplaceSoonTableSumaUser":"passed",
"DashboardAssetLifeStatusStyleCardLayout":"broken",
"DashboardAssetLifeStatusStyleCardLayoutOboUser":"broken",
"DashboardAssetLifeStatusStyleCardLayoutSumaUser":"broken",
"DashboardCategoryFilterFunctionalities":"passed",
"DashboardCategoryFilterFunctionalities_OBOUser":"passed",
"DashboardCategoryFilterWhenZeroAssets":"passed",
"DashboardCategoryFilterWhenZeroAssets_OBOUser":"passed",
"DashboardNewTableFunctionality":"passed",
"DashboardNewTableFunctionalityOBOUser":"passed",
"DashboardNewTableFunctionalitySumaUser":"passed",
"DashboardPropertyFilterFunctionalities":"passed",
"DashboardPropertyFilterFunctionalities_OBOUser":"passed",
"DashboardPropertyFilterWhenZeroAssets":"passed",
"DashboardPropertyFilterWhenZeroAssets_OBOUser":"passed",
"DashboardReplaceNowTableFunctionality":"broken",
"DashboardReplaceNowTableFunctionalityOboUser":"passed",
"DashboardReplaceNowTableFunctionalitySumaUser":"passed",
"DashboardReplaceNowTableSorting":"passed",
"DashboardReplaceNowTableSortingOboUser":"passed",
"DashboardReplaceNowTableSortingSumaUser":"passed",
"DashboardReplaceSoonTableFunctionality":"passed",
"DashboardReplaceSoonTableFunctionalityOboUser":"broken",
"DashboardReplaceSoonTableFunctionalitySumaUser":"passed",
"DashboardReplaceSoonTableSorting":"passed",
"DashboardReplaceSoonTableSortingOboUser":"passed",
"DashboardReplaceSoonTableSortingSumaUser":"passed",
"AssetNameLinkInWarrantyExpiration":"passed",
"DashboardPageRecentlyInstalledTab":"passed",
"DashboardPageTabs":"passed",
"DashboardWelcomeMessageWhenUserReturns":"passed",
"DataDisplayedInRecentlyAdded":"passed",
"EmptyStateAssetLifeStatusMessage":"passed",
"EmptyStateMessagesOnDashboardPageTabs":"passed",
"EmptyStateRecentlyAddedTabAndLinkToAddAssetModal":"passed",
"HyperLinkAssetNameInRecentlyAdded":"passed",
"ManagePropertyAsset":"passed",
"ManufacturerWarrantyTypeInWarrantyExpiration":"passed",
"RecentlyAddedTab":"passed",
"RecentlyAddedTableColumns":"passed",
"RecentlyInstalledTabData":"passed",
"RecentlyInstalledTableColumnsData":"passed",
"ServiceWarrantyTypeInWarrantyExpiration":"passed",
"TopManufacturerSection":"passed",
"WarrantyExpirationTabColumnsData":"passed",
"SortingOnManageAssetsComponents":"passed",
"UserAuthenticationToVideoResourcesPage":"passed",
"VideoResourcesTabEspotAndVideoPlayWindow":"passed",
"VideoResourcesTabOnMyAssetManager":"passed",
"FilterSearchBasedOnCategory":"passed",
"FilterSearchBasedOnDistributor":"broken",
"IfLocationListEnabledInManageAssets":"passed",
"AddLocationFields":"passed",
"AddPropertyFields":"passed",
"AssetPropertyValidation":"passed",
"DisabledFieldsForNoAssets":"passed",
"DisabledFieldsForNoLocation":"passed",
"FloorPlanForAsset":"passed",
"IfFieldUpdated":"passed",
"NoResultMessage":"passed",
"PLaceHolderText":"passed",
"PropertyListTable":"passed",
"PropertySorting":"failed",
"SearchField":"passed",
"TableRecords":"passed",
"UpdateAsset":"passed",
"APMPropertyDropdown":"passed",
"AddLocationForHVAC":"passed",
"AddNewLocationList":"passed",
"AddNewProperty":"passed",
"AscendingOrderInAssetTableColumn":"passed",
"CancelBtnOnAddProperty":"passed",
"CancelBtnOnEditProperty":"passed",
"CloseModelOnAddAsset":"passed",
"ComponentsOnManageAssets":"passed",
"CopyComponentUnderLocation":"broken",
"CreateAssetViewComponent":"broken",
"CreatePageNameForManageProperties":"passed",
"FloorPlanOnManageAssets":"passed",
"LeftNavLink":"passed",
"ManageProperties":"passed",
"ManagePropertyLeftNavigationLinks":"passed",
"MessageForNoLocationAvailable":"passed",
"MousePointerAndNoHighlight":"passed",
"NewFieldsLocation":"passed",
"NewFieldsLocationOnHvac":"passed",
"NoAssetAvailable":"passed",
"NoPropertyAvailable":"passed",
"RemoveLocation":"passed",
"SearchOnManageAssetPage":"passed",
"SearchWithInvalidCharacter":"passed",
"PlaceholderTextSearchBox":"passed"
}

Re: Read from JSON Array

ITWhisperer — Wed, 15 Nov 2023 14:12:40 GMT

It looks like spath has a character limit spath - Splunk Documentation

Try using rex to extract key/value pairs

| rex max_match=0 "(?<keyvalue>\"[^\"]+\":\"[^\"]+\")" | mvexpand keyvalue | rex field=keyvalue "\"(?<key>[^\"]+)\":\"(?<value>[^\"]+)\"" | eval {key}=value | fields - keyvalue key value _raw | eval date=strftime(_time,"%F") | untable date name state ```| stats count by name | where count > 1``` | xyseries name date state

Btw, you do have a duplicate, assetPortfolio_VerifyAPMPropertyDropdown, uncomment the commented lines and comment out the last line to show it.