Splunk – Removing Data

Splunk – Removing Data ”; Previous Next Removing data from Splunk is possible by using the delete command. We first create the search condition to fetch the events we want to mark for delete. Once the search condition is acceptable, we add the delete clause at the end of the command to remove those events from Splunk. After deletion, not even a user with admin privilege is able to view this data in Splunk. Removal of data is irreversible. If you still want the removed data back into Splunk then you should have the original source data copy with you which can be used to re-index the data in Splunk. It will be a process similar to creating a new index. Assigning Delete Privilege Any user including admin user does not have access to delete the data by default. By default, only the “can_delete” role has the ability to delete events. So, we create a new user, assign this role and then login with the credentials of this new user to perform the delete operation. The below image shows how we create a new user with “can_delete” role. We arrive at this screen by following the path Settings → Access Controls → Users → New User. We then log out of Splunk interface and login back with this newly created user. Identifying the data to be removed First, we need to identify the list of events we want to remove. It is done using a normal search query specifying the filter condition. In the below example, we choose to look for the events from the host web_application which has the field http status value as 505. Our goal is to delete only the set of data containing these values to be removed from the search result. The below image shows this set of data selected. Deleting the Selected Data Next, we use the delete command to remove the above selected data from the result set. It involves just adding the word delete after ‘|’ at the end of the search query as shown below − After running the search query above, we can see the next screen where those events have got deleted. You can also further run the search query to verify that these events are not returned in the result set. Print Page Previous Next Advertisements ”;

QlikView – RangeSum Function

QlikView – RangeSum Function ”; Previous Next The RangeSum() function in QlikView is used to do a selective sum on chosen fields which is not easily achieved by the sum function. It can take expressions containing other functions as its arguments and return the sum of those expressions. Input Data Let us consider the monthly sales figure as shown below. Save the data with file name monthly_sales.csv. Month,Sales Volume March,2145 April,2458 May,1245 June,5124 July,7421 August,2584 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from the Data from Files tab and browse for the file containing the above data. Edit the load script to add the following code. Click OK and click Control+R to load the data into QlikView”s memory. LOAD Month, [Sales Volume] FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Applying RangeSum() Function With the above data loaded into QlikView”s memory, we edit the script to add a new column, which will give a rolling sum of the month wise sales volume. For this, we also take the help of the peek function discussed in the earlier chapter to hold the value of the previous record and add it to the sales volume of the current record. The following script achieves the result. LOAD Month, [Sales Volume], rangesum([Sales Volume],peek(”Rolling”)) as Rolling FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above given script. Go to the menu Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Print Page Previous Next Advertisements ”;

QlikView – Concatenation

QlikView – Concatenation ”; Previous Next Concatenation feature in QlikView is used to append the rows from one table to another. It happens even when the tables have different number of columns. It differs from both Join and Keep command, as it does not merge the matching rows from two tables into one row. Input Data Let us consider the following two CSV data files, which are used as input for further illustrations. Please note the second data set has an additional column named Country. SalesRegionOld.csv ProductID,ProductCategory,Region,SaleAmount 1,Outdoor Recreation,Europe,4579 2,Clothing,Europe,4125 3,Costumes & Accessories,South Asia,6521 4,Athletics,South Asia,4125 5,Personal Care,Australia,5124 6,Arts & Entertainment,North AMerica,1245 7,Hardware,South America,456 SalesRegionNew.csv ProductID,ProductCategory,Region,Country,SaleAmount 6,Arts & Entertainment,North AMerica,USA,1245 7,Hardware,South America,Brazil,456 8,Home & Garden,South America,Brazil,241 9,Food,South Asia,Singapore,1247 10,Home & Garden,South Asia,China,5462 11,Office Supplies,Australia,Australia,577 Load Script We load the above input data using the script editor, which is invoked by pressing Control+E. Choose the option Table Files and browse for the Input file. Then we edit the commands in the script to apply the concatenation between the tables. Next, we load the above data to QlikView”s memory and create a Table Box by using the menu Layout → New Sheet Objects → Table Box where we choose all the available fields to be displayed as shown below. Concatenated Data Completing above steps we get the Table box displayed as shown below. Please note the duplicate rows for the product ID 6 and 7. Concatenate does not eliminate the duplicates. Print Page Previous Next Advertisements ”;

QlikView – Column Manipulation

