What do you get the SysAdmin who already “has everything” for Christmas?  Well, I asked Santa Scott (or maybe I should call him “Forstie the Snowman”) for yet another  IBM i Service that would make my life easier.

Note: Thanks to my good friend and i-UG colleague Will Holland for the Festive Sign On Screen. #IBMiAwesome!

One of the more frequent support questions we get is “Why did that NetServer User get disabled” and whilst the answer is obvious (someone used the wrong password) often the root cause is much harder to discover.

Now, for the sake of brevity, you just need to know that you can disable an IBM i User in a way that stops them from being able to Map a Network drive to your IBM i server but still lets them sign on.

Usually, this is because someone has changed their IBM i password but not updated the password in the drive mapping.  The problem is made worse as these days these passwords are cached and users tend to have more than one device connecting to IBM i and they only need to fail to update the password on one of their devices to disable access to all of them.

Using IBM i Service to find the problem device

So, the challenge here is not finding the problem user but the problem device.  The best way I found to do this is to focus on the IP address of the device causing the issue.  This is logged with a VP entry in the System Audit Journal.

The following piece of SQL magic makes short work of what used to be a gruelling task!

ProTip, there is a copy of the SQL statement in a format you can copy at the bottom of this article, I’ve used an image here as it’s easier for us Muggles to read.

In this example I asked for a list of all the NetServer failed login attempts in the last 2 hours, but you could just as easily say 2 minutes, 2 days, 2 weeks, 2 months, etc

If you were wondering what that extra “interpret” syntax was for? Well, this is a great example of how you can turn opaque data into easy to understand, useful info!
For more of this SQL gold, visit Scott’s Db2 for IBM i SQL Tutor at https://ibm.biz/Db2foriSQLTutor

So, what was wrong with the old method?

The first problem is that you have to calculate the time period you are interested in, the example below shows the command to look at a 20 min period start 7:01pm on 30^th December 2020.

Then you have the aggravation of interpreting the results, the following is the same information as shown in the IBM i Service, I’ll let you decide which you prefer 😉

ProTip: You get bonus marks if you spotted that the first character denotes if the password was incorrect (P) or was incorrect on enough occasions to disable the user for NetServer (D).  You get star of the week if you knew that after the D is issued, subsequent failed attempts report as P even though the user is now disabled.

i-UG goes Hybrid

Our last User Group Meeting was in December and it was our first attempt at a Hybrid event, mixing a live and remote speakers audience.  It was a great success and we thank you for all the kind words of support we received.  We are returning to the Mount Hotel in my home town of Wolverhampton for another Hybrid event on the 18^th March, we hope to see you there. For more details check out  www.i-ug.co.uk

Sample SQL that you can copy and paste

Sample SQL in text format below, please feel free to improve upon this and add it back as a comment to this article:

select
Entry_Timestamp,
interpret(substr(entry_data, 32, 10) as char(10)) as user_name,
Remote_Address,
case interpret(substr(entry_data, 1, 1) as char(1))
when ‘P’ then ‘NerServer password incorrect’
when ‘D’ then ‘NetServer user disabled’
else interpret(substr(entry_data, 1, 1) as char(1))
end as error_type
from table (
qsys2.display_journal(‘QSYS’, ‘QAUDJRN’,
starting_receiver_name => ‘*CURAVLCHN’,
journal_entry_types => ‘VP’,
starting_timestamp => CURRENT TIMESTAMP – 6 HOURS)
);