Dashboard studio: join data from basesearch

[Ste]

Dear experts

I'm trying to move old xml dashboards to Dashboard Studio. Now I'm running into issues with a join which uses a base search. 

I've created a basesearch
Data_BaseSearch
Datasource_id=ds_Zwp0QfAJ

index="pm-azlm_internal_prod_events" sourcetype="azlmj" 
d_id IN (DIAG_162, DIAG_164, DIAG_165, DIAG_166, DIAG_218, DIAG_219, DIAG_220, DIAG_221, DIAG_222, DIAG_224, DIAG_225, DIAG_226, DIAG_227, DIAG_228, DIAG_229, DIAG_234) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) 
[| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]

Afterwards I'm creating the table data with

| stats count(eval(d_id IN ("DIAG_162", "DIAG_164", "DIAG_165", "DIAG_166"))) AS "ZP_Def" 
	count(eval(d_id IN ("DIAG_218", "DIAG_219"))) AS "UART" 
	count(eval(d_id IN ("DIAG_220", "DIAG_221", "DIAG_222"))) AS "DEF_W" 
	count(eval(d_id IN ("DIAG_224", "DIAG_225", "DIAG_226"))) AS "Drift_W" 
	count(eval(d_id IN ("DIAG_227", "DIAG_228", "DIAG_229"))) AS "WPoZ" 
	count(eval(d_id IN ("DIAG_234"))) AS "Versatz" 
	by ocp fr el
| table ocp fr el "ZP_Def" "UART" "DEF_W" "Drift_W" "WPoZ" "Versatz"

Until here everything works. 

Now the challenge: for every counter I need to know date/time of the last event. 

My current draft for this is to do a join (per counter, below is only one prototype shown) in which I search the needed timestamp to be joined to the already existing data.

The join should reuse the data generated by the basesearch, as there in is already a lot of (token based) filtering I don't won't to redo for every counter/ join again. 

| stats count(eval(d_id IN ("DIAG_162", "DIAG_164", "DIAG_165", "DIAG_166"))) AS "ZP_Def" 
	count(eval(d_id IN ("DIAG_218", "DIAG_219"))) AS "UART" 
	count(eval(d_id IN ("DIAG_220", "DIAG_221", "DIAG_222"))) AS "DEF_W" 
	count(eval(d_id IN ("DIAG_224", "DIAG_225", "DIAG_226"))) AS "Drift_W" 
	count(eval(d_id IN ("DIAG_227", "DIAG_228", "DIAG_229"))) AS "WPoZ" 
	count(eval(d_id IN ("DIAG_234"))) AS "Versatz" 
	by ocp fr el

| join ocp fr el 
    [ search base=ds_Zwp0QfAJ
    | where d_id IN (DIAG_162, DIAG_164, DIAG_165, DIAG_166)
    | stats latest(_time) as last_ZP_Def by ocp fr el
    | eval last_ZP_Def=strftime(last_ZP_Def,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_ZP_Def]

| table ocp fr el "ZP_Def" last_ZP_Def "UART" "DEF_W" "Drift_W" "WPoZ" "Versatz"

The question is: how to reference the basesearch in the join?  Nor basesearch name or the datasource_id of the basesearch. 



Addon after the first feedback from different users

First of all thank you for your help. 

@yuanliu : I don't think what I'm doing can be done as chain search. 

@livehybrid : just one field doesn't help

The current (brute force) solution looks like: 

```***** First get all the lines related to the error codes in question ```
index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_162, DIAG_164, DIAG_165, DIAG_166, DIAG_218, DIAG_219, DIAG_220, DIAG_221, DIAG_222, DIAG_224, DIAG_225, DIAG_226, DIAG_227, DIAG_228, DIAG_229, DIAG_234) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]


```***** Multiple error codes do belong to the same error category```
```***** Count per category the number of errors received```
| stats count(eval(d_id IN ("DIAG_162", "DIAG_164", "DIAG_165", "DIAG_166"))) AS ZP_Def count(eval(d_id IN ("DIAG_218", "DIAG_219"))) AS UART count(eval(d_id IN ("DIAG_220", "DIAG_221", "DIAG_222"))) AS DEF_W count(eval(d_id IN ("DIAG_224", "DIAG_225", "DIAG_226"))) AS Drift_W count(eval(d_id IN ("DIAG_227", "DIAG_228", "DIAG_229"))) AS WPoZ count(eval(d_id IN ("DIAG_234"))) AS Versatz by ocp fr el 

```***** Per error category we want to know when the last error was recorded```
| join ocp fr el type=outer 
    [ search index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_162, DIAG_164, DIAG_165, DIAG_166) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]
    | stats latest(_time) as last_ZP_Def by ocp fr el
    | eval last_ZP_Def=strftime(last_ZP_Def,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_ZP_Def]

| join ocp fr el type=outer 
    [ search index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_218, DIAG_219) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]
    | stats latest(_time) as last_UART_Def by ocp fr el
    | eval last_UART_Def=strftime(last_UART_Def,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_UART_Def]

| join ocp fr el type=outer 
    [ search index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_220, DIAG_221, DIAG_222) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]
    | stats latest(_time) as last_DEF_W by ocp fr el
    | eval last_DEF_W=strftime(last_DEF_W,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_DEF_W]
    
| join ocp fr el type=outer 
    [ search index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_224, DIAG_225, DIAG_226) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]
    | stats latest(_time) as last_Drift_W by ocp fr el
    | eval last_Drift_W=strftime(last_Drift_W,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_Drift_W]

