Security

Query formatting error.

jerinvarghese
Communicator

Need help in filtering words from the RAW output.
Below is a sample message that am getting from my index.

2020-01-24T18:48:03.593Z USDALPOD03-DCNPL2023 <29> ifIndex 515, ifAdminStatus up(1), ifOperStatus up(1), ifName et-0/0/50
2020-01-24T18:48:01.793Z USDALPOD03-DCNPL2023 <28> ifIndex 515, ifAdminStatus up(1), ifOperStatus down(2), ifName et-0/0/50

Below is the code that am using.

index=nw_syslog  "et-*" "ifoper*"
| rex field=_raw "ifName (?<Interface>.*)"
| rex field=_raw "ifOperStatus (?<Status>.*)"
| table hostname, Status, Interface

Below is the output that am getting.

hostname    Status  Interface
USDALPOD03-DCNPL2023    up(1), ifName et-0/0/50 et-0/0/50
USDALPOD03-DCNPL2023    down(2), ifName et-0/0/50   et-0/0/50

Expected output

hostname    Status  Interface    Time
USDALPOD03-DCNPL2023    up  et-0/0/50  XX:XX:XX
USDALPOD03-DCNPL2023    down    et-0/0/50  XX:XX:XX

While am giving | rex field=_raw "ifOperStatus (?.*)(" this qurry, its giving me error. please help in formatting.

0 Karma
1 Solution

gcusello
Legend

Hi @jerinvarghese,
at first use this regex

ifIndex\s+(?<ifIndex>[^,]*),\sifAdminStatus\s+(?<ifAdminStatus>[^,]*),\s+ifOperStatus\s+(?<Status>[^\(]*)\(\d+\),\s+ifName\s+(?<Interface>.*)

that you can test at https://regex101.com/r/KRBboF/2

So you can modify your output having a search like this:

 index=nw_syslog  "et-*" "ifoper*"
 | rex "ifIndex\s+(?<ifIndex>[^,]*),\sifAdminStatus\s+(?<ifAdminStatus>[^,]*),\s+ifOperStatus\s+(?<Status>[^\(]*)\(\d+\),\s+ifName\s+(?<Interface>.*)"
| table hostname, Status, Interface

Ciao.
Giuseppe

View solution in original post

0 Karma

gcusello
Legend

Hi @jerinvarghese,
at first use this regex

ifIndex\s+(?<ifIndex>[^,]*),\sifAdminStatus\s+(?<ifAdminStatus>[^,]*),\s+ifOperStatus\s+(?<Status>[^\(]*)\(\d+\),\s+ifName\s+(?<Interface>.*)

that you can test at https://regex101.com/r/KRBboF/2

So you can modify your output having a search like this:

 index=nw_syslog  "et-*" "ifoper*"
 | rex "ifIndex\s+(?<ifIndex>[^,]*),\sifAdminStatus\s+(?<ifAdminStatus>[^,]*),\s+ifOperStatus\s+(?<Status>[^\(]*)\(\d+\),\s+ifName\s+(?<Interface>.*)"
| table hostname, Status, Interface

Ciao.
Giuseppe

0 Karma

jerinvarghese
Communicator

Thanks so much for the regex command.

I edited little more in that

index=nw_syslog  "et-*" "ifoper*"
  | rex "ifIndex\s+(?<ifIndex>[^,]*),\sifAdminStatus\s+(?<ifAdminStatus>[^,]*),\s+ifOperStatus\s+(?<Status>[^\(]*)\(\d+\),\s+ifName\s+(?<Interface>.*)"
  | stats  latest(_time) as Time_CST count by hostname, Status, Interface
  | sort - Time_CST
 | fieldformat Time_CST=strftime(Time_CST,"%x %X")
 | table hostname, Status, Interface, Time_CST, count

Output came as

hostname    Status  Interface   Time_CST    count
USDALPOD03-DCNPL2023    up  et-0/0/50   01/24/20 12:48:03   2
USDALPOD03-DCNPL2023    down    et-0/0/50   01/24/20 12:48:01   1
USDALPOD03-DCNPL2023    up  et-0/0/48   01/24/20 12:33:27   2
USDALPOD03-DCNPL2023    down    et-0/0/48   01/24/20 12:33:26   1
USDALPOD03-DCNPL2021    down    et-0/0/48   01/24/20 10:26:53   1
USDALPOD03-DCNPL2021    up  et-0/0/48   01/24/20 10:26:52   1

Is it possible to dedup the Interface w.r.t to the hostname and display the latest one Status based.

0 Karma

gcusello
Legend

Hi @jerinvarghese,
if you want to list all the Statuses, you can modify your stats command in

| stats  latest(_time) as Time_CST values(Status) AS Status count by hostname Interface

if instead you want only the last one:

| stats  latest(_time) as Time_CST max(Status) AS Status count by hostname Interface

Ciao.
Giuseppe

0 Karma

to4kawa
Ultra Champion
your search
| dedup hostname,  Interface
0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...