topic Append search result in Splunk Search

Append search result

rangarbus — Sun, 13 Jun 2021 04:03:26 GMT

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.

Re: Append search result

kamlesh_vaghela — Sun, 13 Jun 2021 07:02:34 GMT

@rangarbus

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

Re: Append search result

rangarbus — Mon, 14 Jun 2021 15:50:17 GMT

@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

Re: Append search result

kamlesh_vaghela — Mon, 14 Jun 2021 16:28:56 GMT

@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.

Re: Append search result

rangarbus — Tue, 15 Jun 2021 07:02:34 GMT

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"

Re: Append search result

kamlesh_vaghela — Tue, 15 Jun 2021 07:43:13 GMT

@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.

Re: Append search result

rangarbus — Tue, 15 Jun 2021 16:22:30 GMT

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.

Re: Append search result

kamlesh_vaghela — Tue, 15 Jun 2021 17:30:03 GMT

@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.

Re: Append search result

rangarbus — Wed, 16 Jun 2021 02:28:08 GMT

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

Re: Append search result

kamlesh_vaghela — Wed, 16 Jun 2021 03:30:25 GMT

Glad to help you @rangarbus

Happy Splunking