Splunk Search

Multisearch or union for this case- How to show for 5 user id's?

untitledman27
Loves-to-Learn Everything

i All

 

There are query splunk like this : 


(index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Response" ERR-12120)
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode
| append [search index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Request")
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId
| table userId]

I will try to join both source from Request and Response, and result like below attachment :

My question  is, how show for 5 user id's ? (in blue line)
Because i already try join both sources, the user id shown not related for the customer name (in black line)PicturePicture

Labels (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The table is displaying the events as separate rows - in order to align the userID rows with the customer Name/Response Code rows, you could do something like this

(index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Response" ERR-12120)
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode
| streamstats count as row
| append [search index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Request")
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId
| table userId
| streamstats count as row]
| stats values(*) as * by row
| fields - row
0 Karma

untitledman27
Loves-to-Learn Everything
(index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Response" ERR-12120)
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode
| streamstats count as row
| append [search index=Prod sourcetype=ProdApp (host=Prod01 OR Prod02) source="/prodlib/SPLID" "Request")
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId
| table userId
| streamstats count as row]
| stats values(*) as * by row
| fields - row

 

Hi IT Whisper

 

I already try for above script. but the result like this

Capture 2.PNG

1. Why only two customer name and response code for this ? And there is wrong user id for above script (red line). Example for number 1, for Ikbal the username should be IKBAL110294, not DANIAAS086
Capture 3.PNG

 

 

 

 

And i hope for below snapshoot from my script, red line is filled all for user id and should be 29 rows for today

 

Capture 4.PNG

This is my script , pls help for filled all user id in above screenshoot(redline)


(index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod12) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999) | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?\{.*)" | spath input=my_json output=customerName path=response.login.customerName | spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode | dedup customerName | table customerName,responseCode | append [search index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" ("Request" "-/spl-banking/services/id/security/v1/login") | rex "^(?:[^\[\n]*\[){6}(?P\w+)" | rex field=_raw "(?\{.*)" | spath input=my_json output=userId path=data.userId | dedup userId | table userId]

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

How do you correlate the events with customerName to the events with userId?

There is nothing in your search to suggest that you wanted to do that!

0 Karma

untitledman27
Loves-to-Learn Everything

I will corellate customerName and user id with this source. For customerName with Response source, and for UserId with Request source, will joining Request and Response for got the both info 

(index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod12) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)
| rex "^(?:[^\[\n]*\[){6}(?P
\w+)"
| rex field=_raw "(?\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName | table customerName,responseCode
| append
[search index=prd-splid sourcetype=prd-splid-app (host=Prod11 OR host=Prod12 OR host=Prod011 OR host=Prod012) source="/prodlib/SPLID/logs/spl-message-*.log" ("Request" "-/spl-banking/services/id/security/v1/login")
| rex "^(?:[^\[\n]*\[){6}(?P\w+)"
| rex field=_raw "(?\{.*)"
| spath input=my_json output=userId path=data.userId
| dedup userId | table userId]

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

In your example "Ikbal" does not match "IKBAL110294" and therefore these fields cannot be used to correlate the events - unless you have some additional manipulations done on one or both of the fields until the values match.

0 Karma

untitledman27
Loves-to-Learn Everything

Each name or CIF have a different User ID.

For this example(Ikbal), he has the userid IKBAL110294 which he got from the Response login.
So I want to combine the customer name in the Request login(blueline) with the UserID in the Response login(redline)

Will join this 

Capture 5.PNG

And this

and thisand this

And my expexted result like this

And my exprected result like thisAnd my exprected result like this

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Why are you not extracting userId from here?

ITWhisperer_0-1664358810441.png

 

0 Karma

untitledman27
Loves-to-Learn Everything

