Splunk Search

Why is my search not pulling all results from a log in JSON format?

janis_berzins
Engager

I need some help writing a search that can do the following things:

The log file below needs to be interrogated and the mean ElapsedTimeMs needs to be output for a given set of tests.

In the case shown below, that would be all InsertInvoice tests.

I need the lowest and highest values to be discarded from the calculation.

I also need to know the number of tests that were run for each set – i.e. how many Insertinvoice results that there are (excluding the highest and lowest value containing ones).

My Search:

source="PerfTester_20-11-2015.txt20-11-2015.txt" host="sh1.tungsten.splunkcloud.com" index="genesis" sourcetype="GenesisPerfTest" 
| spath
| rename message.TestName AS TestNameTop, message.Results{}.TestName AS TestName, message.Results{}.ElapsedTimeMs AS ElapsedTimeMs, message.Results{}.Notes AS TestNotes 
| eval x=mvzip(TestName,ElapsedTimeMs,TestNotes)
| dedup x
| search TestName="InsertInvoice"
| table ElapsedTimeMs, TestNotes, TestName

For some reason, this search returns me all the results, but 3 times.

I suspect that the log is not formatted in correct way. The results array might bee missing object names like something that is used in the Notes object.

Example log:

{
    "date": "2015-11-20T10:27:07",
    "UID": "(null)",
    "SID": "(null)",
    "logger": "GENESIS.PERFTEST",
    "message": {
        "TestName": "InsertInvoice",
        "Notes": "Starting Insert testing with 10 tests",
        "Start": "2015-11-20T10:26:15.0825842+00:00",
        "End": "2015-11-20T10:27:07.5897673+00:00",
        "ElapsedTimeMs": 52499,
        "Results": [{
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 1",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 16245,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 10",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 35510,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 2",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 305,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 3",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 64,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 4",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 86,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 5",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 63,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 6",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 56,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 7",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 65,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 8",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 54,
            "Results": null
        },
        {
            "TestName": "InsertInvoice",
            "Notes": "Inserted invoice with InvoiceNumber 9",
            "Start": null,
            "End": null,
            "ElapsedTimeMs": 51,
            "Results": null
        }]
    }
}
Tags (3)
0 Karma

javiergn
SplunkTrust
SplunkTrust

The following works for me (you can copy and paste it into your search GUI, the logic is at the bottom):

| stats count
| eval myjson = "{
\"date\": \"2015-11-20T10:27:07\",
\"UID\": \"(null)\",
\"SID\": \"(null)\",
\"logger\": \"GENESIS.PERFTEST\",
\"message\": {
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Starting Insert testing with 10 tests\",
\"Start\": \"2015-11-20T10:26:15.0825842+00:00\",
\"End\": \"2015-11-20T10:27:07.5897673+00:00\",
\"ElapsedTimeMs\": 52499,
\"Results\": [{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 1\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 16245,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 10\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 35510,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 2\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 305,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 3\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 64,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 4\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 86,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 5\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 63,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 6\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 56,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 7\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 65,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 8\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 54,
\"Results\": null
},
{
\"TestName\": \"InsertInvoice\",
\"Notes\": \"Inserted invoice with InvoiceNumber 9\",
\"Start\": null,
\"End\": null,
\"ElapsedTimeMs\": 51,
\"Results\": null
}]
}
}"
| spath input=myjson
| fields *ElapsedTimeMs
| rename message.ElapsedTimeMs AS TotalElapsedTimeMs, message.Results{}.ElapsedTimeMs AS TestElapsedTimeMs
| mvexpand TestElapsedTimeMs
| sort 0 num(TestElapsedTimeMs)
| stats list(TestElapsedTimeMs) as TestElapsedTimeMs by TotalElapsedTimeMs
| eval TestElapsedTimeMs_NoMaxMin=mvindex(TestElapsedTimeMs, 1, mvcount(TestElapsedTimeMs)-2)
| stats 
    list(TotalElapsedTimeMs) as TotalElapsedTimeMs,
    list(TestElapsedTimeMs) as TestElapsedTimeMs, 
    avg(TestElapsedTimeMs) as Avg_TestElapsedTimeMs,
    list(TestElapsedTimeMs_NoMaxMin) as TestElapsedTimeMs_NoMaxMin, 
    avg(TestElapsedTimeMs_NoMaxMin) as Avg_TestElapsedTimeMs_NoMaxMin

And this is the result I'm getting:

alt text

0 Karma

javiergn
SplunkTrust
SplunkTrust

I have added some extra logic above and pasted a screenshot with the results.
Hope that helps.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...