Splunk Search

how to customize a report or dashboard as i mentioned below?

Shan
Builder

Hai All,

I need to achieve a Dashboard or Report in the format I mentioned below. Here Measures, Detail, value are static fields. There won’t be any changes in the values they are showing. But filed Reached is a dynamic field, based on the dropdown value whatever I’m providing. Data in Reached field need to change. How to achieve such a table format and Is it possible to write a SPL query to each cell of Reached filed and achieve it? Kindly help out in achieving it.

alt text

Thanks in advance …

Shankar ..

0 Karma
1 Solution

poete
Builder

Hello @shankarananth,

this is the code of the dashboard with the look of your table.

Hope this helps

<dashboard>
  <search>
    <query>|makeresults 
| eval Value1=3.8|eval Value2=100|eval Value3=85|eval Value4=87|eval Value5=75|eval Value6="Yes"|eval Value7="No"|eval Value8=40|eval Value9=44|eval Value10=54|eval Value11="Reach"
</query>
<done>
  <set token="Value1">$result.Value1$</set>
  <set token="Value2">$result.Value2$</set>
  <set token="Value3">$result.Value3$</set>
  <set token="Value4">$result.Value4$</set>
  <set token="Value5">$result.Value5$</set>
  <set token="Value6">$result.Value6$</set>
  <set token="Value7">$result.Value7$</set>
  <set token="Value8">$result.Value8$</set>
  <set token="Value9">$result.Value9$</set>
  <set token="Value10">$result.Value10$</set>
  <set token="Value11">$result.Value11$</set>
</done>
  </search>
  <row>
    <panel>
      <html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 5px;
    text-align: left;    
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th>Measures</th>
    <th>DB</th> 
    <th>Detail</th>
    <th>Value</th>
    <th>Reached</th>
  </tr>
  <tr>
    <td rowspan="6">DB Connect</td>
    <td>Oracle</td>
    <td>Data Processed volume</td>
    <td><3.5%</td>
    <td>$Value1$</td>
    <!--<td rowspan="2">Logs</td> 
    <td rowspan="2">lookup</td>-->
  </tr>
  <tr>
    <td rowspan="4">SQL Server</td>
    <td rowspan="4">Data Processed volume</td>
    <td>100%</td>
    <td>$Value2$</td>
  </tr>
  <tr>
    <td>>80%</td>
    <td>$Value3$</td>
  </tr>
  <tr>
    <td>>60%</td>
    <td>$Value4$</td>
  </tr>
  <tr>
    <td>>40%</td>
    <td>$Value5$</td>
  </tr>
  <tr>
    <td>DB2</td>
    <td>Data Processed volume</td>
    <td><=3</td>
    <td>$Value6$</td>
  </tr>
  <tr>
    <td rowspan="2">Logs</td>
    <td>Unstructured</td>
    <td>Data Processed volume</td>
    <td>>=80%</td>
    <td>$Value7$</td>
  </tr>
  <tr>
    <td>Structured</td>
    <td>Data Processed volume</td>
    <td>>=60%</td>
    <td>$Value8$</td>
  </tr>
  <tr>
    <td rowspan="3">Lookup</td>
    <td>Country</td>
    <td>Number of countries</td>
    <td>>=40%</td>
    <td>$Value9$</td>
  </tr>
  <tr>
    <td>State</td>
    <td>Number of State</td>
    <td><=3%</td>
    <td>$Value10$</td>
  </tr>
  <tr>
    <td>Continent</td>
    <td>Number of Continents</td>
    <td>>=80%</td>
    <td>$Value11$</td>
  </tr>
</table>

</body>
</html>
    </panel>
  </row>
</dashboard>

View solution in original post

poete
Builder

Hello @shankarananth,

this is the code of the dashboard with the look of your table.

Hope this helps

<dashboard>
  <search>
    <query>|makeresults 
| eval Value1=3.8|eval Value2=100|eval Value3=85|eval Value4=87|eval Value5=75|eval Value6="Yes"|eval Value7="No"|eval Value8=40|eval Value9=44|eval Value10=54|eval Value11="Reach"
</query>
<done>
  <set token="Value1">$result.Value1$</set>
  <set token="Value2">$result.Value2$</set>
  <set token="Value3">$result.Value3$</set>
  <set token="Value4">$result.Value4$</set>
  <set token="Value5">$result.Value5$</set>
  <set token="Value6">$result.Value6$</set>
  <set token="Value7">$result.Value7$</set>
  <set token="Value8">$result.Value8$</set>
  <set token="Value9">$result.Value9$</set>
  <set token="Value10">$result.Value10$</set>
  <set token="Value11">$result.Value11$</set>
