Is it possible to get total without the join command?

[JoshuaJohn]

I am trying to see the number of devices in a fleet by location without a specific setting applied. The data I have coming in shows the location, all devices in that location and the settings applied to each device.

OG: the location, MacAddress: devices missing the specific setting, Model: Type of device, Profile status: Missing "Specific setting", Not installed count: Count of devices in that location missing the setting, TotalCount: Total number of devices regardless of compliance with setting at that location, Compliance: Just a percentage of devices at that location that have the correct setting.

I am joining the same search to get the total number of devices at the location. The reason I am doing it this way is because I am filtering with a where in my search that will impact all values when I don't want it to impact my TotalCount. Anyway can I do this differently?

index=nitro_apps source="DATA" 
| rex "^[^,\n]*,(?P<OG>[^,]+)[^,\n]*,(?P<MacAddress>[^,]+)(?:[^ \n]* ){4}(?P<Model>[^,]+),(?P<OS>[^,]+),\w+,(?P<Profile>[^,]+),\d+,(?P<Latest_Version>\d+),(?P<Installed_Date>[^,]+)[^,\n]*,(?P<Installed>\w+)" 
| search "OG"=* Model="180" 
| eval IsProfileInstalled=if(in(Profile, "OID"),"true","false") 
| dedup MacAddress 
| where 'IsProfileInstalled'=="false" 
| eval Profile_Status="Missing "+ "OID" 
| stats list(MacAddress) as MacAddress, list(Model) as Model, list(Profile_Status) as Profile_Status, count as Not_Installed_Count by OG 
| join 
    [ search index=nitro_apps source="DATA" 
    | rex "^[^,\n]*,(?P<OG>[^,]+)[^,\n]*,(?P<MacAddress>[^,]+)(?:[^ \n]* ){4}(?P<Model>[^,]+),(?P<OS>[^,]+),\w+,(?P<Profile>[^,]+),\d+,(?P<Latest_Version>\d+),(?P<Installed_Date>[^,]+)[^,\n]*,(?P<Installed>\w+)"
    | search "OG"=*
    | where 'Model'="180" 
    | stats dc(MacAddress) as TotalCount by OG] 
| eval Compliance_% = round(((TotalCount-Not_Installed_Count)/TotalCount)*100, 2)
| table OG MacAddress Model Profile_Status Not_Installed_Count TotalCount Compliance_%

I also feel my dedup is ruining some of my data. Overall the question is can I be doing this better? I believe it can be optimized significantly.

[zonistj · Accepted solution]

I think eventstats could do what you're trying to do:

index=nitro_apps source="DATA" 
 | rex "^[^,\n]*,(?P<OG>[^,]+)[^,\n]*,(?P<MacAddress>[^,]+)(?:[^ \n]* ){4}(?P<Model>[^,]+),(?P<OS>[^,]+),\w+,(?P<Profile>[^,]+),\d+,(?P<Latest_Version>\d+),(?P<Installed_Date>[^,]+)[^,\n]*,(?P<Installed>\w+)" 
 | search "OG"=* Model="180" 
 | eval IsProfileInstalled=if(in(Profile, "OID"),"true","false") 
 | eventstats dc(MacAddress) AS TotalCount by OG
 | dedup MacAddress 
 | where 'IsProfileInstalled'=="false" 
 | eval Profile_Status="Missing "+ "OID" 
 | stats list(MacAddress) as MacAddress, list(Model) as Model, list(Profile_Status) as Profile_Status, count as Not_Installed_Count, values(TotalCount) AS TotalCount by OG 
 | eval Compliance_% = round(((TotalCount-Not_Installed_Count)/TotalCount)*100, 2)
 | table OG MacAddress Model Profile_Status Not_Installed_Count TotalCount Compliance_%

Eventstats let's you calculate statistics but it appends your newly created fields to the existing data rather than transforming it the way the normal stats command does.

[JoshuaJohn]

I don't know why I completely forgot about the existence of eventstats, thank you so much.