Splunk : Using DrillDown to connect two dashboards

Splunk does provide an option to connect 2 dashboards using drilldown option, that means when we click on any dashboard output it will open linked dashboard rather than search string. We can use drilldown option to map with specific dashboards or can take input from one dashboard and pass as variables to the next one.

  • Using Static Link:-  In order to create the drilldown mapping for any given panel on the dashboard we just simply add the following piece of code replacing mapped dashboard address.

setting drilldown option to all for generic cases

<option name=“drilldown”>all</option>

For Charts: – 

<option name=“charting.drilldown”>all</option>

For Tables: – 

<option name=“drilldown”>cell</option>

<drilldown>

<link> /app/APP_NAME/Dashboard_Name </link>

</drilldown>

  • Using with fix tokens:-

<option name=“drilldown”>all</option>

<drilldown>

<link> /app/APP_NAME/Dashboard_Name?form.tkn_Time.earlier=-60m@m&form.tkn_Time.latest=@m </link>

</drilldown>

Here @m will provide start time from start of the minute and again in the latest till the end of last minute.

  • Using dynamic field values:-  We can also use data published in the current dashboard as input for another dashboard. for e.g. we have dashboard providing the summary of placed orders at given time and another dashboard providing details of individual order from order id. Using drilldown we can link this 2 dashboard allowing users to any order data just by clicking order-id rather than searching and opening two dashboards every time.

 

<drilldown target=“_blank”>

<condition field=“ORDERID”>

<set token=“src_token”>$row.ORDERID$</set>

<link>

<![CDATA[ /

/app/APP_NAME/Dashboard_Name?form.tkn_Time.earliest=$tkn_Time.earliest$&form.tkn_Time.latest=$tkn_Time.latest$&form.tkn_searchID=$src_token$

]]>

</link>

</condition> </drilldown>

</drilldown>

In above examples token values can be changed as per your requriment.

Advertisements

Oracle : Basic Tips for writing optimised SQL Queries

SQL queries are one of the core parts in every of the RDBMS-based applications. Most of the times a bad SQL query is the reason for performance and data integrity issues. So how can we build optimized SQL queries?

Every query is different from each other. Therefore we cannot follow the same approach for writing SQL queries. Some of the basic steps are listed below.

  • Understand the expectation of the problem and then analyze respective application tables, columns and relevant primary and foreign key mappings.
  • Always try to use indexed column/primary key in your query. If we do not have this information then you can consult application DBAs to get index information. Most of the times query engine we are using provides functionality for viewing table related information.
  • Avoid using hard parsing means the use of static values rather than passing bind variables. for e.g. in the simple scenario, the application is validating username without passing as bind variables. You can refer to the using bind variables blog for the detailed explanation. This sometimes slows down application performance if usage of the same SQL is high. It will also expose passed values in the SQL query.
  • Forecast growth of your key tables and use right index.
  • Avoid using big sub-queries inside “IN Clause”. In the case of fetching large data, database executes sub-query every time for each fetch. To avoid this use EXISTS clause.
  • For large tables avoid using like statements with % tag on both ends. If it is part of application design then suggest creating function index on the column where like clause is used.
  • Always give preference to direct joins and if you notice redundant information in the tables.

These are some the basic information we need to be aware of writing basic queries. For some people, it could simple, but during my IT career, I have experienced many times lack of usage of basic principles. I will be writing another post on usage of commits and group by clauses.

Best SSL Certificate Checking Site

At Big organizations, we can find the range of certificates used internally or externally to validate the authenticity of the  service/website or third party web services. Mostly the root certificate maps to the organization level having expiry date generally for 20-25 years, followed by a chain of certificates. The final certificate which is actually exposed to the service level most of the times having small expiry dates such as 6 months of 1 year. But in order to authenticated by browser completely mainly with user facing certificates the certificates must be linked in the correct order. Also, all certificates in the chain should be active. While working on the production issues I found one of the external site doing the same job with detailed level analyze. It takes URL name as input and validates all certificates deployed on the chain providing expiry dates. Please find below link to the same.

SSL Certificate Checker

I have just validated my own site and hopefully showing everything green 🙂

sslchecker

If there are any issues with the certificates, Certificate Authority etc then the output will provide such information with amber or red flags. Please find below one of such example

sslerro

Avoiding SQL Injection attacks in Coding

