Dashboards & Visualizations

Need output in table format from event in jason format

ravir_jbp
Explorer

Event: [{"hostname":"BBBBBBBBB","contentSourceName":"Authored","contentSourceType":"Authored","incremental":true,"skipCrawl":false,"isBulk":false,"startTime":1616802303335,"endTime":1616802355772,"reportStatus":"Success","documentsFound":20,"documentsFailed":0,"documentsSucceeded":16,"documentsFiltered":0,"documentsUnchanged":0,"contentProcessed":16,"contentAdded":0,"contentUpdated":0,"contentDeleted":0,"pdfSlices":0,"pdfDocCount":0,"exceptionCount":0,"generalExceptionCount":0,"warningCount":0,"processorFailureCount":0,"generalizedFailureCount":0,"heritrixErrorCount":0,"duplicateItemCount":0,"duplicateReportRelativeFilename":null,"jobId":-1}, {"hostname":"AAAAAAAA","contentSourceName":"Authored","contentSourceType":"Authored","incremental":true,"skipCrawl":false,"isBulk":false,"startTime":1616801520297,"endTime":1616801578765,"reportStatus":"Success","documentsFound":40,"documentsFailed":0,"documentsSucceeded":28,"documentsFiltered":0,"documentsUnchanged":0,"contentProcessed":28,"contentAdded":0,"contentUpdated":0,"contentDeleted":0,"pdfSlices":0,"pdfDocCount":0,"exceptionCount":0,"generalExceptionCount":0,"warningCount":0,"processorFailureCount":0,"generalizedFailureCount":0,"heritrixErrorCount":0,"duplicateItemCount":0,"duplicateReportRelativeFilename":null,"jobId":-1}, {"hostname":"ZZZZZZZZZ","contentSourceName":"Authored","contentSourceType":"Authored","incremental":true,"skipCrawl":false,"isBulk":false,"startTime":1616797920257,"endTime":1616797999256,"reportStatus":"Success","documentsFound":104,"documentsFailed":0,"documentsSucceeded":59,"documentsFiltered":0,"documentsUnchanged":0,"contentProcessed":59,"contentAdded":0,"contentUpdated":0,"contentDeleted":0,"pdfSlices":0,"pdfDocCount":0,"exceptionCount":0,"generalExceptionCount":0,"warningCount":0,"processorFailureCount":0,"generalizedFailureCount":0,"heritrixErrorCount":0,"duplicateItemCount":0,"duplicateReportRelativeFilename":null,"jobId":-1}, {"hostname":"YYYYYYYY","contentSourceName":"Authored","contentSourceType":"Authored","incremental":true,"skipCrawl":false,"isBulk":false,"startTime":1616794883261,"endTime":1616795120383,"reportStatus":"Success","documentsFound":236,"documentsFailed":3,"documentsSucceeded":121,"documentsFiltered":0,"documentsUnchanged":0,"contentProcessed":121,"contentAdded":0,"contentUpdated":0,"contentDeleted":0,"pdfSlices":0,"pdfDocCount":0,"exceptionCount":0,"generalExceptionCount":0,"warningCount":0,"processorFailureCount":3,"generalizedFailureCount":3,"heritrixErrorCount":0,"duplicateItemCount":0,"duplicateReportRelativeFilename":null,"jobId":-1}, {"hostname":"XXXXXXXX","contentSourceName":"Authored","contentSourceType":"Authored","incremental":true,"skipCrawl":false,"isBulk":false,"startTime":1616742071025,"endTime":1616794342113,"reportStatus":"Success","documentsFound":83004,"documentsFailed":640,"documentsSucceeded":81533,"documentsFiltered":0,"documentsUnchanged":0,"contentProcessed":81528,"contentAdded":0,"contentUpdated":0,"contentDeleted":0,"pdfSlices":0,"pdfDocCount":0,"exceptionCount":0,"generalExceptionCount":0,"warningCount":0,"processorFailureCount":640,"generalizedFailureCount":640,"heritrixErrorCount":0,"duplicateItemCount":0,"duplicateReportRelativeFilename":null,"jobId":-1}] ================================

We get above data in one event. I like to extract few data from above event in dashboard in table format:

Hostname | contentSourceName | incremental| startTime| endTime|Duration| reportStatus | documentsFound | documentsFailed |

The extra column needs to be add is i.e. "Duration" that can be extracted from StartTime and EndTime. Start date and end date is in Unix epoch time that needs to be converted into human readable format. Please help

Labels (1)
Tags (1)
0 Karma

ITWhisperer
Ultra Champion

Your example doesn't have the outer JSON field, so I added that in order to extract the array elements as a multivaliue field which could expanded (mvexpand) - you may be able to extract this more easily from your real data.

Your epoch times are in milliseconds not seconds as normal, so I converted those before calculating the duration and formatting for readability

