Read from JSON Array

[Thulasiraman]

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

[ITWhisperer]

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

[Thulasiraman]

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.

[ITWhisperer]

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.

[Thulasiraman]

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

[ITWhisperer]

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

[Thulasiraman]

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

[ITWhisperer]

Try removing the other fields

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

[Thulasiraman]

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?

[ITWhisperer]

You possibly have duplicates/triplicates in your events.

[Thulasiraman]

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-t...

[ITWhisperer]

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.

[Thulasiraman]

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.

[ITWhisperer]

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

[Thulasiraman]

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

/>

[ITWhisperer · Accepted solution]

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.

[ITWhisperer]

Which fields are missing?