Merging result of inputlookup file with subsearch to get required results

[SunilMaharishi]

i have one csv file which contains device name location data , i need to get count of all the device name location wise.
and then i am trying to get malware count and spyware count on the infected machines for one of indexed data as below but this search is not working "" Error in 'stats' command: The argument 'index=abc' is invalid"" if i remove inputlookup first line i will get results but i wont get total no of computers . how should i merge these two requirement .

|inputlookup xyz.csv | stats count("Device Name") as Total_Computers by Location
index=abc | search TMCM:SLF_INCIDENT
|lookup xyz.csv "Device Name" as nodename OUTPUT Location
| stats count(nodename) as InfectedComputers count(signature) as MalwareCount
count(Spyware) as Spywarecount by Location
| table Total_Computers,Location,InfectedComputers, MalwareCount, Spywarecount

[harishalipaka · Accepted solution]

hi @SunilMaharishi
try this

|inputlookup xyz.csv | stats count("Device Name") as Total_Computers by Location|appendcols [search index=abc | search TMCM:SLF_INCIDENT
|lookup xyz.csv "Device Name" as nodename OUTPUT Location
| stats count(nodename) as InfectedComputers count(signature) as MalwareCount 
count(Spyware) as Spywarecount by Location ]
| table Total_Computers,Location,InfectedComputers, MalwareCount, Spywarecount 

[SunilMaharishi]

i tried using append command and that is giving correct result , little bit modified the search though

[SunilMaharishi]

i tried using this , however i am getting the total computers and location but" InfectedComputers, MalwareCount, Spywarecount " is blank and not having any values