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.
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.
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.
I don't know why I completely forgot about the existence of eventstats, thank you so much.