QlikView – Column Manipulation ”; Previous Next Column Manipulation is a type of Data Transformation in which a new column is populated with values from an existing column, which meets certain criteria. The criteria can be an expression, which is created as part of the Data Transformation step. Input Data Let us consider the following input data, which represents the actual and forecasted sales figures. Month,Forecast,Actual March,2145,2247 April,2458,2125 May,1245,2320 June,5124,3652 July,7421,7514 August,2584,3110 September,5314,4251 October,7846,6354 November,6532,7451 December,4625,1424 January,8547,7852 February,3265,2916 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the “Table Files” option from the “Data from Files” tab and browse for the file containing the above data. After clicking Next, we choose the Enable Transformation Step button to carry out the required data transformation. Selecting the Data Transformation Choose the Column tab and then choose the New button. It asks to specify the New column and the Row Condition. We specify column 3 as the source column and pick the values, which start with two as the Row Condition. Transformed Data On completing the above steps, we get the transformed data as shown below. Load Script for Transformed Data The load script for the Transformed data can be seen using the script editor. The script shows the expression, which creates the new column with required values. Display Transformed Data The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object. Print Page Previous Next Advertisements ”;

QlikView – Rotating Tables

QlikView – Rotating Tables ”; Previous Next The Rotating table in QlikView is similar to the column and row transpose feature in Microsoft Excel but with some additional options. We can transpose columns in multiple directions and they give different results. In this chapter, we will be seeing the normal transpose option of converting rows to columns. Input Data Let us consider the following input data, which represents the actual and forecasted sales figures. Month,Forecast,Actual March,2145,2247 April,2458, May,1245, June,5124,3652 July,7421,7514 August,2584, September,5314,4251 October,7846,6354 November,6532,7451 December,4625,1424 January,8547,7852 February,3265, Load Script The above data is loaded to QlikView memory by using the script editor. Open the script editor from the File menu or press Control+E. Choose the “Table Files” option from the “Data from Files” tab and browse for the file containing the above data. After clicking Next, we choose the Enable Transformation Step button to carry out the required data transformation. Selecting the Data Transformation As we are going to use the Rotate function, let us choose the Rotate tab which displays the values of all the fields. Apply Rotate We click the Transpose button to transpose the above data. The transposed data appears as shown below. Load Script for transformed Data The load script for the Transformed data can be seen using the script editor. The script shows the expression, which replaces the empty cell values. Display Transformed Data The transformed data can be seen by creating a Table Box using the option in the menu Layout → New Sheet Object. Print Page Previous Next Advertisements ”;

QlikView – Pivot Tables

QlikView – Pivot Tables ”; Previous Next Pivot Tables are widely used in data analysis to present sum of values across many dimensions available in the data. QlikView”s Chart option has the feature to create a Pivot Table by choosing the appropriate chart type. Input Data Let us consider the following input data, which represents the sales figure of different product lines and product categories. Product_Line,Product_category,Value Sporting Goods,Outdoor Recreation,5642 Food, Beverages & Tobacco,2514 Apparel & Accessories,Clothing,2365 Apparel & Accessories,Costumes & Accessories,4487 Sporting Goods,Athletics,812 Health & Beauty,Personal Care,6912 Arts & Entertainment,Hobbies & Creative Arts,5201 Arts & Entertainment,Paintings,8451 Arts & Entertainment,Musical Instruments,1245 Hardware,Tool Accessories,456 Home & Garden,Bathroom Accessories,241 Food,Drinks,1247 Home & Garden,Lawn & Garden,5462 Office Supplies,Presentation Supplies,577 Hardware,Blocks,548 Baby & Toddler,Diapering,1247 Baby & Toddler,Toys,257 Home & Garden,Pipes,1241 Office Supplies,Display Board,2177 Load Script The above data is loaded to the QlikView’s memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the “Table Files” option from the “Data from Files” tab and browse for the file containing the above data. The following screen appears. Click “OK” and press “Control+R” to load the data into QlikView”s memory. Select Chart Type Next, we use the chart wizard to select the Pivot Table option. Click Next. Select Chart Dimension In the next screen, we choose Product_Line as the first dimension for the chart. Select Chart Expression The next screen prompts us for selecting the chart expression where we choose the sum of value. Select the Chart Format On clicking next, we get the screen to choose chart format in which we select Pyjama Green as the style and the default mode. Pivot Chart Data Completing the above steps gives us the final chart as below. Print Page Previous Next Advertisements ”;

QlikView – Bar Chart