SQL Injection in one of the commonly seen of attacks nowadays. SQL injection means when attacker appends a piece of SQL code into the browser script or as part of the input parameter in order to gain access to the side. Most of the times we fail to put basic validations in the code leaving loopholes which can be easily targeted. Therefore when it comes to validation we should not be allowing any special characters which can be used as a valid SQL execution result in giving access to the attacker. But in some cases, it’s a mandate to allow special characters in the input field such as password field. In such scenario, we must ensure that we are taking complete input value as a text which is explained below.

Here we can see an example for login form where validations are not very good. In this case, the application is taking username/password field and checking whether combination exists at the  DB level and then allows access to the user. We can consider following SQL used to validate login form where both user and password data stored in login_user table.

sqlQuery = “SELECT count(1) FROM LOGIN_USER” +
” WHERE USER_ID =  ‘” + inputUserNameSring + “‘and password = ‘ ” + inputPasswordSring + “‘” ;

If I enter User Name = test and password = test assuming that the combination is not present at DB level. The login form will not provide me access to the main form.

sql_a

But what if I enter the following string in the password field “test’ or ‘1’ = ‘1“. Since we are not filtering password string completely to text field single quote and equal to is becoming part of SQL query. From SQL point of view, this will add another OR condition which is already true which means it will return the count of all rows from the DB as below.

sql_b

As a result, application will grant access to the main form without valid user and password combination. This type of injection is most commonly used to bypass validation forms. Therefore to avoid such bugs we must convert the entire input string into the text field. In the above case, I have now replaced every single quote to additional single quote which will then consider as text and not part of SQL query. Therefore  “test’ or ‘1’ = ‘1” is now changed to  “test” or ”1” = ”1” as below and when it will get executed at DB level will not return any output.

sql_c

As seen above now we have validated input value passed in password field exactly at Database avoiding SQL injections.

The example provided above is the most basic example to explain SQL injections. Hackers do use advanced level of injections, but if we ensure that all critical input parameters are getting validated without leaving any loopholes then such type of attacks can be easily avoided.

ITIL : Developer’s Worst Nightmare ?

A developer is a person for which his/her code is like a treasure or a baby. Most of the developers are very much serious about their coding, whether he/she is fresher or 10-15 years experience person. In India, we hardly see a developer who is having 10 or 15 years experience in coding. If we find any such person then he/she will be like hardcore coder as the corporate practice doesn’t provide career growth after certain years on the technical side. They must choose a managerial option for further growth.

Anyways coming back to topic what can be developer’s worst nightmare? breakup with the girlfriend? Getting sacked? Appraisal ? Code not getting committed?  Incorrect requirement? I think the one I have seen in my lifetime is when code fails on production and on other side works perfectly fine in every single environment. Most of the techies come with the reason that code is working on dev/test/UAT environments. But from a production perspective, working code in any other environment but not in production holds no value and results in the back out.

To avoid such situations developers needs to go beyond their component scope by understanding the production environment needs domain requirements, system architecture/versions/bugs. Another important factor is the load, in large production environments, most of the times due to lack of performance testing code fails to sustain against high customer load. Also sometimes to parallel user stories delivered at the times can cause the problem with each other due to common services they are using during their journey cycle.There can be many such scenarios. But definitely understanding production scope and working will definitely reduce the likelihood of code failure. 🙂

ITIL – Incident Closure/Problem Record when no workaround ?

As per the ITIL framework, Incident record used for auditing purpose for tracking production issues. Depending on the impact on services Incident Record can have different priorities and resolution times as per the defined SLAs.

Standard practice says that incident can only be closed when impacted service is restored. Some companies follow the strict practice of tagging every single incident to the PM record for RCA. Ideally, from my view for the one-off incident should be considered as out of scope for problem management. The definition of Problem says an Unknown fault with the service/application which needs root cause analysis followed by the permanent fix. Therefore, to summarize problem records are the mandatory parameter for delivering in-life fixes discovered via incident management route. Except proactive problem management which helps to resolve fault in live service which hasn’t caused service impact.

Coming back to Incident resolution what if we don’t have the workaround. A workaround is set of activities Support team performs as part of the temporary fix to restore service. So, in this case, we cannot close this incident since service is not restored and problem record can only be raised when the incident is service restored leaving catch-22 situation. The solution to this is to leave Incident Open and deliver permanent fix under incident without creating problem record followed by service restoration.