topic Re: Query formatting error. in Security

Query formatting error.

jerinvarghese — Tue, 28 Jan 2020 09:31:29 GMT

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.

Re: Query formatting error.

gcusello — Tue, 28 Jan 2020 09:46:47 GMT

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

Re: Query formatting error.

jerinvarghese — Tue, 28 Jan 2020 11:09:34 GMT

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.

Re: Query formatting error.

to4kawa — Tue, 28 Jan 2020 11:27:03 GMT

your search
| dedup hostname,  Interface

Re: Query formatting error.

gcusello — Tue, 28 Jan 2020 13:13:58 GMT

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