User agent Extraction - Lookup

[jaibalaraman]

Hi Team 

I am trying to extract few report from user agent. like below 

OS details

OS version

Browser

Browser Version

Operating System

Operating System Version

Mobile device

Currently i am using eval  ( IF & Case ) to generate report however its very manual process and more time consuming. Please find below command for example 

If - val Device =if(match(cs_user_agent, "iPhone"),"iPhone",if(match(cs_user_agent, "Macintosh"),"iPhone",if(match(cs_user_agent, "iPad"),"iPhone",if(match(cs_user_agent, "Android"),"Android",if(match(cs_user_agent, "Win64"),"Windows",if(match(cs_user_agent, "14092"),"Windows",if(match(cs_user_agent, "Windows"),"Windows",if(match(cs_user_agent,"SM-"),"Android",if(match(cs_user_agent,"CPH"),"Android",if(match(cs_user_agent,"Nokia"),"Android",if(match(cs_user_agent,"Pixel"),"Android",if(match(cs_user_agent,"TB-"),"Android",if(match(cs_user_agent,"VFD"),"Android",if(match(cs_user_agent,"HP%20Pro%20Slate"),"Android",if(match(cs_user_agent,"VOG-L09"),"Android",if(match(cs_user_agent,"YAL-L21"),"Android",if(match(cs_user_agent,"ATU-L22"),"Android",if(match(cs_user_agent,"MAR-LX1A"),"Android",if(match(cs_user_agent,"RNE-L22"),"Android",if(match(cs_user_agent,"INE-LX2"),"Android",if(match(cs_user_agent,"AMN-LX2"),"Android",if(match(cs_user_agent,"LYO-LO2"),"Android",if(match(cs_user_agent,"DRA-LX9"),"Android",if(match(cs_user_agent,"LYA-L29"),"Android",if(match(cs_user_agent,"ANE-LX2J"),"Android",if(match(cs_user_agent,"STK-L22"),"Android",if(match(cs_user_agent,"EML-AL00"),"Android",if(match(cs_user_agent,"BLA-L29"),"Android",if(match(cs_user_agent,"X11"),"Linux",if(match(cs_user_agent,"LDN-LX2"),"Android",if(match(cs_user_agent,"TB3-"),"Android",if(match(cs_user_agent,"5033T"),"Android",if(match(cs_user_agent,"5028D"),"Android",if(match(cs_user_agent,"5002X"),"Android",if(match(cs_user_agent,"COR-"),"Android",if(match(cs_user_agent,"MI%20MAX"),"Android",if(match(cs_user_agent,"WAS-LX2"),"Android",if(match(cs_user_agent,"vivo"),"Android",if(match(cs_user_agent,"EML-L29"),"Android",if(match(cs_user_agent,"Moto"),"Android",if(match(cs_user_agent,"MMB"),"Android",if(match(cs_user_agent,"Redmi%20Note%208"),"Android",if(match(cs_user_agent,"M2003J15SC"),"Android",if(match(cs_user_agent,"MI%20MAX"),"Android",if(match(cs_user_agent,"Nexus"),"Android",if(match(cs_user_agent,"ELE-L29"),"Android",if(match(cs_user_agent,"Redmi%20Note%204"),"Android",if(match(cs_user_agent,"rv:89.0"),"Android",if(match(cs_user_agent,"VKY-L09"),"Android",if(match(cs_user_agent,"SmartN11"),"Android",if(match(cs_user_agent,"A330"),"Android",if(match(cs_user_agent,"LM-"),"Android",if(match(cs_user_agent,"G8341"),"Android",if(match(cs_user_agent,"INE-AL00"),"Android",if(match(cs_user_agent,"Mi"),"Android",if(match(cs_user_agent,"CLT"),"Android",if(match(cs_user_agent,"Android"),"Android",if(match(cs_user_agent,"BV9700Pro"),"Android",if(match(cs_user_agent,"5024I"),"Android",if(match(cs_user_agent,"MEIZU"),"Android",if(match(cs_user_agent,"Linux%20X86_64"),"Linux","OTHER")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

Case - val Brand= case(match(cs_user_agent, "CPH"),"Oppo",match(cs_user_agent, "SM-"),"Samsung",match(cs_user_agent, "VFD"),"Vodafone",match(cs_user_agent, "VFD"),"Vodafone",match(cs_user_agent, "VOG"),"Huawei",match(cs_user_agent, "ELE"),"Huawei",match(cs_user_agent, "CLT"),"Huawei",match(cs_user_agent, "EML"),"Huawei",match(cs_user_agent, "LYA"),"Huawei",match(cs_user_agent, "EVR"),"Huawei",match(cs_user_agent, "BLA"),"Huawei",match(cs_user_agent, "DRA"),"Huawei",match(cs_user_agent, "LDN"),"Huawei",match(cs_user_agent, "YAL-L21"),"Huawei",match(cs_user_agent, "ATU-L22"),"Huawei",match(cs_user_agent, "MAR-LX1A"),"Huawei",match(cs_user_agent, "X11"),"Linux",match(cs_user_agent, "INE-LX2"),"Huawei",match(cs_user_agent, "AMN-"),"Huawei",match(cs_user_agent, "RNE-L22"),"Honor",match(cs_user_agent, "LYO"),"Huawei",match(cs_user_agent, "ANE"),"Huawei",match(cs_user_agent, "STK"),"Huawei",match(cs_user_agent, "BLA"),"Huawei",match(cs_user_agent, "TB3-"),"Lenovo",match(cs_user_agent, "5033T"),"Alcatel",match(cs_user_agent, "5028D"),"Alcatel",match(cs_user_agent, "5002X"),"Alcatel",match(cs_user_agent, "iPhone"),"iPhone",match(cs_user_agent, "20Win64"),"Desktop",1=1,"other")

Can any one help me on how do i use  lookup? or automatic lookup so it fills a "human-readable" type into a separate field.

Thanks 

[jaibalaraman]

Hi 

Not really, the output is coming with some errors like counts are similar for iphone & Desktop. please find below screen shot

Also could you please help me with some sample lookup command based on the case statement which you suggested

[bowesmana]

Can you share the current search?

[jaibalaraman]

Hi , yes

| eval Device = if(match(cs_user_agent,"SM-"),"Android", if(match(cs_user_agent,"CPH"),"Android",if(match(cs_user_agent,"Nokia"),"Android",if(match(cs_user_agent,"Pixel"),"Android",if(match(cs_user_agent,"TB-"),"Android",if(match(cs_user_agent,"VFD"),"Android",if(match(cs_user_agent,"HP%20Pro%20Slate"),"Android",if(match(cs_user_agent,"VOG-L09"),"Android",if(match(cs_user_agent,"YAL-L21"),"Android",if(match(cs_user_agent,"ATU-L22"),"Android",if(match(cs_user_agent,"MAR-LX1A"),"Android",if(match(cs_user_agent,"RNE-L22"),"Android",if(match(cs_user_agent,"INE-LX2"),"Android",if(match(cs_user_agent,"AMN-LX2"),"Android",if(match(cs_user_agent,"LYO-LO2"),"Android",if(match(cs_user_agent,"DRA-LX9"),"Android",if(match(cs_user_agent,"LYA-L29"),"Android",if(match(cs_user_agent,"ANE-LX2J"),"Android",if(match(cs_user_agent,"STK-L22"),"Android",if(match(cs_user_agent,"EML-AL00"),"Android",if(match(cs_user_agent,"BLA-L29"),"Android",if(match(cs_user_agent,"X11"),"Linux",if(match(cs_user_agent,"LDN-LX2"),"Android",if(match(cs_user_agent,"TB3-"),"Android",if(match(cs_user_agent,"5033T"),"Android",if(match(cs_user_agent,"5028D"),"Android",if(match(cs_user_agent,"5002X"),"Android",if(match(cs_user_agent,"COR-"),"Android",if(match(cs_user_agent,"MI%20MAX"),"Android",if(match(cs_user_agent,"WAS-LX2"),"Android",if(match(cs_user_agent,"vivo"),"Android",if(match(cs_user_agent,"EML-L29"),"Android",if(match(cs_user_agent,"Moto"),"Android",if(match(cs_user_agent,"MMB"),"Android",if(match(cs_user_agent,"Redmi%20Note%208"),"Android",if(match(cs_user_agent,"M2003J15SC"),"Android",if(match(cs_user_agent,"MI%20MAX"),"Android",if(match(cs_user_agent,"Nexus"),"Android",if(match(cs_user_agent,"ELE-L29"),"Android",if(match(cs_user_agent,"Redmi%20Note%204"),"Android",if(match(cs_user_agent,"rv:89.0"),"Android",if(match(cs_user_agent,"VKY-L09"),"Android",if(match(cs_user_agent,"SmartN11"),"Android",if(match(cs_user_agent,"A330"),"Android",if(match(cs_user_agent,"LM-"),"Android",if(match(cs_user_agent,"G8341"),"Android",if(match(cs_user_agent,"INE-AL00"),"Android",if(match(cs_user_agent,"Mi"),"Android",if(match(cs_user_agent,"CLT"),"Android",if(match(cs_user_agent,"Android"),"Android",if(match(cs_user_agent,"BV9700Pro"),"Android",if(match(cs_user_agent,"5024I"),"Android",if(match(cs_user_agent,"MEIZU"),"Android","OTHER"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
| stats count by Device | sort -count

[bowesmana]

Sorry, I assumed you were using the my suggested case statement - rather than your original nested if statement. Are you saying that if statement is what gives you the results in your screenshot?

As for your question on examples of the lookup command, my earlier post showed you how to use the lookup command to get what you want, you will just need to create your lookup as I suggeted. You will not need the case statement if you use a lookup.

See the lookup description here

https://docs.splunk.com/Documentation/Splunk/8.2.2/SearchTutorial/Usefieldlookups

and how to set up wildcard lookups here

https://docs.splunk.com/Documentation/Splunk/8.2.2/Knowledge/Addfieldmatchingrulestoyourlookupconfig...

[bowesmana]

You are using match/if in the wrong way - for this type of match you should be using case statement, not if and you should use the features of match so that you use regular expressions rather than a match statement for a single string. Instead you could use this syntax - try this complete search - but the case statement is what you can use in yours...

| makeresults
| fields - _time
| eval cs_user_agent=split("iPhone,Nokia,VOG-LO8,VOG-L09,Android,X11,Redmi%20Note%204", ",")
| mvexpand cs_user_agent
| eval Device = case(
  match(cs_user_agent, "iPhone|Macintosh|iPad"),"iPhone",
  match(cs_user_agent, "(?i)win64|14092|windows"),"Windows", 
  match(cs_user_agent, "Android|SM-|CPH|Nokia|Pixel|TB-|VFD|HP%20Pro%20Slate|VOG-L09|YAL-L21|ATU-L22|MAR-LX1A|RNE-L22|INE-LX2|AMN-LX2|LYO-LO2|DRA-LX9|LYA-L29|ANE-LX2J|STK-L22|EML-AL00|BLA-L29|LDN-LX2|TB3-|5033T|5028D|5002X|COR-|MI%20MAX|WAS-LX2|vivo|EML-L29|Moto|MMB|Redmi%20Note%208|M2003J15SC|MI%20MAX|Nexus|ELE-L29|Redmi%20Note%204|rv:89.0|VKY-L09|SmartN11|A330|LM-|G8341|INE-AL00|Mi|CLT|BV9700Pro|5024I|MEIZU"), "Android",
  match(cs_user_agent, "X11|Linux%20X86_64"),"Linux", 
  1==1, "OTHER")

You could technically use a lookup, where each row of the lookup contains

*XXX*,YYY

where XXX is the expression you want and YYY is the device. Then make a lookup definition that uses that field as a wildcard, e.g. WILDCARD(cs_user_agent)

and then do 

| lookup lookup_definition cs_user_agent OUTPUT Device