topic Group results by similar name into one in Splunk Search

Group results by similar name into one

davidcraven02 — Thu, 01 Feb 2018 10:25:46 GMT

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

Re: Group results by similar name into one

493669 — Thu, 01 Feb 2018 10:45:25 GMT

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!

Re: Group results by similar name into one

davidcraven02 — Thu, 01 Feb 2018 11:26:09 GMT

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

Re: Group results by similar name into one

493669 — Thu, 01 Feb 2018 11:33:24 GMT

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"...

Re: Group results by similar name into one

davidcraven02 — Thu, 01 Feb 2018 13:37:34 GMT

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.

Re: Group results by similar name into one

FrankVl — Thu, 01 Feb 2018 13:47:45 GMT

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?

Re: Group results by similar name into one

niketn — Thu, 01 Feb 2018 14:00:24 GMT

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

Re: Group results by similar name into one

davidcraven02 — Fri, 02 Feb 2018 10:01:47 GMT

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%

Re: Group results by similar name into one

davidcraven02 — Fri, 02 Feb 2018 10:21:12 GMT

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%

Re: Group results by similar name into one

niketn — Fri, 02 Feb 2018 11:28:04 GMT

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

Re: Group results by similar name into one

davidcraven02 — Fri, 02 Feb 2018 15:02:17 GMT

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.