All Apps and Add-ons

Need help to parse JSON REST API Response

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" : "filesystem",
    "properties" : {
    "display
    label" : "C",
    "disksize" : 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:

ServerName Application BusinessUnit DiscoveryModel ServerModel Status PhysicalMemory ProcesorNum CreateTime StorageClass Sum(All drives)

1 Solution

SplunkTrust
SplunkTrust

@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 serverpartitionmapping 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

SplunkTrust
SplunkTrust

@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 serverpartitionmapping 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

Motivator

@Niket : Exactly what I need.Thanks a lot

0 Karma