Splunk Search

Append search result

rangarbus
Path Finder

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.

Labels (3)
0 Karma
1 Solution

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

 

Screenshot 2021-06-14 at 9.58.41 PM.png

 

Thanks
KV
▄︻̷̿┻̿═━一

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

View solution in original post

kamlesh_vaghela
SplunkTrust
SplunkTrust

@rangarbus 

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

KV

0 Karma

rangarbus
Path Finder

@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

rangarbus_0-1623600605836.png

 

Tags (1)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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

 

Screenshot 2021-06-14 at 9.58.41 PM.png

 

Thanks
KV
▄︻̷̿┻̿═━一

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

rangarbus
Path Finder

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"

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

rangarbus
Path Finder

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

 

rangarbus_0-1623774048509.png

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@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
Path Finder

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

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Glad to help you @rangarbus 

 

Happy Splunking

 

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...