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
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
Can you share the current search?
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
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
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