</done>
  </search>
  <row>
    <panel>
      <html>
<head>
<style>
table, th, td {
    border: 1px solid black;
    border-collapse: collapse;
}
th, td {
    padding: 5px;
    text-align: left;    
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th>Measures</th>
    <th>DB</th> 
    <th>Detail</th>
    <th>Value</th>
    <th>Reached</th>
  </tr>
  <tr>
    <td rowspan="6">DB Connect</td>
    <td>Oracle</td>
    <td>Data Processed volume</td>
    <td><3.5%</td>
    <td>$Value1$</td>
    <!--<td rowspan="2">Logs</td> 
    <td rowspan="2">lookup</td>-->
  </tr>
  <tr>
    <td rowspan="4">SQL Server</td>
    <td rowspan="4">Data Processed volume</td>
    <td>100%</td>
    <td>$Value2$</td>
  </tr>
  <tr>
    <td>>80%</td>
    <td>$Value3$</td>
  </tr>
  <tr>
    <td>>60%</td>
    <td>$Value4$</td>
  </tr>
  <tr>
    <td>>40%</td>
    <td>$Value5$</td>
  </tr>
  <tr>
    <td>DB2</td>
    <td>Data Processed volume</td>
    <td><=3</td>
    <td>$Value6$</td>
  </tr>
  <tr>
    <td rowspan="2">Logs</td>
    <td>Unstructured</td>
    <td>Data Processed volume</td>
    <td>>=80%</td>
    <td>$Value7$</td>
  </tr>
  <tr>
    <td>Structured</td>
    <td>Data Processed volume</td>
    <td>>=60%</td>
    <td>$Value8$</td>
  </tr>
  <tr>
    <td rowspan="3">Lookup</td>
    <td>Country</td>
    <td>Number of countries</td>
    <td>>=40%</td>
    <td>$Value9$</td>
  </tr>
  <tr>
    <td>State</td>
    <td>Number of State</td>
    <td><=3%</td>
    <td>$Value10$</td>
  </tr>
  <tr>
    <td>Continent</td>
    <td>Number of Continents</td>
    <td>>=80%</td>
    <td>$Value11$</td>
  </tr>
</table>

</body>
</html>
    </panel>
  </row>
</dashboard>

View solution in original post

Shan
Builder

@poete

Thanks for your reply i can able to get the html view of the report now. But when I'm using real query in the query environment its not displaying the value as expected , but its displaying the $Totals$ as a value in the dashboard

    <dashboard>


     <search>
     <query>  index=** sourcetype=**
   | eval Month=strftime(strptime(date,"%Y-%m-%d"),"%B")
| eval Year=strftime(strptime(date,"%Y-%m-%d"),"%Y") 
| search Month="April" AND Year="2018"
| eval Totals=if(isnull(error),0,error) 
 </query>
 <done>
   <set token="Totals">$result.Totals$</set>

 </done>
   </search>

   <search>
     <query>|makeresults 
 | eval Value1=3.8|eval Value2=100|eval Value3=85|eval Value4=87|eval Value5=75|eval Value6="Yes"|eval Value7="No"|eval Value8=40|eval Value9=44|eval Value10=54|eval Value11="Reach"
 </query>
 <done>
   <set token="Value2">$result.Value2$</set>
   <set token="Value3">$result.Value3$</set>
   <set token="Value4">$result.Value4$</set>
   <set token="Value5">$result.Value5$</set>
   <set token="Value6">$result.Value6$</set>
   <set token="Value7">$result.Value7$</set>
   <set token="Value8">$result.Value8$</set>
   <set token="Value9">$result.Value9$</set>
   <set token="Value10">$result.Value10$</set>
   <set token="Value11">$result.Value11$</set>
 </done>
   </search>
   <row>
     <panel>
       <html>
 <head>
 <style>
 table, th, td {
     border: 1px solid black;
     border-collapse: collapse;
 }
 th, td {
     padding: 5px;
     text-align: left;    
 }
 </style>
 </head>
 <body>
 <table style="width:100%">
   <tr>
     <th>Measures</th>
     <th>DB</th> 
     <th>Detail</th>
     <th>Value</th>
     <th>Reached</th>
   </tr>
   <tr>
     <td rowspan="6">DB Connect</td>
     <td>Oracle</td>
     <td>Data Processed volume</td>
     <td><3.5%</td>
     <td>$Totals$</td>
     <!--<td rowspan="2">Logs</td> 
     <td rowspan="2">lookup</td>-->
   </tr>
   <tr>
     <td rowspan="4">SQL Server</td>
     <td rowspan="4">Data Processed volume</td>
     <td>100%</td>
     <td>$Value2$</td>
   </tr>
   <tr>
     <td>>80%</td>
     <td>$Value3$</td>
   </tr>
   <tr>
     <td>>60%</td>
     <td>$Value4$</td>
   </tr>
   <tr>
     <td>>40%</td>
     <td>$Value5$</td>
   </tr>
   <tr>
     <td>DB2</td>
     <td>Data Processed volume</td>
     <td><=3</td>
     <td>$Value6$</td>
   </tr>
   <tr>
     <td rowspan="2">Logs</td>
     <td>Unstructured</td>
     <td>Data Processed volume</td>
     <td>>=80%</td>
     <td>$Value7$</td>
   </tr>
   <tr>
     <td>Structured</td>
     <td>Data Processed volume</td>
     <td>>=60%</td>
     <td>$Value8$</td>
   </tr>
   <tr>
     <td rowspan="3">Lookup</td>
     <td>Country</td>
     <td>Number of countries</td>
     <td>>=40%</td>
     <td>$Value9$</td>
   </tr>
   <tr>
     <td>State</td>
     <td>Number of State</td>
     <td><=3%</td>
     <td>$Value10$</td>
   </tr>
   <tr>
     <td>Continent</td>
     <td>Number of Continents</td>
     <td>>=80%</td>
     <td>$Value11$</td>
   </tr>
 </table>

 </body>
 </html>
     </panel>
   </row>
 </dashboard>
0 Karma

poete
Builder

@shankarananth,
what is your query trying to get as a result? I do not understand the error in eval Totals=if(isnull(error),0,error). To me, the query is not valid

0 Karma

Shan
Builder

@poete
when i run the query in search window. I'm getting result. But while using inside the i dont know what is happening ..

0 Karma

poete
Builder

It works well on my side. Maybe it is a problem of refressing the dashboard, as both queries must have different execution times.

0 Karma

Shan
Builder

@poete - First of all Thank you very much. Yes it's working for me also now .. when i use one search query as index and another one as |makeresults one, as i mentioned above its not working. But when i use all the search query with my index in both the places it's working fine ..

Thank you very much ...

0 Karma

Shan
Builder

@poete - one last question .. how can i change the background color of the field based on the value which I'm receiving .. For example if the value (SQL Server,Detail=Data Processed volume,Value=100% then Green color) else if the value (SQL Server,Detail=Data Processed volume,Value>=80 then Yellow color) else if the value (SQL Server,Detail=Data Processed volume,Value>=60 then light red color) else the value (SQL Server,Detail=Data Processed volume,Value<=59 then red color) .. How to achieve it ..

0 Karma

poete
Builder

Hi. In case your first four columns are static, why not create an html table and customize it in order to get the right look ad feel and then run as many searches as needed to set tokens with the values to be displayed on the last column?
This would I think answer your question, and enable the interaction with the dropdown value you mentioned.

0 Karma

Shan
Builder

@poete - Please add some more points to your answer. only Reached filed is going to generate values rest of all are static .Even i can hard code the values for them .. can you kindly add some more point on achieving the html table format .. Its new to me ..

0 Karma

hgehrts_splunk
Splunk Employee
Splunk Employee

Kind of hard to come up with an answer without seeing your raw data.
I'd say, doing something like
stats sum(Reached) as Reached by Measure Detail value
could be a first start.

0 Karma

Shan
Builder

@ hgehrts [Splunk]

Thanks for your reply ..

No i don't believe it will help me.. Other than the Reached filed rest of the field i need as a static table. For example if its an html view also I'm ok with it .. its not going to change .. But in the each cell of the Reached field i need to write a separate query and bring up the result..

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!