All Apps and Add-ons

Need help to parse JSON REST API Response

ansif
Motivator

Please find the sample JSON data:

  {
      "cis" : [ {
        "ucmdbId" : "8b3d2f99df09c3d7e9f33ade6f18f4af",
        "globalId" : null,
        "type" : "file_system",
        "properties" : {
          "display_label" : "C",
          "disk_size" : 101897.0,
          "free_space" : 76.44
        }
      }, {
        "ucmdbId" : "e1d3952c4f07bae8d8f470b29d263180",
        "globalId" : null,
        "type" : "file_system",
        "properties" : {
          "display_label" : "C",
          "disk_size" : 101897.0,
          "free_space" : 75.17
        }
      }, {
        "ucmdbId" : "0125be84efd2436561f35b7debf9fdfa",
        "globalId" : null,
        "type" : "nt",
        "properties" : {
          "display_label" : "dexxxxxxxx18",
          "Server_Model" : "Silver",
          "Status" : "Active",
          "discovered_model" : "VMware Virtual Platform",
          "create_time" : "2018-02-01T07:05:42.332Z",
          "nt_physicalmemory" : "4193712",
          "Storage_Class" : "Silver",
          "Business_Unit" : "xxxxxxxxxxxxxxx",
          "Application" : "xxxxxxxxxxxxxxxx",
          "nt_processorsnumber" : 1
        }
      }, {
        "ucmdbId" : "07a757b4b6918800674a87ce6be0a2ff",
        "globalId" : null,
        "type" : "nt",
        "properties" : {
          "display_label" : "dxxxxxxxxxxxxxxx3",
          "Server_Model" : "Silver",
          "Status" : "Active",
          "discovered_model" : "VMware Virtual Platform",
          "create_time" : "2018-01-24T16:35:40.521Z",
          "nt_physicalmemory" : "4193712",
          "Storage_Class" : "Silver",
          "Business_Unit" : "xxxxxxxxxxxxxxxxxxx",
          "Application" : "xxxxxxxxxxxxxxxxxxxx",
          "nt_processorsnumber" : 1
        }
      }, {
        "ucmdbId" : "16cb902378fa5cc3d250c2d5e79fba1f",
        "globalId" : null,
        "type" : "file_system",
        "properties" : {
          "display_label" : "D",
          "disk_size" : 102269.0,
          "free_space" : 98.57
        }
      }, {
        "ucmdbId" : "17840c5200f445d33669eb62a69bc1a7",
        "globalId" : null,
        "type" : "file_system",
        "properties" : {
          "display_label" : "D",
          "disk_size" : 102269.0,
          "free_space" : 99.89
        }
      }, {
        "ucmdbId" : "4490191ade98235da67be691724e9fce",
        "globalId" : null,
        "type" : "nt",
        "properties" : {
          "display_label" : "dxxxxxxxxxxxxx2",
          "Server_Model" : "Gold",
          "Status" : "Active",
          "discovered_model" : "VMware Virtual Platform",
          "create_time" : "2018-03-19T14:10:12.310Z",
          "nt_physicalmemory" : "67108272",
          "Storage_Class" : "Gold",
          "Business_Unit" : "xxxxxxxxxxxxxx",
          "Application" : "xxxxxxxxxxxxxxxxxxxxx",
          "nt_processorsnumber" : 4
        }
      }, {
        "ucmdbId" : "4b685f01d6d0c5c181cdcde7b9c6b1a5",
        "globalId" : null,
        "type" : "file_system",
        "properties" : {
          "display_label" : "D",
          "disk_size" : 102269.0,
          "free_space" : 98.3
        }
      }, {
        "ucmdbId" : "4fbeb8fc1633f3908114cb3648f6b799",
        "globalId" : null,
        "type" : "file_system",
        "properties" : {
          "display_label" : "C",
          "disk_size" : 102047.0,
          "free_space" : 17.99
        }
      } ],
      "relations" : [ {
        "ucmdbId" : "d7246031c9d5e09661978c0dddddb17d",
        "globalId" : null,
        "type" : "composition",
        "properties" : null,
        "end1Id" : "0125be84efd2436561f35b7debf9fdfa",
        "end2Id" : "8b3d2f99df09c3d7e9f33ade6f18f4af"
      }, {
        "ucmdbId" : "e66b04516c2e0668539e22691714449b",
        "globalId" : null,
        "type" : "composition",
        "properties" : null,
        "end1Id" : "0125be84efd2436561f35b7debf9fdfa",
        "end2Id" : "17840c5200f445d33669eb62a69bc1a7"
      }, {
        "ucmdbId" : "4923aa1c20f639b39253df8e302c15ae",
        "globalId" : null,
        "type" : "composition",
        "properties" : null,
        "end1Id" : "4490191ade98235da67be691724e9fce",
        "end2Id" : "4fbeb8fc1633f3908114cb3648f6b799"
      }, {
        "ucmdbId" : "4f74124d9094579db77fba59aff89a80",
        "globalId" : null,
        "type" : "composition",
        "properties" : null,
        "end1Id" : "4490191ade98235da67be691724e9fce",
        "end2Id" : "4b685f01d6d0c5c181cdcde7b9c6b1a5"
      }, {
        "ucmdbId" : "4fb1429a6b6c429b59c8ba0835b97d19",
        "globalId" : null,
        "type" : "composition",
        "properties" : null,
        "end1Id" : "07a757b4b6918800674a87ce6be0a2ff",
        "end2Id" : "16cb902378fa5cc3d250c2d5e79fba1f"
      }, {
        "ucmdbId" : "76522e3fa85c7c8354bae9653336ebaf",
        "globalId" : null,
        "type" : "composition",
        "properties" : null,
        "end1Id" : "07a757b4b6918800674a87ce6be0a2ff",
        "end2Id" : "e1d3952c4f07bae8d8f470b29d263180"
      } ]
    }

