Append search result

[rangarbus]

Hello Folks,

In my current use case i receive events with 3 fields as json .  

{ 'tid''123', ' 'type': 'R', 'app_name': 'app-1'}

Here app_name (app-1 to app-6) are micro services in which tid is generated by app-1 and passed on to other apps.

Each app generate 2 events - with type as R and D.

So for a transaction there will be 12 events with unique tid. 

I want generate a dashboard just out of time range the customer selects so that they get a tabular panel with below columns with the time the event was received.

tid | app-1 R | app-1 D | time took| •••• for each app-*

If I understand, I need to have 2 searches. (1) get unique tid in app-1 and (2) using the unique tid , search app events and form the above table 

Can you pls help me to frame this query as I am stuck with append query.

[kamlesh_vaghela]

@rangarbus 

Please share some sample of multiple apps and the expected output from that sample events.

KV

[rangarbus]

@kamlesh_vaghela 

Example Events are as below.

There could be up to app-1 to app-6 (example here shows app-1 to app-3).

{ "log_processed": { "app_name": "app-1", message: {"type":"R","bk":"Cust-1|Order-2"}, "tId": "f7ac16537e4e89d16c0f5b8c83bd45f2" }
{ "log_processed": { "app_name": "app-1", message: {"type":"D","bk":"Cust-1|Order-2"}, "tId": "f7ac16537e4e89d16c0f5b8c83bd45f2" }
{ "log_processed": { "app_name": "app-2", message: {"type":"R","bk":"Cust-1|Order-2"}, "tId": "f7ac16537e4e89d16c0f5b8c83bd45f2" }
{ "log_processed": { "app_name": "app-2", message: {"type":"D","bk":"Cust-1|Order-2"}, "tId": "f7ac16537e4e89d16c0f5b8c83bd45f2" }
{ "log_processed": { "app_name": "app-3", message: {"type":"R","bk":"Cust-1|Order-2|1"}, "tId": "f7ac16537e4e89d16c0f5b8c83bd45f2" }
{ "log_processed": { "app_name": "app-3", message: {"type":"D","bk":"Cust-1|Order-2|1"}, "tId": "f7ac16537e4e89d16c0f5b8c83bd45f2" }

Here tId is same for all events. So we need to group all events  by tId field and generate the dashboard below

[kamlesh_vaghela · Accepted solution]

@rangarbus 

Can you please try this?

YOUR_SEARCH
| rename log_processed.* as *, message.* as message_*
| fields tId app_name message_bk message_type
| stats max(eval(if(message_type="D",_time,null()))) as message_type_D min(eval(if(message_type="R",_time,null()))) as message_type_R by tId app_name
| eval diff= message_type_D - message_type_R, app_name="app_name=".app_name.". Time took (Sec)"
| chart values(diff) as diff over tId by app_name | addtotals

My Sample Search :

| makeresults | eval raw="{ \"log_processed\": { \"app_name\": \"app-1\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"app-1\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"app-2\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"app-2\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"app-3\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"app-3\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}", raw=split(raw,"@@") | mvexpand raw | rename raw as _raw | eval t= 1000 | accum t | eval _time=_time + t | fields - t
| extract
| rename log_processed.* as *, message.* as message_*
| fields tId app_name message_bk message_type
| stats max(eval(if(message_type="D",_time,null()))) as message_type_D min(eval(if(message_type="R",_time,null()))) as message_type_R by tId app_name
| eval diff= message_type_D - message_type_R, app_name="app_name=".app_name.". Time took (Sec)"
| chart values(diff) as diff over tId by app_name | addtotals

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated. 

[rangarbus]

Thank you @kamlesh_vaghela . It worked.

Only follow-up question i have is whether it is possible to sort the app_name in a specific sequence on the dashboard. ?

app_name required on sequence as "Xerox, Printer, Copier, Marker"

With the current dashboard output the app_name are sorted as "Copier, Marker, Printer , Xerox"

[kamlesh_vaghela]

@rangarbus 

I think the same search will work.. Can you please try this?

| makeresults | eval raw="{ \"log_processed\": { \"app_name\": \"Xerox\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"Xerox\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"Printer\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"Printer\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"Copier\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"Copier\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}", raw=split(raw,"@@") | mvexpand raw | rename raw as _raw | eval t= 1000 | accum t | eval _time=_time + t | fields - t
| extract
| rename log_processed.* as *, message.* as message_*
| fields tId app_name message_bk message_type
| stats max(eval(if(message_type="D",_time,null()))) as message_type_D min(eval(if(message_type="R",_time,null()))) as message_type_R by tId app_name
| eval diff= message_type_D - message_type_R, app_name="app_name=".app_name.". Time took (Sec)"
| chart values(diff) as diff over tId by app_name | addtotals

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[rangarbus]

Thank you @kamlesh_vaghela .

I got the problem, the app_name are prefixed with static string "cs-" which cause the resulted columns to be sorted in asc order rather just following the event sequence.

| makeresults | eval raw="{ \"log_processed\": { \"app_name\": \"cs-xerox\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-xerox\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-printer\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-printer\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-copier\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-copier\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}", raw=split(raw,"@@") | mvexpand raw | rename raw as _raw | eval t= 1000 | accum t | eval _time=_time + t | fields - t
| extract
| rename log_processed.* as *, message.* as message_*
| fields tId app_name message_bk message_type
| stats max(eval(if(message_type="D",_time,null()))) as message_type_D min(eval(if(message_type="R",_time,null()))) as message_type_R by tId app_name
| eval diff= message_type_D - message_type_R, app_name="app_name=".app_name.". Time took (Sec)"
| chart values(diff) as diff over tId by app_name | addtotals

Ideally, the columns should have been tId, app_name=cs-xerox, app_name=cs-printer, app_name=cs-copier - just like the event sequence.

[kamlesh_vaghela]

@rangarbus 

By default the fields are ordered alpha-numerically and field values do not override that default ordering.

But we can trick with some values. Can you please try this?

| makeresults | eval raw="{ \"log_processed\": { \"app_name\": \"cs-xerox\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-xerox\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-printer\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-printer\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-copier\", \"message\": {\"type\":\"R\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}@@{ \"log_processed\": { \"app_name\": \"cs-copier\", \"message\": {\"type\":\"D\",\"bk\":\"Cust-1|Order-2|1\"}, \"tId\": \"f7ac16537e4e89d16c0f5b8c83bd45f2\" }}", raw=split(raw,"@@") | mvexpand raw | rename raw as _raw | eval t= 1000 | accum t | eval _time=_time + t | fields - t
| extract
| rename log_processed.* as *, message.* as message_*
| fields tId app_name message_bk message_type | eval a=1 | accum a
| stats max(eval(if(message_type="D",_time,null()))) as message_type_D min(eval(if(message_type="R",_time,null()))) as message_type_R max(a) as a by tId app_name
| eval diff= message_type_D - message_type_R, app_name=(a/2)."_app_name=".app_name.". Time took (Sec)"
| chart values(diff) as diff over tId by app_name

Thanks
KV
▄︻̷̿┻̿═━一

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated.

[rangarbus]

Thank you @kamlesh_vaghela. It worked.. Really appreciate your quick help!

[kamlesh_vaghela]

Glad to help you @rangarbus 

Happy Splunking