| join ocp fr el type=outer 
    [ search index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_227, DIAG_228, DIAG_229) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]
    | stats latest(_time) as last_WPoZ by ocp fr el
    | eval last_WPoZ=strftime(last_WPoZ,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_WPoZ]
    
| join ocp fr el type=outer 
    [ search index="pm-azlm_internal_prod_events" sourcetype="azlmj" d_id IN (DIAG_234) ocp IN ($t_ocp$) fr IN ($t_fr$) el IN ($t_el$) [| inputlookup pm-azlm-reg-ocp-team | search team IN ($t_team$) | fields ocp ]
    | stats latest(_time) as last_Versatz by ocp fr el
    | eval last_Versatz=strftime(last_Versatz,"%Y-%m-%d %H:%M:%S")
    | fields ocp fr el last_Versatz]

```***** Finally the table```

| table ocp fr el ZP_Def last_ZP_Def UART last_UART_Def DEF_W last_DEF_W Drift_W last_Drift_W WPoZ last_WPoZ Versatz last_Versatz

There is a search at the top reads all the data in. 

In every join just a subset of the data is searched again, which is potentially already available from the search at the top. 

Therefore I came to the idea to reuse the search on the top as a base search with filtering the individual error codes in the joins.  From my idea, this would reduce the number of searches executed, and therefore speed up dashboard loading. But to be honest, I might be wrong!

@bowesmana : I'm not looking for the latest message at all, I'm Looking for the latest message of a group of specific error codes.  Something like 

| eventstats max(_time) as last_ZP_Def by ocp fr el where d_id IN ("DIAG_162", "DIAG_164", "DIAG_165", "DIAG_166")

would in fact replace one of my joins, but I haven't found a way to properly implement this in SPL. 

[yuanliu]

Let's go back to the basics: To ask an answerable data analytics question, follow these golden rules; nay, call them the four commandments:

• Illustrate data input (in raw text, anonymize as needed), whether they are raw events or output from a search (SPL that volunteers here do not have to look at).
• Illustrate the desired output from illustrated data.
• Explain the logic between illustrated data and desired output without SPL.
• If you also illustrate attempted SPL, illustrate actual output and compare with desired output, explain why they look different to you if that is not painfully obvious.

The last point is perhaps moot because you do not have error.  But you still need to explain what is the difference between what you want achieved is different from what chain search is designed for.

[yuanliu]

I'm not sure if I fully understand the question.  But first of all, do not use join.  Second, if you must use join, just create a chain search to invoke that join.  Place your first search in a main search - in the editor, this is called a Search.  When you create chain search, the editor asks you to select a parent search.

As you have already set up the main search, all you do is to put the rest of search into a chain search that chains to that main search.  You do not "reference" a base search.  You chain to it.  Like this

After you make that selection, your main search will be displayed in a grayed area

Thirdly, do not use join if you can help it.

[bowesmana]

You can't reference a base search from another search like that, but why don't you just calculate the latest time in the base search at the beginning with

| eventstats max(_time) as last_ZP_Def by ocp fr el

then you will have that field available to every event, then your stats command in your chained search can use that field.

I'm not sure why you have the base search, because you are using based searches not in the way intended, i.e. you don't have a transforming search in your base search and you don't specify a fields statement either, so you are simply collecting raw events, so that is quite possibly going to make the dashboard slower, particularly if you have a large dataset.

You should at least be doing this (I added the max time calc from above)

| fields ocp fr el d_id last_ZP_Def

See this

https://help.splunk.com/en/splunk-enterprise/create-dashboards-and-reports/dashboard-studio/9.1/use-...

Note also that doing an eventstats on raw data can be expensive.

[livehybrid]

HI @Ste 

If you edit your base search and then tick the box to "Access search results or metadata" then you can use an field from the search elsewhere in other searches/panels 

e.g. you can then do $imgSearch:result.<fieldName>$ in this case 

Would this give you what you need?

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[Ste]

Hey @livehybrid 

The result I'm looking for can maybe be built on your advice, but at the moment I don't see the final solution. 

In my case

$Data_BaseSearch:result.d_id$ 

would give me all values from the field d_id. 

In there I would need to filter for specific values e.g.

d_id IN ("DIAG_162", "DIAG_164", "DIAG_165", "DIAG_166")

and from the resulting values I finally need the 

latest(_time) as last_ZP_Def by ocp fr el

This means I would need d_id, _time,  ocp,  fr , el from the basesearch, and not only a single field.

|fields ocp fr el last_ZP_Def

I would have all data to be used by the join type=outer to be appended to my other data.

Thank you for your help

[livehybrid]

Hi @Ste 

I'm not 100% sure what the end goal is, but actually when you use $Data_BaseSearch:result.d_id$ it will only return the d_id for the first event! There are some quirky things you can do to pull them together (e.g. using stats) but you'll be in the same position for all the fields.

You could look at using the "tojson" command which will turn your fields into JSON like this:

{"count":596853,"host":"cultivar","source":["/opt/splunkforwarder/var/log/splunk/health.log","/opt/splunkforwarder/var/log/splunk/metrics.log","/opt/splunkforwarder/var/log/splunk/splunkd.log","/opt/splunkforwarder/var/log/splunk/splunkd.log.1"]}

and then you can load that into your search - but it might not be the most efficient approach, I think it really depends on the end-goal. 

Could you use a saved searches for your base search and then use the "loadjob" command to get the data?

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing