Splunk Search

Is it possible to get total without the join command?

Contributor

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.

alt text

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.

0 Karma
1 Solution

Path Finder

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.

View solution in original post

0 Karma

Path Finder

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.

View solution in original post

0 Karma

Contributor

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!