QlikView – Bar Chart ”; Previous Next Bar charts are very widely used charting method to study the relation between two dimensions in form of bars. The height of the bar in the graph represents the value of one dimension. The number of bars represent the sequence of values or grouped values of another dimension. Input Data Let us consider the following input data, which represents the sales figure of different product lines and product categories. Product_Line,Product_category,Value Sporting Goods,Outdoor Recreation,5642 Food, Beverages & Tobacco,2514 Apparel & Accessories,Clothing,2365 Apparel & Accessories,Costumes & Accessories,4487 Sporting Goods,Athletics,812 Health & Beauty,Personal Care,6912 Arts & Entertainment,Hobbies & Creative Arts,5201 Arts & Entertainment,Paintings,8451 Arts & Entertainment,Musical Instruments,1245 Hardware,Tool Accessories,456 Home & Garden,Bathroom Accessories,241 Food,Drinks,1247 Home & Garden,Lawn & Garden,5462 Office Supplies,Presentation Supplies,577 Hardware,Blocks,548 Baby & Toddler,Diapering,1247 Baby & Toddler,Toys,257 Home & Garden,Pipes,1241 Office Supplies,Display Board,2177 Load Script The above data is loaded to the QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the “Table Files” option form the “Data from Files” tab and browse for the file containing the above data. Edit the load script to add the following code. Click “OK” and press “Control+R” to load the data into the QlikView”s memory. LOAD Product_Line, Product_category, Value FROM [C:Qlikviewdataproduct_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Table Box(Sheet Object) For the above data, let us create a Table Box, which will show the data in a tabular form. Go to the menu Layout → New Sheet Object → Table Box and choose the column as shown below. Click Apply and then OK to finish creating the Table box. The below given screen appears. Using the Quick Chart Wizard To start creating a bar chart, we will use the quick chart wizard. On clicking it, the following screen appears which prompts for selecting the chart type. Choose bar Chart and click Next. Choose the Chart Dimension Choose Product Line as the First Dimension. Choose the Chart Expression The chart expression is used to apply the functions like Sum, Average, or Count on the fields with numeric values. We will apply the Sum function on the filed named Value. Click Next. Choose the Chart Format The Chart format defines the style and orientation of the chart. We choose the first option in each category. Click Next. The Bar Chart The Bar chart appears as shown below. It shows the height of the field value for different product lines. Print Page Previous Next Advertisements ”;

QlikView – Preceding Load

QlikView – Preceding Load ”; Previous Next QlikView Preceding load is a load type in which we use a load statement, which takes the columns of another load statement present in the same script. The data read by the first Load statement, which is at the bottom of the script editor window and then used by the load statements above it. Load Script The below given screen shot shows the script for data, which is loaded as Inline data and then the max function is applied to one of the columns. The load statement at the bottom makes the data available in QlikView”s memory, which is used by the second load statement above the first load statement. The second load statement applies the max function with group by clause. Table Box Data On creating a Table Box Sheet Object, we see the data that is read from the Inline data load option. Print Page Previous Next Advertisements ”;

QlikView – Peek Function

QlikView – Peek Function ”; Previous Next The peek() function in QlikView is used to fetch the value of a field from a previous record and use it in calculations. Input Data Let us consider the monthly sales figure as shown below. Save the data with file name monthly_sales.csv. Month,Sales Volume March,2145 April,2458 May,1245 June,5124 July,7421 August,2584 September,5314 October,7846 November,6532 December,4625 January,8547 February,3265 Load Script The above data is loaded to QlikView memory by using the script editor. Open the Script editor from the File menu or press Control+E. Choose the Table Files option from for the file containing the above data. Edit the load script to add the following code. Click OK and click Control+R to load the data into QlikView”s memory. LOAD Month, [Sales Volume], peek(”Sales Volume”) as Prevmonth FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above script. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the csv file in the QlikView Table Box as shown below. Also set the sort order as shown below to get the result in the same order of the field Month as it is in the source. On completing the above steps and clicking Finish, we get the Table box showing the data as given below. Using peek() Function in Calculations The peek() can be used in calculations involving other columns. Let us display the percentage change for sales volume for each month. The following script achieves this result. LOAD Month, [Sales Volume], peek(”Sales Volume”) as Prevvolume, (([Sales Volume]-peek(”Sales Volume”)))/peek(”Sales Volume”)*100 as Difference FROM [C:Qlikviewdatamonthly_sales.csv] (txt, codepage is 1252, embedded labels, delimiter is ”,”, msq); Creating Sheet Object Let us create a Table Box sheet object to show the data generated by the above script. Go to the menu item Layout → New Sheet Object → Table Box. The following window appears in which we mention the Title of the table and select the required fields to be displayed. Clicking OK displays the data from the CSV file in the QlikView Table Box as shown below. Print Page Previous Next Advertisements ”;

Qlikview – Functions

QlikView – Functions ”; Previous Next QlikView has many built-in functions, which are available to be applied to data that is already available in memory. These functions are organized into many categories and the syntax of the function appears as soon as it is selected. We can click on the Paste button to get the expression into the editor and supply the arguments. Create Table Box Create a Table Box by following the menu as shown in the screen shot given below. On completing the above given step, we get a window to show the Calculation condition at the bottom left. List of Functions Click on the button next to calculation condition and go to the Function tab. It shows the list of functions available. On choosing String from the functions category, we can see only few functions, which take a string as an argument. In the next chapters, we will see the use of many important functions. Print Page Previous Next Advertisements ”;