Group results by similar name into one

[davidcraven02]

The name for Windows 7 Enterprise is spelt incorrectly for 6 machines as "Entreprise" and I need to group both these Windows 7 results together into one so the total count becomes 100 for Windows 7 Enterprise and the incorrect spelling is removed from the table.

 index=ad source=otl_addnsscan 
      | eval machine=lower(name)
  | rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
  | rename data as IPAddress 
  | search machine="***" operatingSystem="***" OR NOT operatingSystem="*" 
  | fillnull value="No OS listed" operatingSystem
  | dedup machine 
  | stats count(machine) as count by operatingSystem 
  | eventstats sum(count) as total
 | eval percentage = ((count/total)*100)
 | eval percentage = round(percentage,2)
 |eval percentage =percentage ."%"
 |table operatingSystem ,count, percentage 
 |sort operatingSystem

[niketn]

There are several performance issues with your query

1) | search should be added to base search to restrict events pulled from index.
2) Field rename, eval should happen after transforming command unless it can't be avoided i.e. stats so that they operate only on few aggregated rows rather than all events.
3) lower(name) seems unnecessary additional command.
4) eval command can be cascaded

Besides the above can you explain your intention with operatingSystem="***" OR NOT operatingSystem="*"
Try the changes suggested below and confirm:

    index="ad" source="otl_addnsscan" Caption0="yourOSSearchPattern*"  name="yourMachineSearchPattern*"
   | dedup name
   | fillnull value="No OS listed" Caption0
   | stats count(name) as count by Caption0
   | eval Caption0=replace(Caption0,"Entreprise","Enterprise")
   | stats sum(count) as count by Caption0 
   | eventstats sum(count) as total
   | eval percentage = round(((count/total)*100),2)."%"
   | rename Caption0 as  operatingSystem
   | sort operatingSystem
   | table operatingSystem ,count, percentage 

[davidcraven02]

I appreciate the follow up. Its displaying them as separate in a table and chart format even though they are spelt the same.

Actual Output
Microsoft Windows 7 Enterprise 94 2.19%
Microsoft Windows 7 Enterprise 6 0.14%

Desired Output
Microsoft Windows 7 Enterprise 100 2.33%

[niketn]

Hi @davidcraven02. Have you also added the second stats sum(count) as count by Caption0. Percent should be calculated afterwards. Following is a run anywhere search . Commands till the following are used to mimica some sample data as per your current output. | eval Caption0=replace(Caption0,"Entreprise","Enterprise"). The commands with stats starting after the above replace() eval are exactly the same as per the answer. Please try out and confirm.

|  makeresults
|  eval Caption0="Microsoft Windows 10 Enterprise,208;Microsoft Windows 10 Pro,2083;Microsoft Windows 10 Pro N,1;Microsoft Windows 7 Enterprise,94;Microsoft Windows 7 Professional,1409;Microsoft Windows 7 Ultimate,3;Microsoft Windows 7 Entreprise,1409;"
|  makemv Caption0 delim=";"
|  mvexpand Caption0
|  eval Caption0=split(Caption0,",")
|  eval count=mvindex(Caption0,1)
|  eval Caption0=mvindex(Caption0,0)
|  eval Caption0=replace(Caption0,"Entreprise","Enterprise")
|  stats sum(count) as count by Caption0 
|  eventstats sum(count) as total
|  eval percentage = round(((count/total)*100),2)."%"
|  rename Caption0 as  operatingSystem
|  sort operatingSystem
|  table operatingSystem ,count, percentage

[davidcraven02]

Yes that runs correctly but I found that when I download the raw logs it lists the OS as "Microsoft Windows 7 Entreprise" but in Splunk it displays it as "Microsoft Windows 7 Entreprise".

Perhaps this is why Splunk doesn't recognize them as being the same because of this character.

[davidcraven02]

Thanks for a follow up. Yes this renames it the incorrect one to 'Microsoft Windows 7 Enterprise' correctly but when displaying in a table or pie chart they are both listed individually with the same name not as one as I intended.

Actual Results
Microsoft Windows 7 Enterprise 94 2.19%
Microsoft Windows 7 Enterprise 6 0.14%

Desired Results
Microsoft Windows 7 Enterprise 100 3.33%

[493669]

Hi @davidcraven02,
Try This:

index=ad source=otl_addnsscan 
       | eval machine=lower(name)
   | rename User_Name0 as LastKnownUser, Caption0 as operatingSystem, Version0 as Version, Model0 as Model 
   | rename data as IPAddress 
   | search machine="***" operatingSystem="***" OR NOT operatingSystem="*" 
   | fillnull value="No OS listed" operatingSystem
   | dedup machine 
   |eval operatingSystem=replace(operatingSystem,"Entreprise", "Enterprise")
   | stats count(machine) as count by operatingSystem 
   | eventstats sum(count) as total
  | eval percentage = ((count/total)*100)
  | eval percentage = round(percentage,2)
  |eval percentage =percentage ."%"
  |table operatingSystem ,count, percentage 
  |sort operatingSystem

Hope this helps!

[davidcraven02]

Thanks but this didn't make any difference to the table.

[493669]

Try this anywhere search :

|makeresults|eval operatingSystem="Microsoft Windows 10 Entreprise"|append[|makeresults|eval operatingSystem="Microsoft Windows 10 Enterprise"]|append[|makeresults|eval operatingSystem="Microsoft Windows 10 Pro"]|table operatingSystem|eval operatingSystem=replace(operatingSystem,"Entreprise", "Enterprise")

same way include

...|eval operatingSystem=replace(operatingSystem,"Entreprise", "Enterprise")

so that it will replace "Entreprise" by "Enterprise"...

[davidcraven02]

This didnt work unfortunately.

Microsoft Windows 10 Pro
Microsoft Windows 10 Enterprise
Microsoft Windows 10 Enterprise

It doesn't merge the into one row, same issue with the other guys comment.

[FrankVl]

But the string replacing worked.

Did you add that eval statement in your original search in the exact place that was suggested? Given your comment it seems that you might have added the eval statement to fix the OS name after the stats count by operatingSystem line?