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
}]
}
}
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:
I have added some extra logic above and pasted a screenshot with the results.
Hope that helps.