Hi everyone, My post is huge. sorry for that. I need suggestion from you for the query I framed.
I have 2 lookup used (lookfileA, lookfileB)
column: BaseA > count by division in lookupfileA
column: Column_IndexA > to compare lookfileA under indexA and get matching host count
column: BaseB > count by division in lookupfileB
column: Inscope > count by division in lookupfileB with Active status
column: Column_OtherIndexes > to compare lookfileB under otherindexes and get matching host count
index=indexA
| lookup lookfileA host as hostname OUTPUTNEW Division
| fields hostname,Division
| stats dc(hostname) as "Column_IndexA" by Division
| append
[| tstats count where index IN ("win","linux") by host
| eval host=upper(host)
| fields - count
| join type=inner host
[| inputlookup lookfileA
| fields host, Division
| eval host=upper(host)]
| stats count as "Column_OtherIndexes" by Division]
| append
[| inputlookup lookfileA
| stats count as "BaseA" by Division]
| append
[| inputlookup lookfileB
| stats count as BaseB by category
| where category IN ("Win","Linux")
| rename category as Division]
| append
[| inputlookup lookfileB
| stats count as Inscope by category,status
| where category IN ("Win","Linux") AND status="Active"
| rename category as Division]
| fields Division,BaseB,Inscope,"Column_OtherIndexes","BaseA","Column_IndexA"
| stats values(*) as * by Division
| table Division,BaseB,Inscope,"Column_OtherIndexes","BaseA","Column_IndexA"
| eval Difference="Column_IndexA" - "Column_OtherIndexes"
| fillnull value=0
| addtotals col=t row=f labelfield=Division label=Total
Below is the sample output and I need to get difference column. Used eval command but getting error
Division | BaseB | Inscope | Column_OtherIndexes | BaseA | Column_IndexA | Difference |
M | 300 | 200 | 50 | 300 | 200 | 200-50 |
N | 200 | 100 | 20 | 300 | 200 | 200-20 |
Total | 500 | 300 | 70 | 600 | 400 | 400-70 |
What error did you get from the eval command?
I suspect eval is having a problem substracting one string constant from another. Put the field names in single quotes rather than double quotes. On the RHS, single quotes denote a field name and double quotes denote a string. In fact, quotation marks are not needed at all with those names.
| eval Difference = 'Column_IndexA' - 'Column_OtherIndexes'
What error did you get from the eval command?
I suspect eval is having a problem substracting one string constant from another. Put the field names in single quotes rather than double quotes. On the RHS, single quotes denote a field name and double quotes denote a string. In fact, quotation marks are not needed at all with those names.
| eval Difference = 'Column_IndexA' - 'Column_OtherIndexes'
Thanks you so much for a quick help. I got the result as expected 🙂
Today I learned that single quotes denotes a field and double quotes denotes a string. Thanks a lot ...