| makeresults 
| eval _raw="[{\"hostname\":\"BBBBBBBBB\",\"contentSourceName\":\"Authored\",\"contentSourceType\":\"Authored\",\"incremental\":true,\"skipCrawl\":false,\"isBulk\":false,\"startTime\":1616802303335,\"endTime\":1616802355772,\"reportStatus\":\"Success\",\"documentsFound\":20,\"documentsFailed\":0,\"documentsSucceeded\":16,\"documentsFiltered\":0,\"documentsUnchanged\":0,\"contentProcessed\":16,\"contentAdded\":0,\"contentUpdated\":0,\"contentDeleted\":0,\"pdfSlices\":0,\"pdfDocCount\":0,\"exceptionCount\":0,\"generalExceptionCount\":0,\"warningCount\":0,\"processorFailureCount\":0,\"generalizedFailureCount\":0,\"heritrixErrorCount\":0,\"duplicateItemCount\":0,\"duplicateReportRelativeFilename\":null,\"jobId\":-1}, {\"hostname\":\"AAAAAAAA\",\"contentSourceName\":\"Authored\",\"contentSourceType\":\"Authored\",\"incremental\":true,\"skipCrawl\":false,\"isBulk\":false,\"startTime\":1616801520297,\"endTime\":1616801578765,\"reportStatus\":\"Success\",\"documentsFound\":40,\"documentsFailed\":0,\"documentsSucceeded\":28,\"documentsFiltered\":0,\"documentsUnchanged\":0,\"contentProcessed\":28,\"contentAdded\":0,\"contentUpdated\":0,\"contentDeleted\":0,\"pdfSlices\":0,\"pdfDocCount\":0,\"exceptionCount\":0,\"generalExceptionCount\":0,\"warningCount\":0,\"processorFailureCount\":0,\"generalizedFailureCount\":0,\"heritrixErrorCount\":0,\"duplicateItemCount\":0,\"duplicateReportRelativeFilename\":null,\"jobId\":-1}, {\"hostname\":\"ZZZZZZZZZ\",\"contentSourceName\":\"Authored\",\"contentSourceType\":\"Authored\",\"incremental\":true,\"skipCrawl\":false,\"isBulk\":false,\"startTime\":1616797920257,\"endTime\":1616797999256,\"reportStatus\":\"Success\",\"documentsFound\":104,\"documentsFailed\":0,\"documentsSucceeded\":59,\"documentsFiltered\":0,\"documentsUnchanged\":0,\"contentProcessed\":59,\"contentAdded\":0,\"contentUpdated\":0,\"contentDeleted\":0,\"pdfSlices\":0,\"pdfDocCount\":0,\"exceptionCount\":0,\"generalExceptionCount\":0,\"warningCount\":0,\"processorFailureCount\":0,\"generalizedFailureCount\":0,\"heritrixErrorCount\":0,\"duplicateItemCount\":0,\"duplicateReportRelativeFilename\":null,\"jobId\":-1}, {\"hostname\":\"YYYYYYYY\",\"contentSourceName\":\"Authored\",\"contentSourceType\":\"Authored\",\"incremental\":true,\"skipCrawl\":false,\"isBulk\":false,\"startTime\":1616794883261,\"endTime\":1616795120383,\"reportStatus\":\"Success\",\"documentsFound\":236,\"documentsFailed\":3,\"documentsSucceeded\":121,\"documentsFiltered\":0,\"documentsUnchanged\":0,\"contentProcessed\":121,\"contentAdded\":0,\"contentUpdated\":0,\"contentDeleted\":0,\"pdfSlices\":0,\"pdfDocCount\":0,\"exceptionCount\":0,\"generalExceptionCount\":0,\"warningCount\":0,\"processorFailureCount\":3,\"generalizedFailureCount\":3,\"heritrixErrorCount\":0,\"duplicateItemCount\":0,\"duplicateReportRelativeFilename\":null,\"jobId\":-1}, {\"hostname\":\"XXXXXXXX\",\"contentSourceName\":\"Authored\",\"contentSourceType\":\"Authored\",\"incremental\":true,\"skipCrawl\":false,\"isBulk\":false,\"startTime\":1616742071025,\"endTime\":1616794342113,\"reportStatus\":\"Success\",\"documentsFound\":83004,\"documentsFailed\":640,\"documentsSucceeded\":81533,\"documentsFiltered\":0,\"documentsUnchanged\":0,\"contentProcessed\":81528,\"contentAdded\":0,\"contentUpdated\":0,\"contentDeleted\":0,\"pdfSlices\":0,\"pdfDocCount\":0,\"exceptionCount\":0,\"generalExceptionCount\":0,\"warningCount\":0,\"processorFailureCount\":640,\"generalizedFailureCount\":640,\"heritrixErrorCount\":0,\"duplicateItemCount\":0,\"duplicateReportRelativeFilename\":null,\"jobId\":-1}] ================================"
| eval _raw="{\"events\":".rtrim(_raw,"=")."}"
| spath path=events{} output=events
| mvexpand events
| eval _raw=events
| fields _raw
| spath
| eval startTime=round(startTime/1000,3)
| eval endTime=round(endTime/1000,3)
| eval duration=tostring(endTime-startTime,"duration")
| fields hostname contentSourceName incremental startTime endTime duration reportStatus documentsFound documentsFailed
| fields - _*
| fieldformat startTime=strftime(startTime,"%Y-%m-%d %H:%M:%S.%Q")
| fieldformat endTime=strftime(endTime,"%Y-%m-%d %H:%M:%S.%Q")
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!