topic Re: Search Lookup Help in Reporting

Search Lookup Help

DilipKMondal — Fri, 03 May 2024 22:28:40 GMT

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"

Re: Search Lookup Help

gcusello — Sat, 04 May 2024 09:24:25 GMT

Hi @DilipKMondal ,

please try something ike this:

Ciao.

Giuseppe

Re: Search Lookup Help

deepakc — Sat, 04 May 2024 09:35:14 GMT

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.

Re: Search Lookup Help

DilipKMondal — Sat, 04 May 2024 14:09:05 GMT

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

Re: Search Lookup Help

DilipKMondal — Sat, 04 May 2024 15:54:30 GMT

Hi @deepakc - Good Morning.

Thank you, this is really helpful.

You have a great day!

Best Regards,

Dilip

Re: Search Lookup Help

gcusello — Sun, 05 May 2024 09:01:24 GMT

Hi @DilipKMondal ,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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