Subsearch

[RanjiRaje]

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

[richgalloway · Accepted solution]

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'

[RanjiRaje]

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 ...