CODEWITHSUNDEEP
sql-serverjsonexcelssissplunk
Yael Rozentaler
Yael Rozentaler

Reputation: 31

Query json data using Splunk

We have compressed data in json format at azure storage (tables and blobs) I want to ETL the data to relational DB in order to have the option to run sql queries on the data I have an utility which decompresses the data to json files, I did it via SSIS package to ETL from excel to sql server (using pivot after converting json to excel) Is there an easier way to get the same purpose using splunk? Please note that my json architecture is a bit complicated json example:

{
"columns": [
{
  "name": "database_name",
  "values": [
    "sales",
    "salesr",
    "sal"

  ],
  "encd": 0,
  "type": 0
},
{
  "name": "machine_name",
  "values": [
    "ISRVMN823",
    "ISRVMN825",
    "ISRVMN822"

  ],
  "encd": 0,
  "type": 0
},
{
  "name": "program_name",
  "values": [
    "SQLAgent - TSQL JobStep (Job 0x8701D9C6BFB3A146B9E6AB0602F5B4C3 : Step 1)",
    "SQLAgent - TSQL JobStep (Job 0xE3521B34CED03441B971A36E8EF5210B : Step 1)",
    "SQLAgent - TSQL JobStep (Job 0x4BBA5C65C5AF78469A7FE9B765BE430E : Step 1)"

  ],
  "encd": 0,
  "type": 0
}
],
"submission_time": 1483617753706,
"ds_id": "ISRVMN889",
"identity_broker": "00_yr",
"connection_name": "ISRVMN822SQL2012NY",
"table_name": "pass_unique_stat_5m",
"version": "1.0.0",
"duration": 300,
"sample_time": 1483617300000
}

I want to get something as the following in relational DB:

yy

or another way to query the data

Thanks

Upvotes: 3

Views: 1375

Answers (1)

Peter McIntyre
Peter McIntyre

Reputation: 159

Here you go :)

You can ignore the first part, that's just me hard-coding in your data into the search

| makeresults
| eval json = "{
\"columns\": [
{
  \"name\": \"database_name\",
  \"values\": [
    \"sales\",
    \"salesr\",
    \"sal\"

  ],
  \"encd\": 0,
  \"type\": 0
},
{
  \"name\": \"machine_name\",
  \"values\": [
    \"ISRVMN823\",
    \"ISRVMN825\",
    \"ISRVMN822\"

  ],
  \"encd\": 0,
  \"type\": 0
},
{
  \"name\": \"program_name\",
  \"values\": [
    \"SQLAgent - TSQL JobStep (Job 0x8701D9C6BFB3A146B9E6AB0602F5B4C3 : Step 1)\",
    \"SQLAgent - TSQL JobStep (Job 0xE3521B34CED03441B971A36E8EF5210B : Step 1)\",
    \"SQLAgent - TSQL JobStep (Job 0x4BBA5C65C5AF78469A7FE9B765BE430E : Step 1)\"

  ],
  \"encd\": 0,
  \"type\": 0
}
],
\"submission_time\": 1483617753706,
\"ds_id\": \"ISRVMN889\",
\"identity_broker\": \"00_yr\",
\"connection_name\": \"ISRVMN822SQL2012NY\",
\"table_name\": \"pass_unique_stat_5m\",
\"version\": \"1.0.0\",
\"duration\": 300,
\"sample_time\": 1483617300000
}"
| spath input=json path=columns{} | rename columns{} as cols | table cols
| mvexpand cols
| spath input=cols | rename values{} as values | table name values
| transpose header_field=name | fields - column

Upvotes: 1

Related Questions