I don't know how to get the userid(blueline) from here, because it's outside of the Response or Request source(before -/spl..., i don't know how to get the data)

Capture 8.PNG

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| rex "^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]"

Does this work?

If not, please can you share the log events in a code block </> rather than a screenshot?

0 Karma

untitledman27
Loves-to-Learn Everything

Hi IT Whisper 

 

Can you help me or give some clue for this ?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
(index=prd-splid sourcetype=prd-splid-app (host=LXSPLPIDV11 OR host=LXSPLPIDV12 OR host=LXSPLPIDV011 OR host=LXSPLPIDV012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)
| rex "^(?:[^\[\n]*\[){6}(?P<u>\w+)"
| rex "^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table userid,customerName,responseCode
0 Karma

untitledman27
Loves-to-Learn Everything

There this error 

Capture 9.PNG

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Please paste your sample events into a code block </> rather than a graphic so we can test the solution more easily.

0 Karma

untitledman27
Loves-to-Learn Everything

Like this ?

(index=prd-splid sourcetype=prd-splid-app (host=LXSPLPIDV11 OR host=LXSPLPIDV12 OR host=LXSPLPIDV011 OR host=LXSPLPIDV012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

No, please paste the events not the SPL using the code block </>

ITWhisperer_0-1664780761445.png

 

0 Karma

untitledman27
Loves-to-Learn Everything

Like this ?

 

command: regex="^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]"



[LXSPKPIDV01] Streamed search execute failed because: Error in 'rex' command: regex="^.+(\[[^\]]+\].*){4}\[(?<userid>[^\]]+)\]" has exceeded configured match_limit, consider raising the value in limits.conf.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

No, more like this

2022-09-28 10:11:26.484 [default ...

so we can include in our test of a solution for you

0 Karma

untitledman27
Loves-to-Learn Everything

<>
2022-10-04
11:23:11.160 [default task-45] INFO [SPLMessage][TWiUqanHFodo0UrDyrdkN3TXJD-5nry3r9N49A4N][11][Android-SPL][VIVIARVIANY01][ID][3.0][1852ms][] -/spl-banking/services/id/security/v1/login - Response - {"response":{"responseHeader":{"responseContext":{"responseCode":"SPL-PRD-99999","responseDescription":"Kami tidak dapat melanjutkan permintaanmu. Coba kembali ya.","serviceResponseTimeInGMT":"Tue Oct 04 04:23:10 GMT 2022","referenceNumber":"20221004042311158","systemDate":1664857391159,"challenge":"cZtpwwmGW6Q=","pubKey":"30820122300d06092a864886f70d01010105000382010f003082010a0282010100ac92dacc038f81d974e9134ea89bcf8a506962c494d7a8ca7a2931b5f9bdf7fb9b7b5d333b170cd427e3a3a062a7ca319d4817b8a7628a15ace5cfc468a78bcb26b7f69f0155406f4e47cfed6b80d4ead331da0607ce940e00b560878634fce7268d67749c45560c10a14a11808bad0f53b27d4a0037bedb3d885b2259925c813230a0a2438d7c3e87294cf3cfee62b4f635c607dc053ff0636d7251b6ed15665dc7ee498b2318db44c07104ddbc8824d7b8b639eaba8b09e115000f6e450a03aaa9f7da0b800bd6396303a14b8dd9b8e3cf78f1d78d84df8940b9d4fcb9825549903a658551a55b6eb99b4cc3c2a463f881586f09b0d26c379b985f9c900d990203010001","pubKeyIndex":"1"},"requesterContext":{"unityFlag":false,"sessionId":"0zCMYGYrcstrZZRkCodgvfkxOSJWaCtMO4HdB0rq"}},"login":{"pfmAvailable":true,"insightsAvailable":true,"verifyPin":true,"userHashKey":"8+nvMI569nIpkVfAPZ5SWso8VW+lbcdDkuQBeyP7Ges=","softTokenSerialNumber":"VKD2AD363E9A","firstPartyAccess":"Y","activationTime":0,"lastLoginTime":"04 Oct 2022 11:22 AM ","segment":"P","softTokenState":"ACTIVE","twoFAMode":"S","forceChangeUserIdentity":false,"forceChangePassword":false,"concurrentSessions":1,"stt":0,"maskedMobileNumber":"********8187","maskedEmail":"dondalop****@gmail.com","customerName":"VIVI ARVIANY ","securityInformation":{"assignmentDate2":"","tokenStatus":"","preferredLanguage":"E","lastLoginTime":"04 Oct 2022 11:22 AM ","softTokenFlag":true,"softTokenSerialNumber":"VKD2AD363E9A","softTokenTfaDown":false,"softTokenState":"ACTIVE","firstPartyAccess":"Y","concurrentSessions":1,"firstLoginDate":"28092022","firstLoginTime":"085655","ssoSessionToken":"70001_PUsXPbHvIlUFtKLIckbQjSimsaR_LUGkUZEm2KqgTMgd707Nu_bMoluUcNn6XWUPojGhlOgoUwO0QPShh8-9brWokMuiJ_hBXhVtokxXFw-KoEf-GuxE5FbiiPpTLeD3IK1zOtLAFDu_pxYeBwJKjLNhP0EAmvEK-GbK0hzW5EnMpl0jZkd-S69nPqoRopKZmTveYEXn5-AWghl-B-fG1NqcjTepH-8GjwDbe-dGv2aIbLS1ZUQJ_jo1LUfmRFYWXzICFNPK3mzLV5TWas8JmQeuYKoNFg20nMxBMGqbe38LyZejg6KFrDE4b3EJKVEXbqkNcxZXClu_nQFj_yF7J_LKTWiqpXcmHNr9hVviKO-UCQ1hlULk-8_y2b2Kj_oN-R8TLHuMW2htD57IQ","userLoginTime":"1664857389552","timeToSTActivation":0,"twoFAStatus":false,"blackOutTime":"30","activationTime":0,"assignmentDate":"","twoFAMode":"S","tokenActivated":false,"forceChangeUserIdentity":"N","forceChangePassword":"N","lastLoginTimeStamp":"2022-10-04 11:22:51.0"}},"dbcx":false,"timeout":false,"partialTimeout":false,"hasEcomProduct":false,"bundle":false,"twoFAEnabled":false,"twoFaEnabled":false}}
</>

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Not quite but close enough 😀

You are already extracting the user name - Try this

(index=prd-splid sourcetype=prd-splid-app (host=LXSPLPIDV11 OR host=LXSPLPIDV12 OR host=LXSPLPIDV011 OR host=LXSPLPIDV012) source="/prodlib/SPLID/logs/spl-message-*.log" "Response" "-/spl-banking/services/id/security/v1/login" SPL-PRD-99999)
| rex "^(?:[^\[\n]*\[){6}(?P<username>\w+)"
| rex field=_raw "(?<my_json>\{.*)"
| spath input=my_json output=customerName path=response.login.customerName
| spath input=my_json output=responseCode path=response.responseHeader.responseContext.responseCode
| dedup customerName
| table customerName,responseCode,username

 

0 Karma

untitledman27
Loves-to-Learn Everything

Hi, this case is solved. Thanks!

 

But 1 question. how showed the date and time for blue line in this screeshoot?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...