Splunk Search

addtotal ignoring a particular row

surekhasplunk
Communicator

alt text

Current output is attached in the image. i have one input lookup file file1.csv
1) Rows 2-4 are coming are coming from column "ITO Department" of lookup file
2) Rows 6 till 8 are coming from "Other Department" of the same lookup file.
3) ALL ITO is sum of all ITO Department names
4) TOTAL is total of all ALL ITO + 6 to 8

Now how do i achieve this as when i am adding TOTAL using addtotals its giving me sum of all which leads to a count of 20

Tags (1)
0 Karma
1 Solution

acharlieh
Influencer

As Kablesh_vaghela mentions you need to have a scheme where you don't add the total rows... I feel like the magic bullet here for you would be appendpipe or similar commands depending on what you're trying to accomplish...

Unfortunately, while you give a description of what you want, your description of your source data could be more precise, so I'm assuming that you have a lookup where if it's an ITO Department the name is in one column, but other department the name is in another column, e.g. your file1.csv could be generated like so:

| makeresults | eval _raw="ITO Department,Other Department,Permanent,Contract
AB-ITO-DEV,,1,3
AB-ITO-SUPPORT,,2,4
AB-ITO-IMP,,2,2
,HR,4,2
,SUPPORT,3,5
,MANAGEMENT,3,2" | multikv | table *Department,Contract,Permanent | outputcsv file1.csv

and that csv was turned into a lookup retrievable by | inputlookup file1 (or you leverage | inputcsv file1.csv)

One way to get the result you describe is through a currently undocumented search command multireport.

| inputlookup file1 
| multireport 
  [where ITO_Department!="" | rename ITO_Department -> "Department Name" | addcoltotals label="ALL ITO" labelfield="Department Name"] 
  [where Other_Department!="" | rename Other_Department -> "Department Name"] 
  [addcoltotals label="ALL" labelfield="Department Name" | where isnotnull('Department Name')]
| table Department* Contract Permanent

in this case it's kind of behaving like appendpipe on steroids... the input to all of the subsearches is the search to this point (i.e. the entire lookup with no totals), and the output is the results of each in order. We first extract only the ITO Departments, and add a total row for them. We then extract only the other departments (but do nothing else as your output has no Other total row). Finally we give our totals over all rows (remember the input here is the raw table), and we remove all of the source rows from this section. Multireport then stitches these three sets of rows together into a cohesive report.

Now a second potential way to come up with the same output would be like so:

| inputlookup file1 where ITO_Department!="" | addcoltotals label="ALL ITO" labelfield="Department Name"
| inputlookup append=true file1 where Other_Department!=""
| appendpipe [where isnull('Department Name') | stats sum(*) as * ]
| eval "Department Name"=case(isnotnull('Department Name'),'Department Name',ITO_Department!="",ITO_Department,Other_Department!="",Other_Department,1=1,"ALL")
| table D*, P*, C*

Here we pull the parts out of the file in pieces, and then use appendpipe with a where get rid of the other total rows before doing the stats for generating the sums, (this is nice as it has the property that it also throws away the input rows for it's output), and then we do cleanup afterwards.

There may be more options open to you, and some of the conditions may be different, depending on what your source data actually looks like but these should give you some ideas on the topic.

View solution in original post

acharlieh
Influencer

As Kablesh_vaghela mentions you need to have a scheme where you don't add the total rows... I feel like the magic bullet here for you would be appendpipe or similar commands depending on what you're trying to accomplish...

Unfortunately, while you give a description of what you want, your description of your source data could be more precise, so I'm assuming that you have a lookup where if it's an ITO Department the name is in one column, but other department the name is in another column, e.g. your file1.csv could be generated like so:

| makeresults | eval _raw="ITO Department,Other Department,Permanent,Contract
AB-ITO-DEV,,1,3
AB-ITO-SUPPORT,,2,4
AB-ITO-IMP,,2,2
,HR,4,2
,SUPPORT,3,5
,MANAGEMENT,3,2" | multikv | table *Department,Contract,Permanent | outputcsv file1.csv

and that csv was turned into a lookup retrievable by | inputlookup file1 (or you leverage | inputcsv file1.csv)

One way to get the result you describe is through a currently undocumented search command multireport.

| inputlookup file1 
| multireport 
  [where ITO_Department!="" | rename ITO_Department -> "Department Name" | addcoltotals label="ALL ITO" labelfield="Department Name"] 
  [where Other_Department!="" | rename Other_Department -> "Department Name"] 
  [addcoltotals label="ALL" labelfield="Department Name" | where isnotnull('Department Name')]
| table Department* Contract Permanent

in this case it's kind of behaving like appendpipe on steroids... the input to all of the subsearches is the search to this point (i.e. the entire lookup with no totals), and the output is the results of each in order. We first extract only the ITO Departments, and add a total row for them. We then extract only the other departments (but do nothing else as your output has no Other total row). Finally we give our totals over all rows (remember the input here is the raw table), and we remove all of the source rows from this section. Multireport then stitches these three sets of rows together into a cohesive report.

Now a second potential way to come up with the same output would be like so:

| inputlookup file1 where ITO_Department!="" | addcoltotals label="ALL ITO" labelfield="Department Name"
| inputlookup append=true file1 where Other_Department!=""
| appendpipe [where isnull('Department Name') | stats sum(*) as * ]
| eval "Department Name"=case(isnotnull('Department Name'),'Department Name',ITO_Department!="",ITO_Department,Other_Department!="",Other_Department,1=1,"ALL")
| table D*, P*, C*

Here we pull the parts out of the file in pieces, and then use appendpipe with a where get rid of the other total rows before doing the stats for generating the sums, (this is nice as it has the property that it also throws away the input rows for it's output), and then we do cleanup afterwards.

There may be more options open to you, and some of the conditions may be different, depending on what your source data actually looks like but these should give you some ideas on the topic.

surekhasplunk
Communicator

Thanks @acharlieh for such a detailed explanation.
I got the hint of what you meant. and got it working.

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

Hi @surekhasplunk ,
You ignore all those rows which is starts from "ALL*". Can you please share your sample search So we can you help you more.
Thanks

0 Karma

surekhasplunk
Communicator
|inputlookup file1.csv |search bla bla |chart count over "Other Department" by "Sourcing Status" |eval bla bla |appendpipe [|inputlookup file1.csv |search "ITO Department"=*ITO* "Sourcing Status"="Permanent" OR "Sourcing Status"="Contract"|chart count over "ITO Department" by "Sourcing Status" |eval bla bla]|rename "Department Name" as wd | rex field=wd mode=sed "s/ITO Dept/ALL ITO/g"| addcoltotals label="TOTAL" labelfield="Department Name" 

Here is the piece of code am using

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...