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

[janis_berzins]

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

[javiergn]

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:

[javiergn]

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