Things to do:

  1. Split events with ucmdbid,like (not sure splitting helps to parse json easily,but the JSON response is really gonna huge)
    If REST API python response handler is good,help me with the code. Let timestamp is REST API call time.

    {
             "ucmdbId" : "8b3d2f99df09c3d7e9f33ade6f18f4af",
             "globalId" : null,
             "type" : "file_system",
             "properties" : {
               "display_label" : "C",
               "disk_size" : 101897.0,
               "free_space" : 76.44
             }
    

    {
    "ucmdbId" : "e1d3952c4f07bae8d8f470b29d263180",
    "globalId" : null,
    "type" : "file_system",
    "properties" : {
    "display_label" : "C",
    "disk_size" : 101897.0,
    "free_space" : 75.17
    }

  2. Find the Filesystem drives for each servers:

Example :
Server JSON Response:

{
         "ucmdbId" : "0125be84efd2436561f35b7debf9fdfa",
         "globalId" : null,
         "type" : "nt",
         "properties" : {
           "display_label" : "dexxxxxxxx18",
           "Server_Model" : "Silver",
           "Status" : "Active",
           "discovered_model" : "VMware Virtual Platform",
           "create_time" : "2018-02-01T07:05:42.332Z",
           "nt_physicalmemory" : "4193712",
           "Storage_Class" : "Silver",
           "Business_Unit" : "xxxxxxxxxxxxxxx",
           "Application" : "xxxxxxxxxxxxxxxx",
           "nt_processorsnumber" : 1
         }

Server ucmdb id:

0125be84efd2436561f35b7debf9fdfa

2 Matches:

{
         "ucmdbId" : "d7246031c9d5e09661978c0dddddb17d",
         "globalId" : null,
         "type" : "composition",
         "properties" : null,
         "end1Id" : "0125be84efd2436561f35b7debf9fdfa",
         "end2Id" : "8b3d2f99df09c3d7e9f33ade6f18f4af"
       }, {
         "ucmdbId" : "e66b04516c2e0668539e22691714449b",
         "globalId" : null,
         "type" : "composition",
         "properties" : null,
         "end1Id" : "0125be84efd2436561f35b7debf9fdfa",
         "end2Id" : "17840c5200f445d33669eb62a69bc1a7"
       }

Find the drive using the end2Id:

{
         "ucmdbId" : "8b3d2f99df09c3d7e9f33ade6f18f4af",
         "globalId" : null,
         "type" : "file_system",
         "properties" : {
           "display_label" : "C",
           "disk_size" : 101897.0,
           "free_space" : 76.44
         }

{
         "ucmdbId" : "17840c5200f445d33669eb62a69bc1a7",
         "globalId" : null,
         "type" : "file_system",
         "properties" : {
           "display_label" : "D",
           "disk_size" : 102269.0,
           "free_space" : 99.89
         }
  1. Calculate the total space :

Sum(All drives) for each server

  1. Plot in a table like below:

Server_Name Application Business_Unit Discovery_Model Server_Model Status Physical_Memory Procesor_Num Create_Time Storage_Class Sum(All drives)

1 Solution

niketn
Legend

@ansif since you are using Splunk REST API input it would be better if you split your CIs JSON array and relations JSON array and create single event for each ucmdbid.

Following steps are required:

Step 1) Change Rest API Response Handler Code Change to Split Events CIs and relations and create single event for each ucmdbid

class UcmdbJSONArrayHandler:

        def __init__(self,**args):
                pass

        def __call__(self, response_object,raw_response_output,response_type,req_args,endpoint):
                if response_type == "json":
                        output = json.loads(raw_response_output)
                        for ci in output["cis"]:
                                print_xml_stream(json.dumps(ci))
                        for relation in output["relations"]:
                                print_xml_stream(json.dumps(relation))
                else:
                        print_xml_stream(raw_response_output)

Step 2) Create a Splunk Query to push relations to lookup (you would need to create a join otherwise). Schedule as a daily search for Server Id and Partition Id mapping to be saved as latest Lookup file.

<yourbasesearch> type=composition
| dedup ucmdbid
| rename end1Id as serverId
| rename end2Id as partitionId
| table serverId partitionId
| outputlookup server_partition_mapping.csv

Step 3) Create Lookup Definition server_partition_mapping and provide required access.

Step 4) Add query to correlate Severs with their corresponding Partitions. (Another lookup can be created instead of join if required)

<yourbasesearch> type=nt 
| dedup ucmdbId 
| lookup server_partition_mapping serverId as ucmdbId 
| mvexpand partitionId 
| rename properties.display_label as server_name 
| rename partitionId as ucmdbId 
| join ucmdbId 
    [ search  <yourbasesearch> type=file_system 
    | dedup ucmdbId] 
| stats values(properties.Application) as Application sum(properties.disk_size) as disk_size sum(properties.free_space) as free_space by server_name 
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@ansif since you are using Splunk REST API input it would be better if you split your CIs JSON array and relations JSON array and create single event for each ucmdbid.

Following steps are required:

Step 1) Change Rest API Response Handler Code Change to Split Events CIs and relations and create single event for each ucmdbid

