How to add two Splunk queries output in Single Panel

[devsru]

Hi All,

I am trying to pass a token link to another dashboard panel. My requirement is when I pass Windows Server Token, it must display Windows metrics and Vice Versa. Both the OS SPL queries are different and at one point it can display the metrics from one host only. Can anyone tell me how to achieve this in one PANEL ?

Windows Host SPL

| mstats min("Processor.%_Idle_Time") as val WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=$host$ span=1m BY "instance"
| eval instance="CPU: ".instance
| eval val=100-val
| xyseries _time instance val

Unix Host SPL

|mstats max(ps_metric.pctCPU) as val WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=$host$ span=1m BY USER
| eval instance="User: ".USER
| xyseries _time instance val

[yuanliu]

One potential way is to append the two together before performing xyseries, something like

| mstats min("Processor.%_Idle_Time") as val WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=$host$ span=1m BY "instance"
| eval instance="CPU: ".instance
| eval val=100-val
| append
    [ |mstats max(ps_metric.pctCPU) as val WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=$host$ span=1m BY USER
    | eval instance="User: ".USER]
| xyseries _time instance val

[devsru]

thanks @yuanliu  The Solution seems working but i am getting extra fields. For Example In the below query I am getting results for SPL after append command then why i am seeing empty fields of the first search Filesystem, MountedON etc. 

| mstats latest(df_metric.Used_KB) as "Used_KB", latest(df_metric.Avail_KB) as "Avail_KB", latest(df_metric.UsePct) as "UsePct", WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=* (earliest="-5m" latest="now") by Filesystem, MountedOn, Type
| eval UsePct=round(UsePct,2), Used_KB=round(Used_KB), Avail_KB=round(Avail_KB) | append [
| mstats min("LogicalDisk.%_Free_Space") prestats=true WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=* span=1m AND NOT "instance"="_Total" by instance | timechart span=1m min("LogicalDisk.%_Free_Space") as val by instance | eval val=100-val | fields - _time | head 1]

[yuanliu]

---

@devsru wrote:

thanks @yuanliu  The Solution seems working but i am getting extra fields. For Example In the below query I am getting results for SPL after append command then why i am seeing empty fields of the first search Filesystem, MountedON etc. 

| mstats latest(df_metric.Used_KB) as "Used_KB", latest(df_metric.Avail_KB) as "Avail_KB", latest(df_metric.UsePct) as "UsePct", WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=* (earliest="-5m" latest="now") by Filesystem, MountedOn, Type
| eval UsePct=round(UsePct,2), Used_KB=round(Used_KB), Avail_KB=round(Avail_KB) | append [
| mstats min("LogicalDisk.%_Free_Space") prestats=true WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=* span=1m AND NOT "instance"="_Total" by instance | timechart span=1m min("LogicalDisk.%_Free_Space") as val by instance | eval val=100-val | fields - _time | head 1]

---

This is getting all too confusing.

1. In the original question, both searches ends with xyseries.  That is why my proposed combined search ends with xyseries.   The above code has no xyseries.
2. In the original question, both searches are reduced to contain the same three fields: _time, val, and instance.  Therefore my proposed combined search contains the same three fields before append and inside append.  In the above code, only the part inside append is reduced to three fields; the part before append contains "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", and "Type".  How are these reduced to val and instance?
3. In the original question, both mstats commands have a "span=1m" clause, meaning that output would contain rows in 1-minute increment of the search window.  That's why my proposed combined search runs xyseries against _time as specified in the two original searches.  The above code has no span=1m in the main search, and explicitly removes _time from the appended search.  This totally changes the question.  Therefore whatever result you get from the above code has no relationship to the original question.  You should start a different question, carefully describe/illustrate the data after mstats, carefully illustrate what kind of results you are looking for, and describe the logic between data and desired results.
4. In the form of the above code, your header would have contained "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", and "Type" (all from the first search), "val", and "instance" (from appended search).  This would have give you header rows  "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", "Type", "val", and "instance", NOT  "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", "Type", "C:", "D:", "E:", "HarddiskVolume1", and "HarddiskVolume2" as the screenshot shows.  In other words, the above code could not have produced the screenshot.  What is the real code that produced the that screenshot?
5. If, as the screenshot shows,  "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", and "Type" are all blank, it only means that the search before append gives all null output, i.e.,

| mstats latest(df_metric.Used_KB) as "Used_KB", latest(df_metric.Avail_KB) as "Avail_KB", latest(df_metric.UsePct) as "UsePct", WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=* (earliest="-5m" latest="now") by Filesystem, MountedOn, Type
| eval UsePct=round(UsePct,2), Used_KB=round(Used_KB), Avail_KB=round(Avail_KB)

is simply blank.  If so, what is the point of running it with append?

• Additionally, if you only care about removing "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", and "Type" from final display, you should run fields - "Used_KB", "Avail_KB", "UsePct", "Filesystem", "MountedOn", "Type" after append, not inside append.

