Search Lookup Help

[DilipKMondal]

I am trying to create a table with 

# pyUserIdentifier pyStatusMessage Count of occurences

1 user1234 Couldn't connect to server 1

Our logs have the following json pattern. Any help is highly appreciated.

Please see below sample log.

JSON log:
"pyOptions":"{\"HasTelephonyPriv\":\"true\",\"isSnapshotOnly\":\"\",\"pyAutoLogin\":\"\",\"pyClientHandle\":\"HEWR40W8VLO39ZP5OVIBJKMZKEF8YETH5A\",\"pyDeviceState\":\"\",\"pyNumberOfLines\":\"3\",\"pyPegaCTIError\":\"\",\"pyTelephonyMode\":\"1\",\"pyThisPageAsJSON\":\"\",\"pyUserIdentifier\":\"user1234\",\"pyUserName\":\"\",\"pyUserPassword\":\"\",\"pyWorkMode\":\"Busy\",\"queue\":[ \"\"] }"
,"pyPageExists":"false"
,"pyPort":"7017"
,"pyPresenceAgent":"H-GET"
,"pySelectedLinkName":"CHANNELSERVICES-ADMIN-CTILINK-LOCAL-JTAPI AVAYAPBX1"
,\"pySSLProtocolVersion\":\"TLSv1.2\",\"pyStatusMessage\":\"Couldn't connect to server\",\"pyStatusValue\":\"Fail\",\"pySwitchType\":\"Avaya EAS CM\",\"pyVendor\":\"Avaya\",\"pyWorkgroupPhoneBook\":\"true\",\"pzInsKey\":\"CHANNELSERVICES-ADMIN-CTILINK-LOCAL-JTAPI AVAYAPBX1\",\"pzLoadTime\":\"May 3, 2024 9:00:35 AM CDT\",\"pzOriginalInstanceKey\":\"CHANNELSERVICES-ADMIN-CTILINK-LOCAL-JTAPI AVAYA-1\",\"pzPageNameBase\":\"D_CTILinkInfo\",\"LogoutReasonCodes\":[ ],\"NotReadyReasonCodes\":[ ],
,"pyThisDN":"24181"
,"pyWorkMode":"Busy"

[deepakc]

Example using makeresults command for the Json data

| makeresults
| eval json_data="{\"pyOptions\":{\"HasTelephonyPriv\":\"true\",\"isSnapshotOnly\":\"\",\"pyAutoLogin\":\"\",\"pyClientHandle\":\"HEWR40W8VLO39ZP5OVIBJKMZKEF8YETH5A\",\"pyDeviceState\":\"\",\"pyNumberOfLines\":\"3\",\"pyPegaCTIError\":\"\",\"pyTelephonyMode\":\"1\",\"pyThisPageAsJSON\":\"\",\"pyUserIdentifier\":\"user1234\",\"pyUserName\":\"\",\"pyUserPassword\":\"\",\"pyWorkMode\":\"Busy\",\"queue\":[\"\"]},\"pyPageExists\":\"false\",\"pyPort\":\"7017\",\"pyPresenceAgent\":\"H-GET\",\"pySelectedLinkName\":\"CHANNELSERVICES-ADMIN-CTILINK-LOCAL-JTAPI AVAYAPBX1\",\"pySSLProtocolVersion\":\"TLSv1.2\",\"pyStatusMessage\":\"Couldn't connect to server\",\"pyStatusValue\":\"Fail\",\"pySwitchType\":\"Avaya EAS CM\",\"pyVendor\":\"Avaya\",\"pyWorkgroupPhoneBook\":\"true\",\"pzInsKey\":\"CHANNELSERVICES-ADMIN-CTILINK-LOCAL-JTAPI AVAYAPBX1\",\"pzLoadTime\":\"May 3, 2024 9:00:35 AM CDT\",\"pzOriginalInstanceKey\":\"CHANNELSERVICES-ADMIN-CTILINK-LOCAL-JTAPI AVAYA-1\",\"pzPageNameBase\":\"D_CTILinkInfo\",\"LogoutReasonCodes\":[],\"NotReadyReasonCodes\":[],\"pyThisDN\":\"24181\",\"pyWorkMode\":\"Busy\"}"
| eval pyUserIdentifier=spath(json_data,"pyOptions{}.pyUserIdentifier")
| eval pyStatusMessage=spath(json_data,"pyStatusMessage")
| stats count BY pyUserIdentifier,pyStatusMessage

If using the spath command the data must be well-formatted as per standards
https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Spath

If you are using indexed_extractions=JSON or KV_MODE=JSON in the props.conf file, then you don't need to use the spath command as it auto extract the fields/values for you and you can then use the stats command based on your fields, and this is the preferred option as it auto extract the fields/values for you. If you don't know what this is Speak to your Splunk Admin to onboard the json data correctly.  

[DilipKMondal]

Hi @deepakc  - Good Morning.

Thank you, this is really helpful.

You have a great day!

Best Regards,

Dilip

[gcusello · Accepted solution]

Hi @DilipKMondal ,

please try something ike this:

<your_search>
| spath
| rename 
     pyOptions.pyUserIdentifier AS pyUserIdentifier
     pyOptions.pyStatusMessage AS pyStatusMessage
| stats 
     count AS "Count of occurences"
     BY pyUserIdentifier pyStatusMessage

| eval counter=1
| accum counter as "#"
| table "#" pyUserIdentifier pyStatusMessage "Count of occurences"

Ciao.

Giuseppe

[DilipKMondal]

Hi @gcusello  - Good Morning.

Thank you for the wonderful help and guidance. I can now able to proceed with this.

I highly appreciate your help. You have a great day!

Best Regards,

Dilip K Mondal

[gcusello]

Hi @DilipKMondal ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