class UcmdbJSONArrayHandler:

        def __init__(self,**args):
                pass

        def __call__(self, response_object,raw_response_output,response_type,req_args,endpoint):
                if response_type == "json":
                        output = json.loads(raw_response_output)
                        for ci in output["cis"]:
                                print_xml_stream(json.dumps(ci))
                        for relation in output["relations"]:
                                print_xml_stream(json.dumps(relation))
                else:
                        print_xml_stream(raw_response_output)

Step 2) Create a Splunk Query to push relations to lookup (you would need to create a join otherwise). Schedule as a daily search for Server Id and Partition Id mapping to be saved as latest Lookup file.

<yourbasesearch> type=composition
| dedup ucmdbid
| rename end1Id as serverId
| rename end2Id as partitionId
| table serverId partitionId
| outputlookup server_partition_mapping.csv

Step 3) Create Lookup Definition server_partition_mapping and provide required access.

Step 4) Add query to correlate Severs with their corresponding Partitions. (Another lookup can be created instead of join if required)

<yourbasesearch> type=nt 
| dedup ucmdbId 
| lookup server_partition_mapping serverId as ucmdbId 
| mvexpand partitionId 
| rename properties.display_label as server_name 
| rename partitionId as ucmdbId 
| join ucmdbId 
    [ search  <yourbasesearch> type=file_system 
    | dedup ucmdbId] 
| stats values(properties.Application) as Application sum(properties.disk_size) as disk_size sum(properties.free_space) as free_space by server_name 
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

ansif
Motivator

@Niket : Exactly what I need.Thanks a lot

0 Karma
Get Updates on the Splunk Community!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...