Could you reconcile the above to the original question?  If the original question is not the real question, start a different question.  Carefully describe/illustrate the data after mstats, carefully illustrate what kind of results you are looking for, and describe the logic between data and desired results.

[ITWhisperer]

Field names appear if any event in your result set has that field, even if the events on that page of results do not have any values in those fields.

[devsru]

Hi @ITWhisperer  How can we ignore these fields as fields - command is not working.

[ITWhisperer]

I am not sure what you mean by "ignore" fields - this is a state of mind, you choose to ignore something.

Having said that, you can remove fields from the output using the fields command with a minus

| fields - FileSystem MountedOn

[devsru]

@ITWhisperer That's what fields - command not working with append. I am getting the same result as in screenshot after using fields - command. Strange

| mstats latest(df_metric.Used_KB) as "Used_KB", latest(df_metric.Avail_KB) as "Avail_KB", latest(df_metric.UsePct) as "UsePct", WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=VMP-STATA-01 (earliest="-5m" latest="now") by Filesystem, MountedOn, Type
| eval UsePct=round(UsePct,2), Used_KB=round(Used_KB), Avail_KB=round(Avail_KB) | append [
| mstats min("LogicalDisk.%_Free_Space") prestats=true WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=VMP-STATA-01 span=1m AND NOT "instance"="_Total" by instance | timechart span=1m min("LogicalDisk.%_Free_Space") as val by instance | eval val=100-val | fields -  Filesystem, MountedOn, Type | head 1]

[ITWhisperer]

The append command is appending more events to the pipeline of events - the events with Filesystem, MountedOn, etc. are introduced to the pipeline by the mstats before the append, and the fields - is only removing the fields from the events being introduced by the append; it does not apply to the whole pipeline since it is still within the square brackets.

[devsru]

@ITWhisperer so how can we ignore the fields ? They are not looking nice in the dashboard. I can’t remove before append because some searches are producing data using that SPL

[ITWhisperer]

What do you mean by "ignore" the fields?

[devsru]

@ITWhisperer @yuanliu Below attached is the final result with append command. I want to ignore the fields with no data. How can it be done as fields - is not working.

[ITWhisperer]

Please share the search which gives this result

[devsru]

@ITWhisperer 

| mstats latest(df_metric.Used_KB) as "Used_KB", latest(df_metric.Avail_KB) as "Avail_KB", latest(df_metric.UsePct) as "UsePct", WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=$host$ (earliest="-5m" latest="now") by Filesystem, MountedOn, Type
| eval UsePct=round(UsePct,2), Used_KB=round(Used_KB), Avail_KB=round(Avail_KB) |append [ | mstats min("LogicalDisk.%_Free_Space") prestats=true WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=$host$ span=1m AND NOT "instance"="_Total" by instance | timechart span=1m min("LogicalDisk.%_Free_Space") as val by instance | eval val=100-val | fields - Filesystem, MountedOn, Type | head 1]

[ITWhisperer]

As I said earlier, you have the fields - inside the square brackets of the append command so it doesn't affect the fields from the first part of the search. You need to move it to after the closing square bracket if you want it to affect all the events.

[devsru]

@ITWhisperer The problem is If I remove the fields after append then it will delete these fields for the first SPL which used these fields. Tricky

[ITWhisperer]

Yes. So what do you mean by "ignore"?

What are you expecting to see?

[devsru]

@ITWhisperer  There are two SPL queries combined. I am passing a token from another dashboard. When it is Linux server token, I want to see first SPL results, when it is Windows server Token , I want to see second SPL result but I want to retain one panel only and no combination of fields from these two searches which are appended.

[ITWhisperer]

OK That is clearer - try something like this (assuming your token is called type and has either "Windows" or "Linux")

| mstats latest(df_metric.Used_KB) as "Used_KB", latest(df_metric.Avail_KB) as "Avail_KB", latest(df_metric.UsePct) as "UsePct", WHERE (`itsi_entity_type_ta_nix_metrics_indexes`) AND host=$host$ (earliest="-5m" latest="now") by Filesystem, MountedOn, Type
| eval UsePct=round(UsePct,2), Used_KB=round(Used_KB), Avail_KB=round(Avail_KB) 
| where $type|s$ == "Windows"
|append [ | mstats min("LogicalDisk.%_Free_Space") prestats=true WHERE (`itsi_entity_type_windows_metrics_indexes`) AND host=$host$ span=1m AND NOT "instance"="_Total" by instance | timechart span=1m min("LogicalDisk.%_Free_Space") as val by instance | eval val=100-val | fields - Filesystem, MountedOn, Type | head 1
| where $type|s$ == "Linux"]

[devsru]

@ITWhisperer  If you can see the SPL's, I am passing a single token $host$ so If I am clicking on either OS, it automatically check which SPL it belongs to. This seems to be working except the Windows token where we are seeing additional fields apart from the correct data.

[ITWhisperer]

Either pass an additional token with the server type or find a way to determine the type from either the host token or the information available in the indexes